Jak zaznamenat makro v aplikaci Excel - průvodce krok za krokem

I když jste úplným nováčkem ve světě aplikace Excel VBA, můžete snadno zaznamenat makro a automatizovat část své práce.

V tomto podrobném průvodci se budu zabývat vším, co potřebujete vědět, abyste mohli začít s nahráváním a používáním maker v Excelu.

Pokud máte zájem naučit se VBA jednoduchým způsobem, podívejte se na můj Online školení Excel VBA.

Co je to makro?

Pokud jste ve VBA nováčkem, nejprve vám řeknu, co je to makro - koneckonců tento výraz budu v celém tutoriálu používat i nadále.

Makro je kód napsaný ve VBA (Visual Basic for Applications), který vám umožní spustit kus kódu při každém spuštění.

Často najdete lidi (včetně mě), kteří označují kód VBA jako makro - ať už je generován pomocí záznamníku maker nebo byl napsán ručně.

Když zaznamenáváte makro, Excel bedlivě sleduje kroky, které provádíte, a zaznamenává si je v jazyce, kterému rozumí - což je VBA.

A protože Excel je opravdu dobrým příjemcem poznámek, vytváří velmi podrobný kód (jak uvidíme dále v tomto tutoriálu).

Když zastavíte nahrávání, uložíte makro a spustíte ho, Excel se jednoduše vrátí ke generovanému kódu VBA a postupuje podle stejných kroků.

To znamená, že i když o VBA nic nevíte, můžete některé úkoly zautomatizovat pouhým ponecháním aplikace Excel zaznamenat vaše kroky jednou a poté je znovu použít.

Nyní se ponoříme a uvidíme, jak zaznamenat makro v aplikaci Excel.

Získání karty Vývojář na pásu karet

Prvním krokem k záznamu makra je získání karty Vývojář na pásu karet.

Pokud již na pásu karet vidíte kartu vývojáře, přejděte k další části, jinak postupujte podle následujících kroků:

• Klikněte pravým tlačítkem na některou ze stávajících karet na pásu karet a klikněte na možnost „Přizpůsobit pás karet“. Otevře se dialogové okno Možnosti aplikace Excel.
• V dialogovém okně Možnosti aplikace Excel budete mít možnosti Přizpůsobit pás karet. Vpravo v podokně Hlavní karty zaškrtněte možnost Vývojář.
• Klikněte na OK.

Výše uvedené kroky by zpřístupnily kartu Vývojář v oblasti pásu karet.

Záznam makra v aplikaci Excel

Nyní, když máme vše na svém místě, pojďme se naučit, jak zaznamenat makro v aplikaci Excel.

Zaznamenejme velmi jednoduché makro - takové, které vybere buňku a zadá do ní text „Excel“. Při záznamu tohoto makra používám text „Excel“, ale můžete zadat své jméno nebo jiný text, který se vám líbí.

Zde jsou kroky k zaznamenání tohoto makra:

1. Klikněte na kartu Vývojář.
2. Ve skupině Kód klikněte na tlačítko Makro. Otevře se dialogové okno „Nahrát makro“.
3. V dialogovém okně Záznam makra zadejte název makra. Používám jméno EnterText. Při pojmenovávání makra je třeba dodržovat některé podmínky pojmenování. Například nemůžete použít mezery mezi nimi. Obvykle dávám přednost tomu, aby moje jména maker byla jako jediné slovo, s různými částmi s první abecedou s velkými písmeny. Podtržítko můžete také použít k oddělení dvou slov - například Enter_Text.
4. (Volitelný krok) Pokud chcete, můžete přiřadit klávesovou zkratku. V tomto případě použijeme zkratku Control + Shift + N. Pamatujte, že zkratka, kterou zde přiřadíte, přepíše všechny existující zkratky ve vašem sešitu. Pokud například přiřadíte zkratku Control + S, nebudete ji moci použít pro uložení sešitu (místo toho pokaždé, když ji použijete, spustí makro).
5. V možnosti „Uložit makro do“ zkontrolujte, zda je vybrána možnost „Tento sešit“. Tento krok zajišťuje, že je makro součástí sešitu. Bude tam, když ji uložíte a znovu otevřete, nebo dokonce když ji s někým budete sdílet.
6. (Volitelný krok) Zadejte popis. Obvykle to nedělám, ale pokud jste extrémně organizovaní, můžete přidat, o čem to makro je.
7. Klikněte na OK. Jakmile kliknete na OK, začne zaznamenávat vaše akce v Excelu. Na kartě Vývojář můžete vidět tlačítko „Zastavit nahrávání“, které označuje, že probíhá nahrávání makra.
8. Vyberte buňku A2.
9. Zadejte text Vynikat (nebo můžete použít své jméno).
10. Stiskněte klávesu Enter. Tím vyberete buňku A3.
11. Klikněte na tlačítko Zastavit nahrávání na kartě Vývojář.

