VBA Excel: příklady programů. Makra v aplikaci Excel

Málokdo ví, že první verze populárního produktu Microsoft Excel se objevila v roce 1985. Od té doby prošla několika modifikacemi a je poptávka mezi milióny uživatelů po celém světě. Současně mnoho pracuje s jen malou částí možností tohoto stolního procesoru a ani nehádají, jak by jim to mohlo usnadnit programovací dovednosti aplikace Excel.

Co je VBA?

Programování v aplikaci Excel se provádí pomocí programovacího jazyka Visual Basic for Application, který je původně vestavěn do nejslavnějšího stolního procesoru od společnosti Microsoft.

Podle svých zásluh odborníci připisují srovnatelnou snadnost vývoje. Jak ukazuje praxe, uživatelé VBA mohou zvládnout i ty, kteří nemají profesionální programovací schopnosti. Zvláštností VBA je provedení skriptu v prostředí kancelářských aplikací.

Nevýhodou programu jsou problémy spojené s kompatibilitou různých verzí. Jsou to kvůli tomu, že kód programu VBA odkazuje na funkci, která je v nové verzi produktu, ale není ve staré verzi. Také nevýhody zahrnují příliš vysokou otevřenost kódu pro změnu neoprávněnou osobou. Nicméně Microsoft Office a IBM Lotus Symphony umožňují uživateli používat šifrování původního kódu a nastavit heslo pro jeho prohlížení.

Objekty, sbírky, vlastnosti a metody

Právě s těmito koncepty musíte pochopit, kdo bude pracovat v prostředí VBA. Především je nutné pochopit, co je předmět. V aplikaci Excel se jedná o list, knihu, buňku a rozsah. Tyto objekty mají zvláštní hierarchii, tj. poslouchejte.

Hlavní je aplikace, která odpovídá samotnému programu Excel. Poté postupujte podle pracovních sešitů, pracovních listů a také podle řady. Například pro přístup k buňce A1 na určitém listu musíte zadat cestu, která obsahuje hierarchii.

Pokud jde o pojem „sběr,“ této skupiny předmětů stejného druhu, ve kterém je záznam daného ChartObjects. Jeho jednotlivé prvky jsou také předměty.

Dalším pojmem jsou vlastnosti. Jsou nezbytnou vlastností jakéhokoli objektu. Například pro rozsah je to hodnota nebo vzorec.

Metody jsou příkazy, které ukazují, co je třeba udělat. Při psaní kódu ve VBA je musíte oddělit od objektu podle období. Například, jak bude ukázáno později, velmi často při programování v aplikaci Excel použijte příkaz Buňky (1,1) .Vyberte. To znamená, že musíte vybrat buňku se souřadnicemi (1.1), tj. A1.

S ním se často používá Selection.ClearContents. Provedení znamená vyčistit obsah vybrané buňky.

Jak začít

Nejprve je třeba vytvořit soubor a uložit jej přiřazením jména a výběru typu "Sešit Excel s podporou maker".

Pak musíte jít do aplikace VB, stačí použít klávesy "Alt" a "F11". Další:

• v panelu nabídek umístěném v horní části okna klepněte na ikonu vedle ikony aplikace Excel;
• vyberte příkaz Mudule;
• uložte kliknutím na ikonu s obrázkem disketu;
• psát, řekněme, náčrtek kódu.

Vypadá to takto:

Podprogram ()

"Náš kód

End Sub

Všimněte si, že řádek "Náš kód" bude zvýrazněn v jiné barvě (zeleně). Důvod je v apostrofu na začátku řádku, což znamená, že následuje komentář.

Nyní můžete napsat kód a vytvořit nový nástroj pro vás ve VBA Excel (viz příklady níže uvedených programů). Samozřejmě, ti, kteří jsou obeznámeni se základy jazyka, budou mnohem jednodušší. Ale i ti, kteří je nemají, pokud si to přejí, si na něj rychle zvyknou.

Makra v aplikaci Excel

