Vytváření maker v aplikaci Microsoft Excel. Makra v Excelu - Návod k použití

Začal jsem vytvářet tento web jako poznámkový blok o pohodlných funkcích Excelu. Kdykoli si můžete osvěžit své znalosti o té či oné funkci programu tím, že přejdete na webovou stránku, a navíc můžete poslat odkaz na článek přátelům nebo kolegům, kteří požádají o pomoc s Excelem. Poměrně rychle se publikum stránek rozrostlo a jak vidím, zaznamenané informace jsou užitečné nejen pro mě a několik dalších lidí, ale také pro mnoho mně neznámých lidí a jsou vysoce hodnoceny vyhledávači. I když jsem si jistý schopnostmi Excelu, nemohu se nazývat specialistou na VBA na vysoké úrovni. Rád bych proto vytvořil velký asistentský článek, kam budu zaznamenávat užitečná makra, která často používám. Jsem si jistý, že tato sbírka funkcí VBA bude užitečná nejen pro mě.

Tato stránka již obsahuje několik článků o VBA, včetně, ale myslím, že bude užitečné ponechat na jednom místě miniaplikace kódu, které se často používají.

Vypněte zobrazování makro akcí na obrazovce jednoduchou funkcí. To nejen vizuálně zlepší provádění, ale také výrazně zrychlí provádění maker.

Sub Makros1() Application.ScreenUpdating = 0 "Váš kód Application.ScreenUpdating = 1 End Sub

Nezapomeňte funkci zahrnout na konec makra

Jak mohu odstranit výběr kopie po spuštění makra?

Pokud máte všechny tyto v makru, vyberte rozsah pro kopírování a vkládání. Nezapomeňte na konci makra zakázat výběr

Sub Makros1() "Váš kód Application.CutCopyMode = 0 End Sub

Užitečná makra. Jak najít poslední řádek nebo sloupec rozsahu

Tato konstrukce vám pomůže snadno najít číslo posledního řádku nebo sloupce vyplněného rozsahu. Zvláště vhodné je použití v cyklech, nemusíte zadávat 1000 cyklů s okrajem, Excel sám najde, kde je konec rozsahu pomocí této konstrukce:

Sub makros1() Dim mLastRow As Long Dim nLastCol As Long mLastRow = Cells(Rows.Count, 1).End(xlUp).Row "Najde číslo posledního řádku vyplněného rozsahu nLastCol= Cells(1, Columns.Count). End(xlToLeft ).Column "Najde číslo posledního naplněného sloupce v rozsahu End Sub

Navíc proměnnou rovnou deklaruji jako Long (délka 2 147 483 647), abych se nedostal do situace, kdy oblíbené Integer nemusí stačit (32 767) pro velké tabulky.

Měli byste také věnovat pozornost tomu, že vyhledávání probíhá v prvním sloupci nebo prvním řádku, vyberte požadované číslo pro správné určení.

Pro smyčku a kontrolu podmínek ve smyčce

Pokud jste našli poslední čísla řádků a sloupců rozsahů, můžete je použít v smyčkách. Například musíte projít první sloupec a spočítat počet prázdných buněk.

Sub makros1() Application.ScreenUpdating = 0 Dim mLastRow As Long Dim Kol As Long "Počítat proměnnou Dim i As Long "Opakovat proměnnou mLastRow = Cells(Rows.Count, 1).End(xlUp).Row Kol = 0 For i = 1 To mLastRow If Cells(i, 1).Value = "" Then Kol = Kol + 1 End If Next i MsgBox Kol Application.ScreenUpdating = 1 End Sub

Pomocí této funkce se zde také používá Msgbox, můžete zobrazit data v samostatném okně. Pro můj příklad to bude vypadat takto:

Výpočet doby provádění makra

Sub makros1() TimeStart = Now TimeFinish = Now MsgBox "Time: " & Format(TimeFinish - TimeStart, "h:mm:ss") End Sub

MsgBox vytvoří následující výsledek:

Je velmi vhodné sledovat dobu provádění maker pro následnou optimalizaci.

Jsou to užitečná makra, která používám velmi často. Tento seznam budu postupně rozšiřovat. Efektivní automatizace pro každého!

Sdílejte náš článek na svých sociálních sítích:

Jazyk VBA je považován za standardní skriptovací jazyk pro aplikace společnosti Microsoft a nyní je součástí všech aplikací Office a dokonce i aplikací jiných společností. Jakmile tedy zvládnete jazyk VBA pro Excel, můžete okamžitě přejít k vytváření maker pro další softwarové produkty společnosti Microsoft. Navíc budete moci vytvářet plnohodnotné softwarové produkty, které současně využívají funkce široké škály aplikací.

Jak povolit makra v Excelu

Ve výchozím nastavení je karta odpovědná za správu a navigaci maker v Excelu skrytá. Chcete-li tuto možnost aktivovat, přejděte na kartu Soubor do skupiny Možnosti. V zobrazeném dialogovém okně MožnostiVynikat přejděte na kartu Přizpůsobení pásky, do pravého pole se seznamem umístěte značku naproti kartě Vývojář. Tyto kroky jsou relevantní pro verze Excelu 2010 a starší.