Gratulujeme!

Právě jste zaznamenali své první makro v aplikaci Excel. Už nejsi makro panna.

Makro sice nedělá nic užitečného, ​​ale svůj účel vysvětlí, jak funguje záznamník maker v Excelu.

Pojďme tedy nyní toto makro otestovat.

Při testování makra postupujte podle následujících pokynů:

1. Odstraňte text v buňce A2. Toto má otestovat, jestli makro vloží text do buňky A2 nebo ne.
2. Vyberte libovolnou buňku - jinou než A2. Toto má otestovat, zda makro vybere buňku A2 nebo ne.
3. Klikněte na kartu Vývojář.
4. Ve skupině Kód klikněte na tlačítko Makra.
5. V dialogovém okně Makro klikněte na Název makra - EnterText.
6. Klikněte na tlačítko Spustit.

Všimnete si, že jakmile kliknete na tlačítko Spustit, text „Excel“ se vloží do buňky A2 a vybere se buňka A3.

Nyní se to všechno může stát během zlomku sekundy, ale ve skutečnosti makro - stejně jako poslušný elf - postupovalo přesně podle kroků, které jste mu ukázali při záznamu makra.

Makro tedy nejprve vybere buňku A2 a poté zadá text Vynikat v něm a poté vybere buňku A3.

Poznámka: Makro můžete také spustit pomocí klávesové zkratky Control + Shift + N. (podržte klávesy Control a Shift a poté stiskněte klávesu N). Je to stejná klávesová zkratka, jakou jsme makru přiřadili při záznamu.

Co dělá záznam makra v backendu

Nyní se podívejme na backend aplikace Excel - VB editor - a podívejme se, co záznam makra skutečně dělá.

Zde jsou kroky k otevření editoru VB v aplikaci Excel:

1. Klikněte na kartu Vývojář.
2. Ve skupině Kód klikněte na tlačítko Visual Basic.

Nebo můžete použít klávesovou zkratku - ALT + F11 (místo výše uvedených dvou kroků podržte klávesu ALT a stiskněte F11). Tato zkratka také otevře stejný editor VB.

Pokud nyní vidíte editor VB poprvé, nenechte se ohromit.

Dovolte mi, abych vás rychle seznámil s anatomií editoru VB.

