Mironov. Připravená makra ve VBA Excel

Málokdo ví, že první verze oblíbeného produktu Microsoft Excel se objevil v roce 1985. Od té doby prošel několika úpravami a je žádaný 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 dovednost 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ý je původně zabudován do nejznámějšího tabulkový procesor od společnosti Microsoft.

Odborníci označují jeho výhody za srovnatelnou snadnost vývoje. 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í skriptu v prostředí kancelářských aplikací.

Nevýhodou programu jsou problémy spojené s kompatibilitou různé verze. Jsou způsobeny skutečností, že programový kód VBA odkazuje funkčnost, které jsou přítomny v nová verze produkt, ale ve starém chybí. Mezi nevýhody patří také příliš vysoká otevřenost kódu pro změny. od outsidera. Nicméně Microsoft Office, stejně jako IBM Lotus Symphony, umožňují uživateli zašifrovat počáteční kód a nastavit heslo pro jeho zobrazení.

Objekty, kolekce, vlastnosti a metody

Právě těmto pojmům musí rozumět ti, kdo se chystají pracovat v prostředí VBA. Nejprve musíte pochopit, co je objekt. V Excelu je to list, sešit, buňka a rozsah. Tyto objekty mají zvláštní hierarchii, tzn. jsou si navzájem podřízeny.

Hlavní je Aplikace, která odpovídá většině Excel program. Pak přijdou Pracovní sešity, Pracovní listy a také 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. Její jednotlivé prvky jsou také 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 musí být odděleny od objektu 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. Jeho provedení znamená vymazání obsahu vybrané buňky.

Jak začít

Poté je třeba přejít do aplikace VB, pro kterou stačí použít kombinaci kláves „Alt“ a „F11“. Dále:

• 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;
• napište, řekněme, náčrt kódu.

Vypadá to takto:

podprogram()

„Náš kód

Všimněte si, že řádek „Náš kód“ bude zvýrazněn jinou barvou (zeleně), důvodem je apostrof na začátku řádku, který označuje, že následuje komentář.

Nyní můžete napsat libovolný kód a vytvořit si ho pro sebe nový nástroj ve VBA Excel (viz příklady programů níže). Pro ty, kteří jsou obeznámeni se základy Visual Basicu, to bude samozřejmě mnohem jednodušší. Nicméně i ti, kteří je nemají, se na přání budou moci dostat do pohodlí dostatečně rychle.

Makra v Excelu

Tento název skrývá programy napsané v jazyce Visual Basic for Application. Programování v Excelu je tedy vytváření maker s správný kód. Díky této schopnosti se tabulka Microsoftu sama vyvíjí a přizpůsobuje se potřebám konkrétního uživatele. Poté, co jste přišli na to, jak vytvořit moduly pro psaní maker, můžete začít uvažovat konkrétní příklady programy VBA Vynikat. 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ě zapíše do druhé.

Pro tohle:

• otevřete kartu "Zobrazit";
• přejděte na ikonu "Makra";
• klikněte na "Zaznamenat makro";
• vyplňte otevřený formulář.

Pro zjednodušení ponechte v poli „Název makra“ „Macro1“ a do pole „Klávesová zkratka“ vložte např. hh (to znamená, že program spustíte bleskový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.

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

V důsledku toho dojde k akci, která byla provedena během 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 „Upravit“ nebo „Zadat“. 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 provedeno kopírování 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 skočí do VBA Excel, do buňky C1.

Příkaz ActiveSheet.Paste dokončí aktivní část kódu. Znamená to zaznamenat obsah vybrané buňky (v tento případ A1) do zvýrazněné 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 existuje funkce y=x + x 2 + 3x 3 - cos(x). Chcete-li získat jeho plán, musíte vytvořit makro. To lze provést pouze pomocí smyček VBA.

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

Všechny příklady maker Excel VBA jsou vytvořeny podle stejného postupu jako 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 aktivní);

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

endsub.

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.

Poté se na nich sestaví graf způsobem, který je standardní 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 čísla odpovídajícího řá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 napíšeme samotný kód. Věříme, že nás zajímají hodnoty pro 10 buněk. Kód vypadá takto.

Pro i = 1 až 10 Další