Na pásu karet se zobrazí nová karta Vývojář s ovládacími prvky automatizace Excel.

Psaní maker v Excelu

V záložce Vývojář ve skupině Kód, klikněte na tlačítko Zaznamenejte makro. Zobrazí se dialogové okno Nahrajte makro, který požaduje nějaké informace o budoucím psaném kódu. Pokud makro vytváříte poprvé, můžete jednoduše kliknout na tlačítko OK. Od této chvíle bude Excel zaznamenávat každou akci uživatele v modulu VBA, ať už jde o zadávání dat, formátování nebo vytváření grafů. Chcete-li zastavit nahrávání makra, klepněte na tlačítko Zastavit nahrávání která je ve stejné skupině Kód.

Můžete také využít alternativní možnost záznamu maker pomocí tlačítka Nahrajte makro, který se nachází v levém dolním rohu excelového sešitu (vpravo od stavu Připraven).

Nyní můžete kliknutím na tlačítko zobrazit seznam všech vytvořených maker makro, umístěné ve skupině Kód. V dialogovém okně, které se zobrazí, můžete kódům přiřadit popisnější názvy nebo nastavit klávesové zkratky, které by spustily konkrétní makro. Alternativní možností pro spuštění tohoto okna je stisknout Alt + F8.

Úprava maker

Gratuluji! Napsali jste své první makro. Bylo by logické nyní zkontrolovat, jaký kód nám Excel vygeneroval. Vygenerovaný kód je napsán ve VBA (Visual Basic for Applications). Chcete-li to vidět, musíte jej otevřít. EditorVB(VBE), který se spouští stisknutím Alt + F11 nebo tlačítka VizuálníZákladní na kartě Vývojář.

Abyste předešli zmatkům v editoru, můžete pracovat pouze s jednou kartou v sešitu, listu nebo modulu. Takto vypadá editor v reálu.

V této fázi navrhuji podrobněji prostudovat různá okna a nabídky editoru VBA. To vám v budoucnu pomůže ušetřit spoustu času.

Pro zobrazení kódu klikněte na vlákno Moduly v okně projektů a dvakrát klikněte na větev, která se objeví Modul1 . Editor otevře okno s kódem, jak je znázorněno na obrázku.

Zde můžete upravit vygenerovaný kód, který byl napsán při práci v Excelu. Například musíte vyplnit určitý sloupec hodnotami od 1 do 10. Již máte první tři kroky, které zadávají hodnoty 1, 2 a 3 do prvních tří buněk sloupce A. Musíme dokončete zbývajících sedm kroků.

Pokud se podíváte na kód výše, uvidíte, že makro je určitým způsobem strukturováno. Aplikace nejprve přesune kurzor na buňku pomocí příkazu Range("A1").Select a poté upraví její obsah pomocí ActiveCell.FormulaR1C1 = "1". Takže u zbývajících kroků můžeme tyto kroky zopakovat a změnit adresu buňky a hodnotu, kterou chcete do této buňky zapsat. Chcete-li například nastavit buňku A4 na 4, napište:

Rozsah("A4").Vyberte ActiveCell.FormulaR1C1 = "4"

A opakujte podobné kroky pro zbývající hodnoty.

Po dokončení úprav knihu uložte. Makro můžete spustit stisknutím tlačítka F5 nebo po návratu do sešitu aplikace Excel přejděte na kartu Vývojář do skupiny Kód -> Makra a vyberte ze seznamu makro, které vás zajímá.

Věnujte několik minut pečlivému prostudování kódu, který Excel vygeneroval. Pokud jste začátečník, investování několika minut do učení kódu přinese později úžasné výsledky při seznamování se s objekty VBA. Vezměte prosím na vědomí, že příklad, o kterém jsme hovořili, je pouze ilustrativní. Existují rychlejší a efektivnější způsoby, jak dosáhnout podobných výsledků, o kterých si povíme příště.

Zvyšte rychlost provádění makra aplikace Excel

Zatím je vše dobré. Podívejme se na pár triků, které pomohou urychlit provádění maker. Vezměme si jako příklad výše uvedený fragment kódu. Moderní počítače spustí příslušný kód tak rychle, že si ho ani nevšimnete. Ale co když potřebujete provést operaci 50 000krát. To bude nějakou dobu trvat. Pokud je makro, které zapisujete, dlouhé stovky řádků, můžete urychlit provádění kódu oříznutím části procesů, která se při provádění makra nepoužívá.

Pomocí příkazu Application.ScreenUpdating

Prvním trikem je vyhnout se aktualizaci obrazovky, když je makro spuštěno. To Excelu umožní ušetřit výpočetní výkon počítače a aktualizovat obrazovku novými hodnotami až po provedení veškerého kódu. Chcete-li to provést, musíte přidat příkaz pro zakázání obnovování obrazovky na začátku makra a příkaz pro povolení obnovení obrazovky na konci makra.

1 2 3 4 5 6 7 8 9 10

Dílčí makro1() Rozsah("A1").Vyberte Rozsah("A2").Vyberte Rozsah("A3").Vyberte End Sub

