Počítače, Software
VBA Excel: příklady programů. Makra v aplikaci Excel
Jen málo lidí ví, že první verze populárního produktu Microsoft Excel se objevila v roce 1985. Od té doby prošla několika změnami a je poptávka mezi miliony uživatelů po celém světě. Současně mnoho lidí pracuje jen s 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.
Na základě 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 skutečnosti, že kód programu VBA odkazuje na funkci, která je přítomna v nové verzi produktu, ale nikoli ve staré verzi produktu. Také mínusy zahrnují příliš otevřenost kódu pro změnu neoprávněnou osobou. Nicméně, Microsoft Office, stejně jako IBM Lotus Symphony, umožňují uživateli použít šifrování počátečního kódu a nastavit heslo pro jeho prohlížení.
Objekty, sbírky, vlastnosti a metody
Právě s těmito pojmy 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. Sledujte navzájem.
Hlavní je aplikace, která odpovídá samotnému programu Excel. Poté postupujte podle pracovních sešitů, pracovních listů a také podle rozsahu. Například pro přístup k buňce A1 na určitém listu musíte zadat cestu obsahující hierarchii.
Pokud jde o pojem "sběr", pak je to skupina objektů stejné třídy, která ve vstupu vypadá jako ChartObjects. Jeho jednotlivé prvky jsou také předměty.
Dalším konceptem 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 formátu VBA je třeba je 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 klikněte na ikonu vedle ikony aplikace Excel;
- Vyberte příkaz Mudule;
- Uložit kliknutím na ikonu s disketovou disketou;
- Napište, řekněme, náčrt 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šší. Nicméně i ti, kteří je nemají, pokud si to přejí, si na to rychle mohou zvyknout.
Makra v aplikaci Excel
Pro tento název jsou programy zapsané v jazyce Visual Basic for Application skryté. Programování v aplikaci Excel tedy vytváří makra s požadovaným kódem. Díky této funkci se procesor tabulky procesoru společnosti Microsoft vyvíjí a přizpůsobuje se požadavkům konkrétního uživatele. Po pochopení, jak vytvořit 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 "Nahrát makro";
- Vyplňte otevřený formulář.
Pro zjednodušení je v poli "Makro jméno" ponecháno "Macro1" a v poli "Klávesová zkratka" se například vkládá hh (to znamená, že můžete spustit program pomocí příkazu "Ctrl + h"). 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 tlačítko "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, přejděte zpět na řádek Makra a klikněte na tlačítko Upravit nebo Přihlaste se. V důsledku toho jsou v prostředí VBA. Ve skutečnosti je samotný kód makra umístěn mezi řádky Sub Macro1 () a End Sub.
Pokud bylo kopírování provedeno například z buňky A1 do buňky C1, pak jeden z řádků kódu bude vypadat jako Rozsah ("C1"). V překladu vypadá jako "Rozsah (" C1 "). Zvolte", jinými slovy přepne 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.
VBA cykly vám pomohou vytvořit různé makra. Předpokládejme, že existuje funkce y = x + x 2 + 3x 3 - 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 Do x1
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 napsána v buňce s souřadnicemi (i, 2))
I = i + 1 (pracuje čítač);
X1 = x1 + shag (argument je změněn podle velikosti 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
K implementaci cyklů ve verzi VBA Excel 2010, stejně jako v jiných verzích, spolu s již zadaným konstruktem Do Do je použito For.
Zvažte program, který vytvoří sloupec. V každé své buňce se zaznamenají čtverce 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 je třeba vytvořit makro, jak je popsáno výše. Dále zapíšeme samotný kód. Myslíme si, že máme zájem o 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, jako "Opakujte od 1 do 10 v krocích po jednom".
Je-li úkolem získání sloupce se čtvercem, 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ž začne cyklus, se zvyšováním i o velikost kroku se automaticky zvýší i počet řádků. Kód bude tedy optimalizován.
Obecně bude kód 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) .Value = 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á jeden další cyklus)
End Sub.
Pokud je vše správně provedeno, včetně záznamu a spuštění makra (viz výše uvedený pokyn), pokaždé, když bude volána, získá se sloupec specifikované velikosti (v tomto případě 10 buněk).
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 udělat v aplikaci Excel VBA. Příklady programů, kde je zvolen další průběh provádění algoritmu, spíše než předdefinovaný, nejčastěji používá funkci If ... Then construction (pro složité případy) If ... Then ... END If.
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 pozitivní;
0, pokud argument je nulový;
-1 pokud argument je negativní.
Vytvoření takového makra pro aplikaci Excel se spustí 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 pak 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. Úplně v tomto programovacím jazyce, vytvořeném speciálně pro psaní aplikací v aplikaci Excel a Word, existuje asi 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. Vzhledem k jejich dostupnosti a použití programování v aplikaci Excel získáte efektivní nástroje pro účetnictví a finanční účetnictví.
- Funkce zpracování pole. Mezi ně patří 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í vám 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 konkrétní chvíle 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ů, které jsou zadány 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:
- Vyvinout šablonu 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žme jednu z variant řešení.
Vytvoření šablony
Všechny akce jsou prováděny na standardním listu v aplikaci Excel. Vyhrazené volné buňky pro zadání údajů za měsíc, rok, jméno společnosti-spotřebitele, výši nákladů, jejich výše, obrat. Vzhledem k tomu, že počet společností (společností), ve vztahu k nimž je zpráva vypracována, není stanovena, 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 stejnými písmeny, ale s "předponou" Itog je akumulace 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óza jsou získány vzorci ItogP = ItogP + P a ItogF = ItogF + F.
Pro odchylky použijte = (ItogF - ItogP) / ItogP * 100, pokud je výpočet v procentech a v případě celkové hodnoty - (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 je nutné uložit sešit například pod názvem "Report1.xls".
Tlačítko "Vytvořit tabulku sestav" musí být po zadání informací o hlavičce stisknuto pouze jednou. 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 kliknout na tlačítko "Dokončit" a poté přepnout do okna aplikace Excel.
Nyní, když víte, jak vyřešit problém pro tabulkový procesor Excel s makra. 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“.
Similar articles
Trending Now