Příkaz se překládá do „lidského“ jazyka jako „Opakujte od 1 do 10 v krocích po jedné“.

Pokud je úkolem získat sloupec se čtverci, například všechna lichá čísla 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 je nutné uložit do buněk s číslem (i,1). Potom při každém spuštění smyčky se zvýšením i o hodnotu kroku automaticky vzroste i číslo řádku. Kód bude tedy optimalizován.

Obecně bude kód vypadat takto:

podprogram()

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

Buňky (i, 1). Hodnota = i ^ 2

Další (v jistém smyslu funguje jako počítadlo a znamená další začátek smyčky)

endsub.

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

Příklad 4

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

Zvážit konkrétní případ. Předpokládejme, že potřebujete vytvořit makro pro "Excel", aby byla buňka se souřadnicemi (1,1) zapsána:

1, pokud je argument kladný;

0, pokud je argument null;

-1, pokud je argument záporný.

Začíná vytváření takového makra pro Excel standardním způsobem pomocí "horkého" Klávesy 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

endsub.

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

Funkce VBA

Jak jste si mohli všimnout, programování v nejslavnější tabulce Microsoftu není tak těžké. 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 přítomnosti a využití programování v Excelu můžete získat efektivní nástroje pro účetnictví a finanční výpočty.
• Funkce zpracování pole. Patří mezi ně Array, IsArray; lBound; UBound.
• Funkce VBA Excel pro řádek. To je poměrně velká skupina. Obsahuje například funkce Space pro vytvoření řetězce s počtem mezer rovným celočíselnému argumentu nebo Asc pro převod znaků na kód ANSI. Všechny jsou široce používány a umožňují pracovat s řádky v Excelu a vytvářet aplikace, které práci s těmito tabulkami výrazně usnadní.
• 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 (celý nebo částečný) dne v týdnu jeho číslem. 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 udává osmičkovou reprezentaci čísla.
• Formátovací funkce. Nejdůležitější z nich je Formát. Vrací hodnotu typu 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ý doklad zprávy o skutečné výši nákladů podniku. Požadované:

• vytvořit jeho šablonovou část pomocí tabulky Excel;
• vytvořte VBA program, 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 jedno z ř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 celé jméno specialisty předem rezervovány. Pracovní list dostane nový název. Například "Օtchet".

Proměnné

Chcete-li napsat program, který automaticky vyplní šablonu, musíte zvolit notaci. Budou použity pro proměnné:

• NN - číslo 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čme stejnými písmeny, ale s "prefixem" Itog akumulaci součtu na daném sloupci. Například ItogTP odkazuje na sloupec tabulky s názvem „plánovaný obrat“.

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

Pomocí zavedeného zápisu získáme vzorce pro odchylky. Pokud je nutné provést výpočet v %, máme (F - P) / P * 100 a ve výši - (F - P).

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

Pro skutečné a předpovědní součty se získá 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 uložit pracovní sešit, například s názvem "Sestava1.xls".

Tlačítko "Vytvořit tabulku přehledů" stačí stisknout pouze 1krát po zadání informací v záhlaví. Je třeba si uvědomit další pravidla. Zejména na tlačítko "Přidat řádek" je nutné kliknout pokaždé po zadání hodnot pro každou aktivitu do tabulky. Po zadání všech údajů je třeba kliknout na tlačítko "Dokončit" a poté se přepnout do okna "Excel".

Nyní víte, jak řešit problémy pro Excel pomocí maker. Schopnost používat vba excel (viz příklady programů výše) může být potřeba i pro práci v prostředí aktuálně nejpopulárnějšího textového editoru „Word“. Tlačítka menu můžete vytvářet zejména psaním, jak je znázorněno na samém začátku článku, nebo psaním kódu, díky kterému lze mnoho operací s textem provádět stisknutím funkčních kláves nebo přes záložku „Zobrazit“ a ikonu "Makra".

Práce s knihami