Pro tento název jsou programy zapsané v jazyce Visual Basic for Application skryté. Programování v aplikaci Excel znamená vytváření maker s požadovaným kódem. Díky této funkci se procesor tabulky procesoru společnosti Microsoft vyvíjí sám a přizpůsobuje se požadavkům konkrétního uživatele. Po pochopení, jak vytvářet moduly pro psaní maker, můžete začít hledat konkrétní příklady programů VBA Excel. Nejlepší je začít s nejzákladnějšími kódy.

Příklad 1

Úloha: napište program, který zkopíruje hodnotu obsahu jedné buňky a poté zapíše do jiné.

Postupujte takto:

• otevřete kartu Zobrazení;
• přejděte na ikonu Makra;
• Stiskněte tlačítko "Záznam makro";
• vyplňte otevřený formulář.

Pro jednoduchost, v „Název makra“ odejít „Makros1“ av „klávesová zkratka“, která zní například hh (to znamená, že můžete spustit ukázkový program bude «Ctrl + h» bleskový team). Stiskněte klávesu Enter.

Nyní, když již makro nahrávání začalo, zkopírujte obsah buňky do jiné. Vraťte se na původní ikonu. Klikněte na "Záznam makra". Tato akce znamená ukončení programu.

Další:

• znovu přejděte na řádek "Makra";
• v seznamu vyberte "Makro 1";
• klikněte na "Spustit" (stejná akce se spustí spuštěním klávesové zkratky "Ctrl + hh").

Výsledkem je akce, která byla provedena během záznamu makra.

Má smysl vidět, jak vypadá kód. Chcete-li to provést, vraťte se zpět do řádku "Makra" a klikněte na tlačítko "Upravit" nebo "Přihlaste se". V důsledku toho jsou v prostředí VBA. Samotný kód makra je mezi řádky Sub Macro1 () a End Sub.

Pokud bylo provedeno kopírování, například z buňky A1 do buňky C1, pak jeden z řádků kódu bude vypadat jako Rozsah ("C1"). V překladu to vypadá jako "Range (" C1 "). Zvolte", jinými slovy, přepne se na VBA Excel v buňce C1.

Aktivní část kódu je ukončena příkazem ActiveSheet.Paste. Znamená to zaznamenávat obsah vybrané buňky (v tomto případě A1) ve vybrané buňce C1.

Příklad 2

VBA cykly vám pomohou vytvořit různé makra v aplikaci Excel.

Cykly VBA vám pomohou vytvořit různé makra. Předpokládejme, že existuje funkce y = x + x² + 3x³ - cos (x). Chcete-li získat svůj graf, musíte vytvořit makro. To lze provést pouze pomocí smyček VBA.

Pro počáteční a konečnou hodnotu argumentu funkce použijte x1 = 0 a x2 = 10. Navíc musíte zadat konstantní hodnotu kroku změny argumentu a počáteční hodnoty pro počítadlo.

Všechny příklady maker VBA Excel jsou vytvořeny stejným způsobem jako výše. V tomto konkrétním případě kód vypadá takto:

Podprogram ()

x1 = 1

x2 = 10

shag = 0,1

i = 1

Do Zatímco x1 < x2 (smyčka bude provedena, zatímco výraz x1 bude true < x2)

y = x1 + x1 ^ 2 + 3 * x1 ^ 3 - Cos (x1)

Buňky (i, 1) .Value = x1 (hodnota x1 je zapsána do buňky souřadnicemi (i, 1))

Buňky (i, 2) .Value = y (hodnota y je zapsána do buňky souřadnicemi (i, 2))

i = i + 1 (počítadlo funguje) -

x1 = x1 + shag (argument je změněn velikostí kroku) -

Loop

End Sub.

V důsledku spuštění tohoto makra v "Excelu" získáme dva sloupce, z nichž první obsahuje hodnoty pro x a druhý pro y.

Nakreslí graf tak, jak je standardní pro aplikaci Excel.

Příklad 3

Pro implementaci cyklů ve verzi VBA Excel 2010, stejně jako v jiných verzích, je spolu s již vytvořeným konstruktem Do Do použito For.

Zvažte program, který vytvoří sloupec. V každé své buňce budou zapsány čtverečky odpovídajícího čísla řádku. Použití konstrukce For vám umožní psát jej velmi rychle bez použití počítadla.