• Lišta menu: Zde máte všechny možnosti editoru VB. Považujte to za stuhu VBA. Obsahuje příkazy, které můžete použít při práci s editorem VB.
• Panel nástrojů - Je to jako panel nástrojů Rychlý přístup editoru VB. Dodává se s několika užitečnými možnostmi a můžete k nim přidat další možnosti. Jeho výhodou je, že možnost na panelu nástrojů je vzdálená pouhé kliknutí.
• Okno Průzkumníka projektu - Zde Excel uvádí všechny sešity a všechny objekty v každém sešitu. Pokud například máme sešit se 3 pracovními listy, zobrazí se v Průzkumníku projektů. Zde jsou některé další objekty, jako jsou moduly, uživatelské formuláře a moduly třídy.
• Okno s kódem - Zde se zaznamenává nebo zapisuje kód VBA. Pro každý objekt uvedený v Průzkumníkovi projektu existuje okno kódu - například pracovní listy, sešity, moduly atd. Později v tomto kurzu uvidíme, že zaznamenané makro přejde do okna kódu modulu.
• Okno vlastností - V tomto okně můžete vidět vlastnosti každého objektu. Toto okno často používám k pojmenování objektů nebo změně skrytých vlastností. Toto okno se vám nemusí zobrazit, když otevřete editor VB. Chcete -li to zobrazit, klikněte na kartu zobrazení a vyberte Okno vlastností.
• Okamžité okno - Při psaní kódu často používám okamžité okno. Je to užitečné, když chcete otestovat některá prohlášení nebo při ladění. Ve výchozím nastavení nemusí být viditelný a můžete jej zobrazit kliknutím na kartu Zobrazit a výběrem možnosti Okamžité okno.

Když jsme zaznamenali makro - EnterTextV editoru VB se staly následující věci:

• Byl vložen nový modul.
• Bylo zaznamenáno makro s názvem, který jsme zadali - EnterText
• Kód byl zapsán do okna kódu modulu.

Pokud tedy dvakrát kliknete na modul (v tomto případě na Modul 1), zobrazí se okno s kódem, jak je uvedeno níže.

Zde je kód, který nám dal záznam makra:

Sub EnterText () '' EnterText Macro '' Klávesová zkratka: Ctrl+Shift+N 'Range ("A2"). Vyberte ActiveCell.FormulaR1C1 = "Excel" Range ("A3"). Vyberte End Sub

Ve VBA jakýkoli řádek, který následuje po ‘ (znak apostrofu) se neprovede. Je to komentář, který je určen pouze pro informační účely. Pokud odeberete prvních pět řádků tohoto kódu, bude makro stále fungovat podle očekávání.

Nyní mi dovolte rychle popsat, co každý řádek kódu dělá:

Kód začíná na Sub a za ním název makra a prázdné závorky. Sub je zkratka pro podprogram. Každý podprogram (také nazývaný Procedura) ve VBA začíná Sub a končí End Sub.

• Rozsah („A2“). Vyberte - Tento řádek vybere buňku A2.
• ActiveCell.FormulaR1C1 = „Excel“ - tento řádek zadá do aktivní buňky text Excel. Protože jsme jako první krok vybrali A2, stane se naší aktivní buňkou.
• Rozsah („A3“) Vyberte - Tím se vybere buňka A3. K tomu dochází, když po zadání textu stiskneme klávesu Enter, jejímž výsledkem bylo vybrat buňku A3.

Doufám, že už máte základní znalosti o tom, jak zaznamenat makro v aplikaci Excel.

Mějte na paměti, že kód zapsaný makro rekordérem není v žádném případě efektivní kód.

Makro rekordér někdy přidává do kódu spoustu vláken, což není někdy nutné. To ale neznamená, že to není užitečné. Pro někoho, kdo se učí VBA, může být záznamník maker skvělým způsobem, jak analyzovat, jak věci ve VBA fungují.

Absolutní vs. relativní záznam makra

Už víte o absolutních a relativních referencích v Excelu … že?

Pokud ne - nejprve si přečtěte tento návod k referencím.

Přečtěte si to? Pokračujme.

Později v této části uvidíme, jak zaznamenávat makra v absolutních a relativních referencích. Ale ještě předtím mi dovolte rychle shrnout rozdíl mezi absolutní a relativní referencí ve VBA (v případě, že jste zlenivěli a nečetli jste odkaz, který jsem dal před několika řádky):