Makro 1: Vytvořte nový sešit od začátku
Makro 2. Při změně konkrétní buňky/rozsahu uložte sešit
Makro 3: Uložte sešit před zavřením
Makro 4: Ochrana listu v sešitu před zavřením
Makro 5: Zrušte ochranu listu při otevírání souboru aplikace Excel
Makro 6. Otevřete knihu na požadovaném listu
Makro 7: Otevření konkrétního sešitu definovaného uživatelem
Makro 8. Zjistěte, zda je kniha otevřená
Makro 9. Zjistěte, zda ve složce existuje kniha
Makro 10. Aktualizujte všechny odkazy v otevřených sešitech
Makro 11. Zavřete všechny knihy najednou
Makro 12. Otevřete všechny sešity ve složce
Makro 13. Tisk všech knih ve složce
Makro 14. Nenechávejte knihu zavřít, dokud není buňka plná
Makro 15. Zálohujte aktuální sešit s dnešním datem

Práce s listy

Makro 16. Přidání nového listu a přiřazení názvu
Makro 17. Odstraňte všechny listy kromě aktivního
Makro 18. Skryjte vše kromě aktivního listu
Makro 19. Zobrazte všechny listy knihy
Makro 20: Přesouvání listů
Makro 21. Třídění listů podle názvu
Makro 22. Seskupit listy podle barvy karty
Makro 23. Zkopírujte list do nového sešitu
Makro 24: Vytvořte nový sešit pro každý list
Makro 25 - Tisk listů
Makro 26. Chraňte všechny listy
Makro 27. Zrušte ochranu všech listů
Makro 28: Vytvořte obsah
Makro 29: Přiblížení listu dvojitým kliknutím
Makro 30. Vyberte aktivní sloupec řádku

Výběr a úprava rozsahů

Makro 31: Výběr a formátování rozsahu
Makro 32: Vytvořte a vyberte pojmenované rozsahy
Makro 33. Iterace přes řadu buněk
Makro 34: Výběr a formátování rozsahů
Makro 35. Vložte prázdné řetězce do rozsahu
Makro 36. Zobrazit všechny skryté řádky a sloupce
Makro 37 – Odstraňte prázdné řádky
Makro 38 – Odstraňte prázdné sloupce
Makro 39: Výběr a formátování všech vzorců v sešitu
Makro 40. Najděte a vyberte první prázdný řádek nebo sloupec
Makro 41: Aplikujte sekundární barvu výplně
Makro 42. Třídění rozsahů poklepáním
Makro 43. Omezení rozsahu posouvání v určité oblasti
Makro 44. Automaticky nastavte oblast tisku listu

Práce s daty

Makro 45: Zkopírujte a vložte rozsah
Makro 46: Převeďte všechny vzorce v rozsahu na hodnoty
Makro 47. Převeďte textové hodnoty na čísla
Makro 48. Převeďte pomlčky na mínus
Makro 49. Odstraňte nadbytečné mezery ze všech buněk v rozsahu
Makro 50. Ořízněte levých 5 znaků v každé buňce rozsahu
Makro 51. Přidejte do buňky chybějící nuly
Makro 52. Nahraďte prázdné buňky nulou
Makro 53: Přidání textu na začátek nebo konec buňky
Makro 54: Vytvořte makro transformace dat
Makro 55 – Vymazání dat (netisknutelné znaky)
Makro 56. Zvýrazněte duplikáty v rozsahu dat
Makro 57. Skryjte duplicitní řádky
Makro 58. Selektivně skryjte šipky automatického filtru
Makro 59. Zkopírujte filtrované řádky do nového sešitu
Makro 60: Vytvořte nový list pro každou položku v automatickém filtru
Makro 61 – Zobrazí filtrované sloupce ve stavovém řádku

Práce s kontingenčními tabulkami

Makro 62: Vytvoření kontingenční tabulky zpětně kompatibilní
Makro 63: Obnovte všechny kontingenční tabulky v sešitu
Makro 64. Vytvoření "inventáře" všech kontingenčních tabulek knihy
Makro 65: Vytvořte všechny kontingenční tabulky pomocí stejné mezipaměti dat
Makro 66. Skryjte všechny mezisoučty v kontingenční tabulce
Makro 67. Změňte názvy dat všech polí souhrnu
Makro 68 – Vynutit součtování pro všechna pivotní data
Makro 69: Aplikujte číselný formát na všechny datové položky
Makro 70: Seřaďte klíčová pole abecedně
Makro 71: Použít vlastní řazení na datové položky
Makro 72: Ochrana kontingenční tabulky
Makro 73: Použít omezení kontingenčního pole
Makro 74. Automatické mazání listů s detailním shrnutím
Makro 75: Tisk kontingenční tabulky pro každý prvek filtru
Makro 76: Vytvořte nový soubor pro každý prvek filtru
Makro 77. Příprava rozsahu dat pro kontingenční tabulku