Nejprve musíte vytvořit makro, jak je popsáno výše. Dále zapíšeme kód sám. Myslíme si, že nás zajímají hodnoty pro 10 buněk. Kód vypadá takto.

Pro i = 1 až 10 Další

Příkaz je přeložen do "lidského" jazyka, například "opakujte od 1 do 10 v krocích po jednom".

Je-li úkolem získání sloupce s čtverci, například všech lichých čísel z rozmezí od 1 do 11, pak píšeme:

Pro i = 1 až 10 krok 1 Další.

Zde krok je krok. V tomto případě se rovná dvěma. Ve výchozím nastavení chybějící slovo ve smyčce znamená, že krok je jednoduchý.

Získané výsledky by měly být uloženy v buňkách s číslem (i, 1). Potom pokaždé, když spustíte cyklus s nárůstem i o velikost kroku, automaticky se zvýší i počet řádků. Kód bude tedy optimalizován.

Kód bude obecně vypadat takto:

Podprogram ()

Pro i = 1 až 10 Krok 1 (můžete psát jednoduše pro i = 1 až 10)

Buňky (i, 1). Hodnota = i ^ 2 (tj. Hodnota čtverce i) je zapsána v buňce (i, 1)

Další (v jistém smyslu hraje roli počitadla a znamená to, že začne jeden cyklus)

End Sub.

Pokud se to udělá správně, včetně nahrávání a provozní makra (viz. Výše ​​uvedených pokynů), pak se nazývá pokaždé, když je daná velikost bude dosaženo sloupec (v tomto případě skládající se z 10 článků).

Příklad 4

V každodenním životě je velmi často nutné rozhodovat v závislosti na nějakém druhu stavu. Nemůžete bez nich provádět ve VBA Excel. Příklady programů, ve kterých je zvolen další průběh provádění algoritmu, spíše než předem definovaný, nejčastěji používají konstrukci If hellip-Then (pro složité případy) Pokud hellip-Pak hellip-END Pokud.

Zvažme konkrétní případ. Předpokládejme, že je třeba vytvořit makro pro aplikaci Excel tak, aby byla zapsána buňka se souřadnicemi (1,1):

1, pokud argument je kladný -

0, jestliže argument nulové-

-1, pokud je argument negativní.

Vytvoření takového makra pro aplikaci Excel se spouští standardním způsobem pomocí "horkých" kláves Alt a F11. Pak je napsán následující kód:

Podprogram ()

x = buňky (1, 1) .Value (tento příkaz přiřadí hodnotu obsahu buňky souřadnicemi (1, 1))

Pokud x> 0 pak buňky (1, 1) .Value = 1

Pokud x = 0, pak buňky (1, 1). Hodnota = 0

Pokud x<0 Potom buňky (1, 1) .Value = -1

End Sub.

Zbývá spouštět makro a získat správnou hodnotu argumentu v aplikaci Excel.

Funkce VBA

Jak jste si možná všimli, programování v nejslavnějším stolním procesoru společnosti Microsoft není tak obtížné. Zvláště pokud se naučíte používat funkce VBA. Tento programovací jazyk, vytvořený speciálně pro psaní aplikací v aplikaci Excel a Word, má celkem 160 funkcí. Mohou být rozděleny do několika velkých skupin. Jedná se o:

• Matematické funkce. Aplikujete je na argument, získáte hodnotu kosinusu, přirozený logaritmus, celou část a tak dále.
• Finanční funkce. Díky jejich dostupnosti a použití programování v aplikaci Excel můžete získat efektivní nástroje pro účetnictví a finanční účetnictví.
• Funkce zpracování pole. Ty zahrnují Array, IsArray-LBound-UBound.
• Funkce VBA Excel pro řetězec. To je poměrně velká skupina. Zahrnuje například funkce Space pro vytvoření řetězce s počtem mezery rovnajícím se celočíselnému argumentu nebo Asc pro překládání znaků do kódu ANSI. Všechny jsou široce používány a umožňují pracovat s řetězci v aplikaci Excel a vytvářet aplikace, které značně usnadňují práci s těmito tabulkami.
• Funkce převodu typu dat. Například CVar vrací hodnotu argumentu Expression a převede ji na typ dat Variant.
• Funkce práce s daty. Výrazně rozšiřují standardní funkce aplikace Excel. Funkce WeekdayName tedy vrátí název (úplné nebo částečné) dne v týdnu číslem. Ještě užitečnější je časovač. Udává počet sekund, který uplynul od půlnoci do určitého okamžiku dne.
• Funkce pro konverzi numerického argumentu na různé číselné systémy. Například Oct vydal osmičkovou reprezentaci čísla.
• Funkce formátování. Nejdůležitější z nich je Format. Vrátí hodnotu typu Variant s výrazem formátovaným podle pokynů uvedených v popisu formátu.
• a další.