Pokud k záznamu makra použijete možnost absolutního odkazu, kód VBA by vždy odkazoval na stejné buňky, které jste použili. Pokud například vyberete buňku A2, zadejte text Excel a pokaždé stiskněte klávesu Enter - bez ohledu na to, kde se na listu nacházíte a bez ohledu na to, která buňka je vybrána, váš kód nejprve vybere buňku A2, zadá text Excel a poté se přesuňte do buňky A3.

Pokud k záznamu makra použijete možnost relativní reference, VBA by odkazy na buňky nezakódovala natvrdo. Ve srovnání s aktivní buňkou by se spíše soustředilo na pohyb. Předpokládejme například, že již máte vybranou buňku A1, a začnete nahrávat makro v relativním referenčním režimu.

Nyní vyberte buňku A2, zadejte text aplikace Excel a stiskněte klávesu Enter. Když nyní spustíte toto makro, nevrátí se do buňky A2, místo toho se bude pohybovat relativně k aktivní buňce. Pokud je například vybrána buňka K3, přesune se na K4, zadejte text Excel a nakonec vyberte buňku K5.

Nyní vám řeknu, jak zaznamenat makro v režimu relativních referencí:

1. Vyberte buňku A1.
2. Klikněte na kartu Vývojář.
3. Ve skupině Kód klikněte na tlačítko „Použít relativní reference“. Zezelená, což znamená, že je zapnutý.
4. Klikněte na tlačítko Nahrát makro.
5. V dialogovém okně Záznam makra zadejte název makra. Používám jméno EnterTextRelRef.
6. V možnosti Uložit makro do zaškrtněte políčko „Tento sešit“.
7. Klikněte na OK.
8. Vyberte buňku A2.
9. Zadejte text Vynikat (nebo můžete zadat své jméno).
10. Stiskněte klávesu Enter. Tím se kurzor přesune do buňky A3.
11. Klikněte na tlačítko Zastavit nahrávání na kartě Vývojář.

Tím by bylo makro zaznamenáno v relativním referenčním režimu.

Nyní to udělejte.

1. Vyberte libovolnou buňku (jinou než A1).
2. Klikněte na kartu Vývojář.
3. Ve skupině Kód klikněte na tlačítko Makra.
4. V dialogovém okně Makro klikněte na Název makra - EnterTextRelRef.
5. Klikněte na tlačítko Spustit.

Co se stalo? Vrátil se kurzor zpět do buňky A3.

To by ne - protože jste zaznamenali makro v relativním referenčním režimu. Kurzor by se tedy pohyboval relativně k aktivní buňce. Pokud to například provedete, když je vybrána buňka K3, zadá text Excel je buňka K4 a nakonec vybere buňku K5.

Zde je kód, který se zaznamená do backendu (okno kódu modulu VB Editor):

Sub EnterTextRelRef () '' EnterTextRelRef Macro '' ActiveCell.Offset (1, 0) .Range ("A1"). Vyberte ActiveCell.FormulaR1C1 = "Excel" ActiveCell.Offset (1, 0) .Range ("A1"). Vyberte End Sub

Celý tento kód se nikde nevztahuje na buňky K3 nebo K4. Místo toho používá Activecell k odkazování na vybranou buňku a Offset k pohybu vzhledem k aktivní buňce.

Nedělejte si starosti s částí Range („A1“), kterou kód obsahuje. Je to jeden z těch nepotřebných kódů, které makro rekordér přidává a které neslouží žádnému účelu a lze je odstranit. Kód by bez něj fungoval dobře.

Tlačítko „Použít relativní odkaz“ na kartě Vývojář je přepínací tlačítko. Můžete jej vypnout (a přepnout zpět na absolutní referenci) kliknutím na něj.

Co záznamník makra nedokáže

Makro rekordér je skvělý při sledování v Excelu a zaznamenávání vašich přesných kroků, ale může selhat, když potřebujete více.