Práce s tabulkami a grafy

Makro 78 – Změna velikosti grafů na listu
Makro 79. Spojte graf s určitým rozsahem
Makro 80: Vytvořte sadu neuspořádaných grafů
Makro 81: Tisk všech grafů na list
Makro 82. Označení nejlepší a nejhorší hodnoty na
Makro 83 - Stejné barvy pro hodnoty v různých grafech
Makro 84. Korespondence barvy grafů s barvou rozsahů

Odesílání e-mailů z Excelu

Makro 85 – Odeslat aktivní sešit poštou (příloha)
Makro 86 - Odeslání rozsahu hodnot jako přílohy
Makro 87: Odeslání jednoho listu jako přílohy
Makro 88. Pošlete e-mail s odkazem na naše soubory
Makro 89: Odeslání přidání adres do našeho seznamu kontaktů
Makro 90: Uložte všechny přílohy do samostatné složky
Makro 91: Uložení konkrétních příloh do složky

Interakce s jinými aplikacemi Office

Makro 92 – Spusťte přístupový dotaz z Excelu



Makro 96 – Komprimujte databázi Accessu z Excelu
Makro 97: Odesílání dat aplikace Excel do dokumentu aplikace Word
Makro 98. Sloučení s dokumentem aplikace Word
Makro 99: Odesílání dat aplikace Excel do prezentace PowerPoint
Makro 100. Odesílání grafů Excel do prezentací PowerPoint
Makro 101: Převést sešit na prezentaci v PowerPointu

Interakce s jinými aplikacemi Office
Naučte se pracovat s Wordem, Accessem a PowerPointem
Makro 92 – Spusťte přístupový dotaz z Excelu
Makro 93: Spusťte makro Access z Excelu
Makro 94: Otevření sestavy Accessu z Excelu
Makro 95 – Otevření přístupového formuláře z Excelu

Chcete-li automatizovat opakující se úlohy, můžete zaznamenat makro pomocí Záznamníku maker v aplikaci Microsoft Excel. Představte si, že máte data v náhodných formátech a chcete použít stejný formát na všechny. To lze provést pomocí makra. Makro můžete zaznamenat v požadovaném formátu a v případě potřeby jej přehrát.

Když zaznamenáte makro, všechny potřebné akce se zaznamenají jako kód jazyka Visual Basic for Applications (VBA). Tyto akce mohou zahrnovat zadávání textu nebo čísel, výběr buněk nebo příkazů na pásu karet nebo v nabídce, formátování buněk, řádků nebo sloupců a dokonce import dat z externího zdroje, jako je Microsoft Access. Visual Basic for Applications (VBA) je součástí programovacího jazyka Visual Basic. Je k dispozici ve většině Kancelářské aplikace. VBA sice umožňuje automatizovat procesy v rámci aplikací Office a mezi nimi, ale nepotřebujete umět programovat ani znát jazyk VBA, protože záznam maker se o vše postará.

Je důležité vědět, že když nahráváte makro, zaznamenává se téměř vše, co děláte. Pokud tedy uděláte chybu, například stisknete špatné tlačítko, záznamník maker tuto akci zaznamená. V takovém případě můžete přepsat celou sekvenci nebo upravit kód VBA. Proto před záznamem procesu byste jej měli dobře propracovat. Čím přesněji napíšete sekvenci, tím efektivněji bude makro fungovat.

Vývojář, která je ve výchozím nastavení skrytá, takže ji musíte nejprve povolit. Další informace najdete v tématu Zobrazení karty Vývojář.

Makro záznam

Na kartě Vývojář klikněte Makra zobrazíte makra přidružená k sešitu. Můžete také stisknout tlačítka ALT+F8. Tím se otevře dialogové okno. Makro.

Pozornost:

Práce s napsaným kódem v editoru Visual Basic (VBE)

Pomocí editoru jazyka Visual Basic (VBE) můžete do zaznamenaného kódu přidat své vlastní proměnné, řídicí struktury a další prvky, které záznamník maker nepodporuje. Vzhledem k tomu, že makrorekordér zachytí téměř každý krok, ke kterému dojde během nahrávání, možná budete muset odstranit nepotřebný kód. Prohlížení zaznamenaného kódu je skvělý způsob, jak se naučit programování VBA nebo zdokonalit své dovednosti.

Příklad úpravy zaznamenaného kódu najdete v článku Začínáme s VBA v Excelu.

Makro záznam

Před záznamem maker je užitečné vědět následující:

Makro napsané pro práci v rozsahu aplikace Excel poběží pouze na buňkách v tomto rozsahu. Pokud tedy do rozsahu přidáte nový řádek, makro se na něj nepoužije.

Pokud potřebujete zaznamenat dlouhou sekvenci úkolů, doporučujeme místo toho použít několik menších maker.

Makro může obsahovat i jiné úlohy než Excel. Proces makra může zahrnovat další aplikace Office a další programy, které podporují Visual Basic for Applications (VBA). Můžete například zaznamenat makro, které nejprve aktualizuje tabulku v Excelu a poté otevře Outlook a pošle jej e-mailem.

Makra a nástroje VBA jsou na kartě Vývojář, která je ve výchozím nastavení skrytá, takže ji musíte nejprve povolit.

Vybrat vynikat > Možnosti> Páska a panel.

Chcete-li zaznamenat makro, postupujte podle pokynů níže.

Práce s makry zaznamenanými v Excelu

Na kartě Vývojář klikněte Makra zobrazíte makra přidružená k sešitu. Tím se otevře dialogové okno. Makro.

Poznámka: Makra nelze vrátit zpět. Před prvním spuštěním zaznamenaného makra uložte nebo vytvořte kopii sešitu, abyste předešli nechtěným změnám. Pokud nejste spokojeni s výsledky spuštění makra, můžete sešit zavřít bez jeho uložení.

Níže jsou uvedeny dodatečné informace o práci s makry v Excelu.

dodatečné informace

Vždy můžete položit otázku komunitě Excel Tech Community , požádat o pomoc v komunitě odpovědí a také navrhnout nová vlastnost nebo vylepšení na webu

Makra v aplikaci Microsoft Excel mohou výrazně urychlit práci s dokumenty v tomto tabulkovém editoru. Toho je dosaženo automatizací opakujících se akcí zapsaných ve speciálním kódu. Pojďme se podívat na to, jak vytvářet makra v Excelu a jak je lze upravovat.

Makro lze zaznamenat dvěma způsoby:

• automaticky;
• ručně.

Pomocí první možnosti jednoduše zaznamenáte určité akce program Microsoft Excel, který právě používáte. Poté si můžete tuto nahrávku přehrát. Tato metoda velmi lehký a nevyžaduje znalost kódu, ale jeho praktické použití je dosti omezené.

Ruční nahrávání maker naopak vyžaduje znalosti programování, protože kód se píše ručně z klávesnice. Ale dobře napsaný kód tímto způsobem může výrazně urychlit provádění procesů.

Automatický záznam makra

Než budete moci spustit automatický záznam maker, musíte .

Otevře se okno nastavení záznamu maker. Zde můžete zadat libovolný název makra, pokud vám nevyhovuje výchozí. Hlavní věc je, že název by měl začínat písmenem, nikoli číslicí. V názvu by také neměly být žádné mezery. Ponechali jsme výchozí název - "Macro1".

Zde si na přání můžete nastavit klávesovou zkratku, po stisku se makro spustí. První klíč musí být klávesa Ctrl, a druhý klíč si nastavuje uživatel sám. Jako příklad nastavíme například klávesu M.

Dále je třeba určit, kam bude makro uloženo. Ve výchozím nastavení bude uložen ve stejné knize (souboru), ale pokud chcete, můžete nastavit uložení v nové knize nebo v samostatné knize maker. Ponecháme výchozí hodnotu.