Studium vlastností těchto funkcí a jejich aplikace výrazně rozšíří rozsah aplikace aplikace Excel.

Příklad 5

Pokusíme se vyřešit složitější problémy. Například:

Vzhledem k papírovému dokumentu o skutečné výši nákladů podniku. Požadováno:

• vývoj šablonové části pomocí stolního procesoru "Excel";
• vytvořte program VBA, který bude dotazovat zdrojová data k jeho vyplnění, provádět potřebné výpočty a vyplní je pomocí příslušných buněk šablony.

Zvažte jedno z řešení.

Vytvoření šablony

Všechny akce jsou prováděny na standardním listu v aplikaci Excel. Vyhrazené volné buňky pro vkládání dat na měsíc, rok, jméno společnosti-spotřebitele, výše nákladů, jejich výše, obrat. Vzhledem k tomu, že počet společností (společností), pro které je sestavena zpráva, není pevně stanoven, buňky pro vytváření hodnot založené na výsledcích a jméno specialisty nejsou dříve vyhrazeny. Pracovní list má nový název. Například "Օ tchet".

Proměnné

Chcete-li napsat program pro automatické vyplnění šablony, musíte vybrat symboly. Budou použita pro proměnné:

• Číslo NN aktuálního řádku tabulky;
• TP a TF - plánovaný a skutečný obrat;
• SF a SP - skutečná a plánovaná výše nákladů;
• IP a IF - plánovaná a skutečná úroveň nákladů.

Označte tytéž písmena, ale s "prefixem" Itog je shromáždění celkové hodnoty pro tento sloupec. Například ItogTP odkazuje na sloupec tabulky s názvem "plánovaný obrat".

Řešení problémů pomocí programování VBA

Pomocí zavedeného zápisu získáváme vzorce pro odchylky. Pokud chcete vypočítat v%, máte (F - P) / P * 100 a v součtu - (F - P).

Výsledky těchto výpočtů lze nejlépe okamžitě přidat do odpovídajících buněk tabulky aplikace Excel.

Pro výsledky ve skutečnosti a prognózu jsou získány vzorce ItogP = ItogP + P a ItogF = ItogF + F.

Pro odchylky použít = (ItogF - ItogP) / ItogP * 100 v případě, že výpočet se provádí v procentech, a je-li celková hodnota - (ItogF - ItogP).

Výsledky jsou okamžitě zapsány do odpovídajících buněk, takže není potřeba je přiřadit k proměnným.

Před spuštěním vytvořeného programu musíte uložit sešit, například nazývaný "Report1.xls".

Tlačítko Create Report Table musí být stisknuto pouze 1 krát po zadání informací o hlavičce. Měli byste znát další pravidla. Tlačítko "Přidat řádek" by mělo být stisknuto vždy po zadání hodnot pro každou aktivitu v tabulce. Po zadání všech dat musíte klepnout na tlačítko "Dokončit" a poté přepnout do okna aplikace Excel.

Nyní víte, jak řešit úkoly aplikace Excel pomocí maker. Schopnost používat VBA Excelu (viz ukázkové programy. Výše), a možná budete muset pracovat v prostředí nejpopulárnější v okamžiku, textový editor Word „“ Zejména tím, že záznam, jak je uvedeno v úvodu tohoto článku, nebo psaní kódu pro vytvoření tlačítek nabídky, jimiž mnohé z operací na textu lze provést pomocí tlačítek na dani, nebo přes ikonu „Zobrazit“ a záložce „Makra“.