• Bez výběru objektu nemůžete spustit kód. Pokud chcete, aby záznam makra přešel na další list a zvýraznil všechny vyplněné buňky ve sloupci A, aniž by opustil aktuální list, pak to nebude možné. Je to proto, že když vás o to požádám, ani vy to nebudete schopni udělat (bez opuštění aktuálního listu). A pokud to nedokážete sami, jak makro rekordér zachytí vaše akce. V takových případech musíte ručně přejít a vytvořit/upravit kód.
• Vlastní funkci nelze vytvořit pomocí záznamníku makra. S VBA můžete vytvářet vlastní funkce, které můžete použít v listu jako běžné funkce. Můžete to vytvořit tak, že kód napíšete ručně.
• Kódy nelze spouštět na základě událostí: Ve VBA můžete použít mnoho událostí - například otevření sešitu, přidání listu, dvojité kliknutí na buňku atd., Ke spuštění kódu spojeného s touto událostí. K tomu můžete použít záznamník maker.
• S makrami nelze vytvářet smyčky. Když kód zadáte ručně, můžete využít sílu smyček ve VBA (například For Next, For Every Next, Do While, Do until). To však nemůžete udělat, když zaznamenáváte makro.
• Nemůžete analyzovat podmínky: Podmínky v kódu můžete zkontrolovat pomocí záznamníku maker. Pokud píšete kód VBA ručně, můžete použít příkazy IF Then Else k analýze podmínky a spuštění kódu, pokud je pravdivý (nebo jiný kód, pokud je nepravdivý).
• Argumenty nelze předat v postupu makra: Když zaznamenáte makro, nikdy nebude mít žádné argumenty. Podprogram může převzít vstupní argumenty, které lze použít v makru k provedení úkolu. Při záznamu makra to nelze provést, protože zaznamenaná makra jsou nezávislá a nejsou připojena k žádným jiným existujícím makrům.

Rozšíření souborů s povoleným makrem

Když zaznamenáváte makro nebo ručně píšete kód VBA v aplikaci Excel, musíte soubor uložit s příponou souboru s povoleným makro (.xlsm).

Před aplikací Excel 2007 stačil jeden jediný formát souboru - .xls.

Ale od roku 2007 byl .xlsx zaveden jako standardní přípona souboru. Soubory uložené jako .xlsx nemohou obsahovat makro. Pokud tedy máte soubor s příponou .xlsx a zaznamenáte/zapíšete makro a uložíte jej, upozorní vás, abyste jej uložili ve formátu povoleném pro makra, a zobrazí vám dialog (jak je uvedeno níže):

Pokud vyberete Ne, Excel vám umožní uložit jej ve formátu s povolenými makry. Pokud ale kliknete na Ano, Excel automaticky odebere veškerý kód ze sešitu a uloží jej jako sešit .xlsx.

Pokud tedy máte v sešitu makro, musíte ho uložit ve formátu .xlsm, aby se toto makro zachovalo.

Různé způsoby spuštění makra v aplikaci Excel

Doposud jsme viděli pouze jeden způsob, jak spustit makro v aplikaci Excel - což je pomocí dialogového okna Makro.

Makra však můžete spouštět několika způsoby.

1. Spuštění makra na pásu karet (karta Vývojář)
2. Pomocí klávesové zkratky (kterou musíte přiřadit)
3. Přiřaďte makro tvaru
4. Přiřaďte makro tlačítku
5. Spusťte makro z editoru VB

Závěr - po zaseknutí zaznamenejte makro

Již jsem zmínil, že záznam makra je užitečný nástroj pro každého, kdo pracuje s VBA v aplikaci Excel.

Když několikrát použijete záznamník makra, všimnete si, že vyplivne spoustu zbytečného kódu. Přesto je to užitečné a dává vám pár nápadů, kde začít. Pokud vás například požádám o filtrování sloupce buněk pomocí VBA a vy nevíte, co je to syntaxe, můžete rychle zaznamenat makro a zkontrolovat kód.

Makro rekordér je nepostradatelným nástrojem a i po letech zkušeností s kódováním VBA pod mým opaskem se často obracím o pomoc na makro rekordér.