Příkaz Application.ScreenUpdating říká Excelu, aby přestal zobrazovat přepočítaná data na obrazovce a vrátil hotové hodnoty na konci provádění kódu.

Pomocí příkazu Aplikace. Výpočet

Druhým trikem je deaktivace automatických výpočtů. Nech mě to vysvětlit. Pokaždé, když uživatel nebo proces aktualizuje buňku, Excel se pokusí přepočítat všechny buňky, které na ní závisí. Řekněme tedy, že pokud buňka, kterou se makro pokouší aktualizovat, ovlivní 10 000 dalších buněk, Excel se je všechny pokusí přepočítat, než se kód dokončí. Pokud tedy existuje mnoho ovlivňujících buněk, přepočet může výrazně zpomalit provádění kódu. Chcete-li tomu zabránit, můžete nainstalovat příkaz Aplikace. Výpočet na začátku kódu, který přepne přepočítávání vzorců do ručního režimu a následně vrátí automatický výpočet na konci makra.

1 2 3 4 5 6 7 8 9 10 11 12

Dílčí makro1() Application.ScreenUpdating = False Rozsah("A1").Vyberte ActiveCell.FormulaR1C1 = "1" Rozsah("A2").Vyberte ActiveCell.FormulaR1C1 = "2" Rozsah("A3").Vyberte ActiveCell.FormulaR1C1 = "3" Application.ScreenUpdating = True End Sub

Pozor, nezapomeňte tuto volbu na konci makra přepnout zpět do automatického režimu. V opačném případě to budete muset provést v samotném Excelu kliknutím na kartu Vzorce do skupiny Výpočet a vybrat si Možnosti výpočtu –> Automaticky.

Vyhněte se výběru buněk a rozsahů

V režimu automatického záznamu maker si můžete všimnout, že Excel velmi často používá příkaz pro výběr buňky, například Range("A1").Select. V našem příkladu jsme tento příkaz použili několikrát k výběru buňky a změně její hodnoty. Tomu se můžete vyhnout jednoduchým zadáním adresy buňky a zadáním požadované hodnoty (Makro zaznamenalo pohyb kurzoru z jedné buňky do druhé, proto vložíte tyto kroky. Nejsou však nutné). Efektivnější kód by tedy vypadal takto.

1 2 3 4 5 6 7 8 9 10 11

Dílčí makro1() Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Rozsah("A1").Hodnota = 1 Rozsah("A2").Hodnota = 2 Rozsah("A3").Hodnota = 3 Rozsah("A4").Hodnota = 4 Rozsah("A5").Hodnota = 5 Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True End Sub

V tomto případě jsme jednoduše odkazovali na buňku a dali jí požadovanou hodnotu, aniž bychom ji vůbec vybrali. Tato metoda je rychlejší než předchozí.

Příklady maker Excelu

Níže jsou uvedeny některé ukázkové kódy VBA, které vám pomohou automatizovat nejběžnější úkoly.

Makro pro zvýraznění buňky A1 na každém listu v aktivním sešitu. To také způsobí pohyb obrazovky.

Sub A1SelectionEachSheet() Dim i As Integer Application.ScreenUpdating = False For i = 1 To Sheets.Count Sheets(i).Select ActiveWindow.ScrollColumn = 1 ActiveWindow.ScrollRow = 1 Range("a1").Select Next Sheets(1) .Vyberte Application.ScreenUpdating = True End Sub

Makro pro kopírování aktuálního listu zadaným počtem opakování. Užitečné pro testování některých maker - provedené změny, kontrola na kopii dat. Došly nám kopie – spusťte makro znovu

Sub SimpleCopy() Dim i As Integer, j As Integer i = Application.InputBox("Zadejte počet kopií aktuálního listu") Application.ScreenUpdating = False For j = 1 To i ActiveSheet.Copy after:=Sheets(Sheets) .Count) ActiveSheet .Name = "Kopírovat" & j Další j Application.ScreenUpdating = True End Sub

Vytvářejte listy s názvy ze zadaného rozsahu na listu

Sub CreateFromList() Ztlumit buňku jako rozsah pro každou buňku ve výběru Sheets.Add after:=Sheets(Sheets.Count) ActiveSheet.Name = cell.Value Další buňka End Sub

Markrosovi za odeslání dopisu se zpožděním. Upravené makro z knihy Johna Walkenbacha Professional VBA Programming

Sub SendLetter() Dim OutApp as Object Dim OutApp As Object Dim OutMail as Object Set OutApp = CreateObject("Outlook.Application") OutApp.Session.Logon On Chyba Přejít na vyčištění Set OutMail = OutApp.CreateItem(0) On Error Pokračovat dále s OutMail .To = " [e-mail chráněný]" .Subject = "Přehled prodeje" .Přílohy.Přidat "C:\Test.txt" .Body = "Text e-mailu" .DeferredDeliveryTime = Nahradit(datum, ".", "/") & " 11:00:00 " .send ".Zobrazení pro vygenerování dopisu a jeho otevření End With On Chyba Přejít na 0 Set OutMail = Nothing cleanup: Set OutApp = Nothing End Sub