V nejnižším poli nastavení makra můžete ponechat libovolný popis tohoto makra, který je vhodný pro daný kontext. Není to však nutné.

Po dokončení všech nastavení klikněte na tlačítko "OK".

Poté budou všechny vaše akce v tomto excelovém sešitu (souboru) zaznamenány v makru, dokud nepřestanete zaznamenávat sami sebe.

Zapišme si například nejjednodušší aritmetickou operaci: sečtení obsahu tří buněk (=C4+C5+C6).

Poté klikněte na tlačítko „Zastavit nahrávání“. Toto tlačítko bylo převedeno z tlačítka „Nahrát makro“, když je povoleno nahrávání.

Spuštění makra

Chcete-li zkontrolovat, jak zaznamenané makro funguje, klikněte na tlačítko „Makra“ ve stejném bloku nástrojů „Kód“ nebo stiskněte kombinaci kláves Alt + F8.

Poté se otevře okno se seznamem zaznamenaných maker. Hledáme makro, které jsme zaznamenali, vybereme ho a klikneme na tlačítko "Spustit".

Můžete to udělat ještě jednodušeji a dokonce ani nevyvolat okno pro výběr makra. Pamatujeme si, že jsme zaznamenali kombinaci „horkých kláves“ pro rychlé volání makra. V našem případě je to Ctrl+M. Tuto kombinaci napíšeme na klávesnici, načež se makro spustí.

Jak vidíte, makro provedlo přesně všechny akce, které byly zaznamenány dříve.

Úprava maker

Chcete-li makro upravit, klikněte znovu na tlačítko "Makra". V okně, které se otevře, vyberte požadované makro a klikněte na tlačítko "Upravit".

Otevře se Microsoft Visual Basic (VBE), prostředí, kde se upravují makra.