Mírně upravené makro obsahu od Nikolaje Pavlova.
Pokud již v knize list „Obsah“ existuje, makro vás vyzve k jeho odstranění. Pokud ne, vytvoří list „Obsah“ a vloží odkazy s názvy listů

Sub TableOfContent() Dim sheet As Worksheet Dim cell As Range Dim Answer As Integer Application.ScreenUpdating = False S ActiveWorkbook For Every Worksheet In ActiveWorkbook.Worksheets If Worksheet.Name = "Table of Contents" Then Answer = MsgBox("Sešit má list s názvem Obsah smazat?", vbYesNo) If Answer = vbNo Then Exit Sub If Answer = vbYes Then Application.DisplayAlerts = False Worksheet.Delete Application.DisplayAlerts = True End If End If Next End With Sheets(Array. (1)).Select Sheets.Add Sheets(1).Name = "Obsah" S ActiveWorkbook Pro každý list V ActiveWorkbook.Worksheets If sheet.Name<>"Obsah" Poté nastavte buňku = Worksheets(1).Buňky(list.Index, 1) .Worksheets(1).Hyperlinks.Add anchor:=cell, Address:="", SubAddress:=""" & sheet .Name & """ & "!A1" cell.Formula = list.Name End If Další list End With Rows("1:1").Delete Application.ScreenUpdating = True End Sub

Třídění listů z průvodců VBA. Makro také třídí skryté listy. Nebude fungovat, pokud má kniha chráněnou strukturu

Sub SORT_ALL_SHEETS() Application.ScreenUpdating = False: Application.EnableEvents = False Dim iSht As Worksheet, oDict As Object, i%, j% Set oDict = CreateObject("Scripting.Dictionary") " si zapamatujte stav viditelnosti každého listu a proveďte vše viditelné Pro každý iSht v ActiveWorkbook.Sheets oDict.Item(iSht.Name) = iSht.Visible: iSht.Visible = True Další S ActiveWorkbook " řazení viditelných listů For i = 1 To .Sheets.Count - 1 For j = i + 1 To .Sheets.Count If UCase(.Sheets(i).Name) > UCase(.Sheets(j).Name) Potom .Sheets(j).Přesunout před:=.Sheets(i) Další j Další i Konec " obnovit původní stav viditelnosti každého listu Pro každý iSht v ActiveWorkbook.Sheets iSht.Visible = oDict.Item(iSht.Name) Další Application.EnableEvents = True: Application.ScreenUpdating = True End Sub

Import sloupců „Pole1“ a „Pole2“ z listu „List1“ souboru Excel „C:\Manager.xls“ přes ADODB připojení a vložení obsahu počínaje buňkou A1 aktuálního listu

Málokdo ví, že první verze oblíbeného produktu Microsoft Excel se objevila v roce 1985. Od té doby prošel několika úpravami a je žádaný mezi miliony uživatelů po celém světě. Mnoho lidí přitom pracuje jen s malým zlomkem možností tohoto tabulkového procesoru a ani si neuvědomují, jak by jim schopnost programování v Excelu mohla usnadnit život.

Co je VBA

Programování v Excelu se provádí pomocí programovacího jazyka Visual Basic for Application, který byl původně zabudován do nejznámějšího tabulkového procesoru od společnosti Microsoft.

Odborníci připisují jeho přednostem relativní snadnost učení. Jak ukazuje praxe, základy VBA zvládnou i uživatelé, kteří nemají profesionální programátorské dovednosti. Mezi funkce jazyka VBA patří spouštění skriptů v prostředí kancelářských aplikací.

Nevýhodou programu jsou problémy spojené s kompatibilitou různých verzí. Dochází k nim, protože kód programu VBA přistupuje k funkcím, které jsou přítomny v nové verzi produktu, ale ne ve staré verzi. Mezi nevýhody patří také příliš vysoká otevřenost kódu pro úpravu cizí osobou. Microsoft Office i IBM Lotus Symphony však umožňují uživateli zašifrovat počáteční kód a nastavit heslo pro jeho zobrazení.

Objekty, kolekce, vlastnosti a metody

Toto jsou pojmy, kterým musí rozumět ti, kdo se chystají pracovat v prostředí VBA. Nejprve musíte pochopit, co je objekt. V Excelu jsou tyto funkce list, sešit, buňka a rozsah. Tyto objekty mají zvláštní hierarchii, tzn. poslouchat jeden druhého.

Hlavní je Aplikace, která odpovídá samotnému programu Excel. Pak následují Sešity, Pracovní listy a Rozsah. Chcete-li například získat přístup k buňce A1 na konkrétním listu, musíte zadat cestu, která bere v úvahu hierarchii.

Pokud jde o pojem „kolekce“, jedná se o skupinu objektů stejné třídy, která má v záznamu tvar ChartObjects. Jeho jednotlivé prvky jsou zároveň předměty.

Dalším pojmem jsou vlastnosti. Jsou nezbytnou vlastností každého předmětu. Například pro rozsah je to Hodnota nebo Vzorec.

Metody jsou příkazy, které označují, co je třeba udělat. Při psaní kódu ve VBA je třeba je od objektu oddělit tečkou. Například, jak bude ukázáno později, velmi často se při programování v Excelu používá příkaz Cells(1,1).Select. To znamená, že musíte vybrat buňku se souřadnicemi

Spolu s ním se často používá Selection.ClearContents. Provedení znamená vymazání obsahu vybrané buňky.

Jak začít

Poté musíte přejít do aplikace VB, pro kterou stačí použít kombinaci kláves „Alt“ a „F11“. Další:

• v panelu nabídek v horní části okna klikněte na ikonu vedle ikony Excel;
• vyberte příkaz Mudule;
• uložit kliknutím na ikonu s obrázkem;
• píšou, řekněme, návrh kódu.

Vypadá to takto:

Podprogram ()

„Náš kód

Upozorňujeme, že řádek „Náš kód“ bude zvýrazněn jinou barvou (zeleně). Důvodem je apostrof umístěný na začátku řádku, který označuje, že následuje komentář.

Nyní můžete napsat libovolný kód a vytvořit si nový nástroj ve VBA Excel (viz příklady programů níže). Samozřejmě to bude mnohem jednodušší pro ty, kteří jsou obeznámeni se základy jazyka Visual Basic. Nicméně i ti, kteří je nemají, si na to mohou zvyknout dostatečně rychle, pokud si to přejí.

Makra v Excelu

Tento název skrývá programy napsané ve Visual Basic for Application. Programování v Excelu tedy znamená vytváření maker s potřebným kódem. Díky této funkci se tabulkový procesor Microsoft vyvíjí sám, přizpůsobuje se požadavkům konkrétního uživatele. Poté, co jste přišli na to, jak vytvořit moduly pro psaní maker, můžete se začít dívat na konkrétní příklady programů VBA Excel. Nejlepší je začít s nejzákladnějšími kódy.

Příklad 1

Úkol: napište program, který zkopíruje hodnotu obsahu jedné buňky a následně ji zapíše do druhé.

Postup:

• otevřete kartu „Zobrazit“;
• přejděte na ikonu „Makra“;
• klikněte na „Zaznamenat makro“;
• vyplňte formulář, který se otevře.

Pro zjednodušení ponechte v poli „Název makra“ „Makro1“ a do pole „Klávesová zkratka“ vložte např. hh (to znamená, že program spustíte rychlým příkazem „Ctrl+h“). Stiskněte Enter.

Nyní, když záznam makra již začal, obsah buňky se zkopíruje do jiné. Návrat k původní ikoně. Klikněte na „Zaznamenat makro“. Tato akce znamená konec programu.

• přejděte zpět na řádek „Makra“;
• vyberte ze seznamu „Makro 1“;
• klikněte na „Spustit“ (stejná akce se spustí spuštěním kombinace kláves „Ctrl+hh“).

V důsledku toho dojde k akci, která byla provedena při záznamu makra.

Má smysl vidět, jak kód vypadá. Chcete-li to provést, přejděte zpět na řádek „Makra“ a klikněte na „Změnit“ nebo „Přihlásit se“. Díky tomu se ocitnou v prostředí VBA. Samotný kód makra je ve skutečnosti umístěn mezi řádky Sub Macro1() a End Sub.

Pokud bylo zkopírováno například z buňky A1 do buňky C1, bude jeden z řádků kódu vypadat jako Range(“C1”). Vyberte. V překladu to vypadá jako „Range(“C1”).Select, jinými slovy jde do VBA Excel, do buňky C1.

Aktivní část kódu je doplněna příkazem ActiveSheet.Paste. Znamená to zapsat obsah vybrané buňky (v tomto případě A1) do vybrané buňky C1.

Příklad 2

Smyčky VBA vám pomohou vytvářet různá makra v aplikaci Excel.

Smyčky VBA vám pomohou vytvořit různá makra. Předpokládejme, že máme funkci y=x + x 2 + 3x 3 - cos(x). Chcete-li získat jeho graf, musíte vytvořit makro. To lze provést pouze pomocí smyček VBA.

Počáteční a konečné hodnoty argumentu funkce jsou x1=0 a x2=10. Kromě toho musíte zadat konstantu - hodnotu pro krok změny argumentu a počáteční hodnotu pro čítač.

Všechny příklady maker VBA Excel jsou vytvořeny pomocí stejného postupu, jak je uvedeno výše. V tomto konkrétním případě kód vypadá takto:

Podprogram ()

krok = 0,1

Do Zatímco x1< x2 (цикл будет выполняться пока верно выражение x1 < x2)

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

Buňky(i, 1).Hodnota = x1 (hodnota x1 se zapíše do buňky se souřadnicemi (i,1))

Buňky (i, 2). Hodnota = y (hodnota y se zapíše do buňky se souřadnicemi (i,2))

i = i + 1 (počítadlo je aktivní);

x1 = x1 + shag (argument se mění o hodnotu kroku);

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.

Z nich se pak sestaví graf standardním způsobem pro Excel.

Příklad 3

K implementaci smyček ve VBA Excel 2010, stejně jako v jiných verzích, spolu s již danou konstrukcí Do While se používá For.

Zvažte program, který vytvoří sloupec. V každé jeho buňce budou zapsány druhé mocniny odpovídajícího čísla řádku. Použití konstrukce For vám umožní napsat jej velmi krátce, bez použití počítadla.