Každý záznam makra začíná příkazem Sub a končí příkazem End Sub. Bezprostředně za příkazem Sub je název makra. Příkaz "Range("...").Select určuje výběr buňky. Například příkaz "Range("C4).Select" vybere buňku C4. Operátor "ActiveCell.FormulaR1C1" se používá k záznamu akcí ve vzorcích a pro další výpočty.

Zkusme trochu změnit makro. Chcete-li to provést, přidejte do makra výraz:

Rozsah("C3").Vyberte
ActiveCell.FormulaR1C1 = "11"

Výraz "ActiveCell.FormulaR1C1 = "=R[-3]C+R[-2]C+R[-1]C"" bude nahrazen výrazem "ActiveCell.FormulaR1C1 = "= R[-4]C+R [-3]C+R[-2]C+R[-1]C"".

Zavřete editor a spusťte makro, stejně jako minule. Jak vidíte, díky změnám, které jsme zavedli, přibyla další buňka s daty. Byl také zahrnut do výpočtu součtu.

Pokud je makro příliš velké, jeho provedení může trvat dlouho. Ale představením ruční změna do kódu, můžeme proces urychlit. Přidejte příkaz "Application.ScreenUpdating = False". To zůstane výpočetní výkon a tím urychlit práci. Toho je dosaženo neaktualizováním obrazovky během výpočetních kroků. Pro obnovení aktualizace po provedení makra napíšeme na konci příkaz "Application.ScreenUpdating = True"

Přidáme také příkaz "Application.Calculation = xlCalculationManual" na začátek kódu a na konec kódu přidáme "Application.Calculation = xlCalculationAutomatic". Tím na začátku makra vypneme automatické přepočítávání výsledku po každé změně v buňkách a na konci makra jej zapneme. Excel tedy výsledek spočítá pouze jednou a nebude jej neustále přepočítávat, což ušetří čas.

Psaní kódu makra od začátku

Pokročilí uživatelé mohou nahraná makra nejen upravovat a optimalizovat, ale také psát kód makra od začátku. Abyste mohli začít, musíte kliknout na tlačítko „Visual Basic“, které se nachází na samém začátku pásu karet vývojáře.

Poté se otevře známé okno editoru VBE.

Programátor tam zapíše kód makra ručně.

Jak vidíte, makra v aplikaci Microsoft Excel mohou výrazně urychlit provádění rutinních a monotónních procesů. Ale ve většině případů jsou pro to vhodnější ručně kódovaná makra než automaticky zaznamenávané akce. Kromě toho lze kód makra optimalizovat pomocí editoru VBE, aby se urychlil proces dokončení úkolu.

VBA je považován za standardní skriptovací jazyk aplikace společnosti Microsoft a nyní je součástí všech aplikací Office a dokonce i aplikací od jiných společností. Jakmile tedy zvládnete jazyk VBA pro Excel, můžete se vrhnout přímo na vytváření maker pro jiné softwarové programy. produkty společnosti Microsoft. Navíc můžete vytvořit kompletní softwarových produktů které současně využívají funkce různých 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 Nastavení pásu karet, 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ší.

se objeví na pásu karet nová vložka Vývojář s ovládacími prvky automatizace Excel.

Psaní maker v Excelu

V záložce Vývojář ve skupině Kód, zmáčknout tlačítko Makro záznam. Zobrazí se dialogové okno makro záznam, 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. S tento moment Excel zachytí 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 záznam makra zastavit, klikněte na tlačítko . zastavit nahrávání, která je ve stejné skupině Kód.

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

Nyní můžete kliknutím na tlačítko zobrazit seznam všech vytvořených maker makro, být 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 toto okno spustíte stisknutím Alt + F8.

Úprava maker

Gratulujeme! Napsali jste své první makro. Bylo by logické nyní zkontrolovat, jaký kód nám Excel vygeneroval. Vygenerovaný kód je zapsán jazyk 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í tab Vývojář.

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

Nabízím za tuto fázi zjistěte více o různých oknech a nabídkách editoru VBA. Později vám to ušetří spoustu času.

Pro zobrazení kódu klikněte na větev 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. Řekněme například, že chcete naplnit určitý sloupec hodnotami od 1 do 10. Již máte za sebou první tři kroky, které zadávají hodnoty 1, 2 a 3 do prvních tří buněk sloupce A. Musíme přidat zbývajících sedm kroků.

Pokud se podíváte na výše uvedený kód, 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". Pro zbývající kroky tedy můžeme tyto kroky zopakovat, přičemž změníme 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:

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

Po dokončení úprav sešit uložte. Makro můžete spustit stisknutím tlačítka F5 nebo návratem do práce Excelový sešit, 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 prozkoumání kódu generovaného aplikací Excel. Pokud jste začátečník, investování několika minut do naučení kódu vám umožní později se seznámit s objekty VBA. Vezměte prosím na vědomí, že příklad, který jsme uvažovali, je pouze ilustrativní. Existují rychlejší a efektivní způsoby dosažení podobných výsledků, o kterých budeme hovořit později.

Zvýšení rychlosti provádění maker aplikace Excel

Zatím je vše dobré. Podívejme se na pár triků, které pomohou urychlit provádění makra. Zvažte fragment kódu výše jako příklad. Moderní počítače projděte dotyčný kód tak rychle, že si toho ani nevšimnete. Ale co když potřebujete provést operaci 50 000krát. Chvíli to potrvá. Pokud je makro, které píšete, dlouhé stovky řádků, můžete urychlit provádění kódu odříznutím některých procesů, které se při provádění makra nepoužívají.

Pomocí příkazu Application.ScreenUpdating

Prvním trikem je odmítnout aktualizaci obrazovky v době provádění makra. 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.

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, ovlivňuje 10 000 dalších buněk, Excel se je pokusí všechny 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. Abyste tomu zabránili, můžete aplikaci nastavit. Výpočet na začátku kódu, který přepne přepočet vzorce na manuální režim a poté vraťte automatický výpočet na konec makra.

Buďte opatrní, nezapomeňte tuto možnost přepnout zpět na automatický režim na konci makra. 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é nahrávání makra, můžete si všimnout, že Excel velmi často používá příkaz pro výběr buňky, jako je Range("A1").Select. V našem příkladu jsme použili daný příkaz několikrát vybrat buňku a změnit její hodnotu. Tomu se můžete vyhnout jednoduchým zadáním adresy buňky a jejím nastavením na požadovanou hodnotu (Makro zaznamenávalo 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.

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 Excel

Níže je uvedeno několik ukázkových kódů VBA, které vám pomohou automatizovat nejčastěji se opakující úlohy.