Nejprve musíte vytvořit makro, jak je popsáno výše. Dále si zapíšeme samotný kód. Předpokládáme, ž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 jako „Opakujte od 1 do 10 v krocích po jedné“.

Pokud je úkolem získat sloupec se čtverci např. všech lichých čísel z rozsahu od 1 do 11, pak napíšeme:

Pro i = 1 až 10 krok 1 Dále.

Tady krok je krok. V tomto případě se rovná dvěma. Ve výchozím nastavení absence tohoto slova v cyklu znamená, že krok je jediný.

Získané výsledky musí být uloženy do buněk očíslovaných (i,1). Poté, při každém spuštění smyčky, když se i zvětší o krok, číslo řádku se automaticky zvýší. Kód bude tedy optimalizován.

Celkově bude kód vypadat takto:

Podprogram ()

Pro i = 1 až 10 Krok 1 (můžete jednoduše napsat Pro i = 1 až 10)

Buňky(i, 1). Hodnota = i ^ 2 (tj. hodnota čtverce i se zapíše do buňky (i,1)

Další (v jistém smyslu hraje roli počítadla a znamená další začátek smyčky)

End Sub.

Pokud je vše provedeno správně, včetně záznamu a spuštění makra (viz pokyny výše), pak při jeho vyvolání pokaždé dostanete sloupec zadané velikosti (v tomto případě sestávající z 10 buněk).

Příklad 4

V každodenním životě je často potřeba učinit jedno nebo druhé rozhodnutí v závislosti na nějaké situaci. Ve VBA Excel se bez nich neobejdete. Příklady programů, kde je další průběh provádění algoritmu zvolen a není předem předem určen, nejčastěji používají konstrukci If ...Then (pro složité případy) If ...Then ...END If.

Podívejme se na konkrétní případ. Předpokládejme, že potřebujete vytvořit makro pro Excel, aby bylo do buňky se souřadnicemi (1,1) zapsáno následující:

1, pokud je argument kladný;

0, pokud je argument null;

-1, pokud je argument záporný.

Vytvoření takového makra pro Excel začíná standardním způsobem pomocí klávesových zkratek Alt a F11. Dále je napsán následující kód:

Podprogram ()

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

Pokud x>0, pak Cells(1, 1).Hodnota = 1

Pokud x=0, pak Cells(1, 1).Hodnota = 0

Pokud x<0 Then Cells(1, 1).Value = -1

End Sub.

Zbývá pouze spustit makro a získat požadovanou hodnotu pro argument v Excelu.

Funkce VBA

Jak jste si již mohli všimnout, programování v nejslavnějším tabulkovém procesoru Microsoftu není tak obtížné. Zvláště pokud se naučíte používat funkce VBA. Celkem má tento programovací jazyk, vytvořený speciálně pro psaní aplikací v Excelu a Wordu, asi 160 funkcí. Lze je rozdělit do několika velkých skupin. Tento:

• Matematické funkce. Když je aplikují na argument, získají hodnotu kosinu, přirozeného logaritmu, celé části atd.
• Finanční funkce. Díky jejich dostupnosti a využití programování v Excelu získáte efektivní nástroje pro účetní a finanční výpočty.
• Funkce zpracování pole. Patří mezi ně Array, IsArray; LBound; UBound.
• Funkce Excel VBA pro řetězec. To je poměrně velká skupina. Patří sem například funkce Space pro vytvoření řetězce s počtem mezer rovným argumentu celé číslo nebo Asc pro převod znaků na kód ANSI. Všechny jsou široce používané a umožňují pracovat s řádky v Excelu a vytvářet aplikace, které práci s těmito tabulkami značně usnadňují.
• Funkce převodu datových typů. Například CVar vrátí hodnotu argumentu Expression tím, že jej převede na datový typ Variant.
• Funkce pro práci s daty. Výrazně rozšiřují ty standardní Funkce WeekdayName tedy vrací název (úplný nebo částečný) dne v týdnu o jeho číslo. Ještě užitečnější je časovač. Udává počet sekund, které uplynuly od půlnoci do určitého bodu dne.
• Funkce pro převod číselného argumentu do různých číselných soustav. Například Oct vydává čísla v osmičkové soustavě.
• Formátovací funkce. Nejdůležitější z nich je Formát. Vrací hodnotu Variant s výrazem naformátovaným podle pokynů uvedených v deklaraci formátu.
• atd.

Studium vlastností těchto funkcí a jejich aplikace výrazně rozšíří záběr Excelu.

Příklad 5

Zkusme přejít k řešení složitějších problémů. Například:

Je uveden papírový dokument uvádějící skutečnou úroveň nákladů podniku. Požadovaný:

• vytvořit jeho šablonovou část pomocí tabulky Excel;
• vytvořte program VBA, který si vyžádá počáteční data k jejich vyplnění, provede potřebné výpočty a vyplní jimi odpovídající buňky šablony.

Zvažme jednu z možností řešení.

Vytvořte šablonu

Všechny akce se provádějí na standardním listu v Excelu. Volné buňky jsou vyhrazeny pro zadání údajů o názvu spotřebitelské společnosti, výši nákladů, jejich výši a obratu. Vzhledem k tomu, že počet společností (společností), pro které je sestava sestavován, není pevně daný, nejsou buňky pro zadávání hodnot na základě výsledků a jména specialisty předem rezervovány. Pracovní list dostane nový název. Například „hlášení“.

Proměnné

Chcete-li napsat program, který automaticky vyplní šablonu, musíte vybrat notace. Ty budou použity pro proměnné:

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

Označme akumulaci součtu pro tento sloupec pomocí stejných písmen, ale s „předponou“ Itog. Například ItogTP – odkazuje na sloupec tabulky s názvem „plánovaný obrat“.

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

Pomocí zavedených zápisů získáme vzorce pro odchylky. Pokud potřebujete vypočítat v %, máme (F - P) / P * 100 a celkem - (F - P).

Výsledky těchto výpočtů lze nejlépe zadat přímo do příslušných buněk v excelové tabulce.

Pro skutečné a předpovědní součty se získají pomocí vzorců ItogP=ItogP + P a ItogF=ItogF+ F.

Pro odchylky použijte = (ItogF - ItogP) / ItogP * 100, pokud se výpočet provádí v procentech, a v případě celkové hodnoty - (ItogF - ItogP).

Výsledky se opět ihned zapisují do příslušných buněk, není tedy potřeba je přiřazovat k proměnným.

Před spuštěním vytvořeného programu je potřeba sešit uložit např. pod názvem "Sestava1.xls".

Tlačítko „Vytvořit tabulku sestav“ stačí stisknout pouze jednou po zadání informací v záhlaví. Existují další pravidla, která byste měli znát. Zejména na tlačítko „Přidat řádek“ je nutné kliknout pokaždé po zadání hodnot pro každý typ aktivity do tabulky. Po zadání všech údajů je třeba kliknout na tlačítko „Dokončit“ a poté se přepnout do okna Excelu.

Nyní víte, jak řešit problémy Excelu pomocí maker. Schopnost používat vba excel (viz příklady programů výše) může být také potřeba pro práci v momentálně nejpopulárnějším textovém editoru Word. Zejména můžete vytvořit tlačítka nabídky psaním, jak je znázorněno na samém začátku článku, nebo psaním kódu, díky kterému lze provádět mnoho operací s textem stisknutím funkčních kláves nebo prostřednictvím karty „Zobrazit“ a ikonu „Makra“.

Následující jednoduché příklady maker aplikace Excel ilustrují některé funkce a techniky popsané ve výukovém programu Excel VBA.

Excel makro: Příklad 1

Zpočátku tento postup Sub byl uveden jako příklad použití komentářů v kódu VBA. Zde však můžete také vidět, jak se deklarují proměnné, jak fungují odkazy na buňky Excelu a jak se používá smyčka Pro, podmíněný operátor Li a zobrazení okna se zprávou.

"Procedura Sub hledá buňku obsahující zadaný řetězec "v rozsahu buněk A1:A100 aktivního listu Sub Find_String(sFindText As String) Dim i As Integer "Celé číslo typu Integer, používané ve smyčce For Dim iRowNumber As Integer "Celé číslo typu Integer pro uložení výsledku iRowNumber = 0 "Prohledává buňky A1:A100 jednu po druhé, dokud nenajde řetězec sFindText For i = 1 až 100 If Cells(i, 1).Value = sFindText Then " Pokud je nalezena shoda se zadaným řetězcem " uložte aktuální číslo řádku a ukončete smyčku For iRowNumber = i Exit For End If Next i "Informujte uživatele ve vyskakovacím okně, zda byl nalezen požadovaný řádek "Pokud zadaný řádek je nalezen, označte, ve které buňce byla nalezena shoda If iRowNumber = 0 Then MsgBox "Row" & sFindText & " not found" Else MsgBox "Row " & sFindText & " nalezen v buňce A" & iRowNumber End If End Sub

Excel makro: Příklad 2

Další postup Sub– příklad použití smyčky Do Zatímco. Můžete také vidět, jak jsou proměnné deklarovány, pracovat s odkazy na buňky aplikace Excel a pomocí podmíněného příkazu. Li.

"Procedura Sub vypíše Fibonacciho čísla nepřesahující 1000 Sub Fibonacci() Dim i As Integer "Počítadlo označující pozici prvku v sekvenci Dim iFib As Integer "Uloží aktuální hodnotu sekvence Dim iFib_Next As Integer "Uloží další hodnota sekvence Dim iStep As Integer "Ukládá velikost dalšího přírůstku "Inicializovat proměnné i a iFib_Next i = 1 iFib_Next = 0 "Smyčka Do While bude prováděna, dokud hodnota "aktuálního Fibonacciho čísla nepřekročí 1000 Do While iFib_Next< 1000 If i = 1 Then "Особый случай для первого элемента последовательности iStep = 1 iFib = 0 Else "Сохраняем размер следующего приращения перед тем, как перезаписать "текущее значение последовательности iStep = iFib iFib = iFib_Next End If "Выводим текущее число Фибоначчи в столбце A активного рабочего листа "в строке с индексом i Cells(i, 1).Value = iFib "Вычисляем следующее число Фибоначчи и увеличиваем индекс позиции элемента на 1 iFib_Next = iFib + iStep i = i + 1 Loop End Sub

Excel makro: Příklad 3

Tento postup Sub skenuje buňky sloupce A aktivní list, dokud nenarazí na prázdnou buňku. Hodnoty se zapisují do pole. Toto jednoduché makro Excel ukazuje práci s dynamickými poli a také použití smyčky Do dokud. V tomto příkladu nebudeme s polem provádět žádné akce, i když v reálné programátorské praxi se na nich po zápisu dat do pole obvykle takové akce provádějí.

"Procedura Sub ukládá hodnoty buněk sloupce A aktivního listu do pole Sub GetCellValues() Dim iRow As Integer "Ukládá číslo aktuálního řádku Dim dCellValues() As Double "Pole pro ukládání hodnot buněk ​​iRow = 1 ReDim dCellValues(1 až 10) „Do Loop Until iteruje sekvenčně přes buňky sloupce A aktivního listu“ a extrahuje jejich hodnoty do pole, dokud nenarazí na prázdnou buňku Do Until IsEmpty(Cells( iRow, 1)) "Zkontrolujte, zda má pole dCellValues® dostatečnou velikost "Pokud ne, zvyšte velikost pole o 10 pomocí ReDim If UBound(dCellValues)< iRow Then ReDim Preserve dCellValues(1 To iRow + 9) End If "Сохраняем значение текущей ячейки в массиве dCellValues dCellValues(iRow) = Cells(iRow, 1).Value iRow = iRow + 1 Loop End Sub

Excel makro: Příklad 4

V tomto příkladu postup Subčte hodnoty ze sloupce A pracovní list List2 a provádí na nich aritmetické operace. Výsledky se zapisují do buněk sloupců A na aktivním listu. Toto makro ukazuje použití objektů aplikace Excel. Odvolání se provádí zejména řízením Sub k objektu Sloupce a ukazuje, jak se k tomuto objektu přistupuje prostřednictvím objektu Pracovní list. Je také ukázáno, že při přístupu k buňce nebo oblasti buněk na aktivním listu není nutné při psaní odkazu zadávat název tohoto listu.

"Procedura Sub pomocí smyčky čte hodnoty ve sloupci A listu Sheet2, "provádí aritmetické operace s každou hodnotou a zapisuje výsledek do "sloupce A aktivního listu (Sheet1) Sub Transfer_ColA() Dim i As Integer Dim Col As Range Dim dVal As Double "Přiřadit proměnné Col sloupec A listu List 2 Set Col = Sheets("Sheet2").Columns("A") i = 1 "Pomocí smyčky čteme hodnoty ​​buněk sloupce Col, dokud "dokud nenarazíte na prázdnou buňku Do Until IsEmpty(Col.Cells(i)) "Proveďte aritmetické operace s hodnotou aktuální buňky dVal = Col.Cells(i).Value * 3 - 1 "Následující příkaz zapíše výsledek do sloupce A aktivního listu "Uveďte název listu v odkazu, který není nutný, protože se jedná o aktivní list Cells(i, 1) = dVal i = i + 1 Loop End Sub.

Excel makro: Příklad 5

Toto makro ukazuje příklad kódu VBA, který monitoruje událost aplikace Excel. Událost, ke které je makro připojeno, nastane pokaždé, když je na listu vybrána buňka nebo oblast buněk. V našem případě při výběru buňky B1, na obrazovce se objeví okno se zprávou.

"Tento kód zobrazí okno se zprávou, pokud je na aktuálním listu vybrána buňka B1. Private Sub Worksheet_SelectionChange(ByVal Target As Range) "Zkontrolujte, zda je vybrána buňka B1, pokud Target.Count = 1 And Target.Row = 1 And Target.Column = 2 Poté "Pokud je vybrána buňka B1, proveďte požadovanou akci MsgBox "Vybrali jste buňku B1" End If End Sub

Excel makro: Příklad 6

Tento postup ilustruje použití operátorů Při chybě A Resumé pro řešení chyb. Tento kód také ukazuje příklad otevírání a čtení dat ze souboru.

"Procedura Sub přiřadí argumentům Val1 a Val2 hodnoty buněk A1 a B1" ze sešitu Data.xlsx umístěného ve složce C:\Documents and Settings Sub Set_Values(Val1 As Double, Val2 As Double) Dim DataWorkbook As Workbook On Error GoTo ErrorHandling " Otevřete sešit s daty Set DataWorkbook = Workbooks.Open("C:\Documents and Settings\Data") "Přiřaďte proměnným Val1 a Val2 hodnoty z daného sešitu Val1 = Sheets("Sheet1 ").Cells(1, 1) Val2 = Sheets("Sheet1").Cells(1, 2) DataWorkbook.Close Exit Sub ErrorHandling: "Pokud soubor není nalezen, uživatel bude vyzván k umístění hledaného souboru " v požadované složce a poté pokračujte ve provádění makra MsgBox "Soubor Data.xlsx nebyl nalezen!" & _ "Přidejte sešit do složky C:\Documents and Settings a klikněte na tlačítko OK" Pokračovat End Sub