Ako zaznamenať makro v programe Excel - Podrobný sprievodca

Aj keď ste úplným nováčikom vo svete Excelu VBA, môžete ľahko nahrať makro a zautomatizovať časť svojej práce.

V tomto podrobnom sprievodcovi sa budem zaoberať všetkým, čo potrebujete vedieť, aby ste mohli začať so záznamom a používaním makier v programe Excel.

Ak máte záujem naučiť sa VBA jednoduchým spôsobom, pozrite sa na moje Online školenie Excel VBA.

Čo je to makro?

Ak ste nováčikom vo VBA, najskôr vám poviem, čo je to makro - koniec koncov, tento výraz budem naďalej používať v celom návode.

Makro je kód napísaný vo VBA (Visual Basic for Applications), ktorý vám umožňuje spustiť kus kódu vždy, keď je spustený.

Často nájdete ľudí (vrátane mňa), ktorí označujú kód VBA ako makro - či už je generovaný pomocou záznamníka makier alebo bol napísaný ručne.

Keď zaznamenávate makro, Excel pozorne sleduje kroky, ktoré robíte, a zaznamenáva si ho v jazyku, ktorému rozumie - čo je VBA.

A pretože Excel je skutočne dobrý príjemca poznámok, vytvára veľmi podrobný kód (ako uvidíme neskôr v tomto návode).

Keď teraz zastavíte nahrávanie, uložíte makro a spustíte ho, Excel sa jednoducho vráti k vygenerovanému kódu VBA a postupuje presne podľa rovnakých krokov.

To znamená, že aj keď o VBA nič neviete, niektoré úlohy môžete zautomatizovať tak, že Excel raz zaznamená vaše kroky a potom ich použije znova.

Teraz sa ponoríme a uvidíme, ako zaznamenať makro v programe Excel.

Získanie karty Vývojár na páse s nástrojmi

Prvým krokom k zaznamenaniu makra je získanie karty Vývojár na páse s nástrojmi.

Ak už na páse s nástrojmi vidíte kartu vývojára, prejdite na ďalšiu sekciu, v opačnom prípade postupujte podľa nasledujúcich krokov:

• Kliknite pravým tlačidlom myši na ktorúkoľvek z existujúcich kariet na páse s nástrojmi a kliknite na možnosť „Prispôsobiť pás s nástrojmi“. Otvorí sa dialógové okno Možnosti programu Excel.
• V dialógovom okne Možnosti programu Excel budete mať možnosti Prispôsobiť pás s nástrojmi. Vpravo na table Hlavné karty začiarknite možnosť Vývojár.
• Kliknite na tlačidlo OK.

Vyššie uvedené kroky by sprístupnili kartu Vývojár v oblasti pása s nástrojmi.

Záznam makra v programe Excel

Teraz, keď máme všetko na svojom mieste, naučme sa zaznamenávať makro v programe Excel.

Zaznamenajme veľmi jednoduché makro - makro, ktoré vyberie bunku a zadá do nej text „Excel“. Pri zaznamenávaní tohto makra používam text „Excel“, ale pokojne zadajte svoje meno alebo iný text, ktorý sa vám páči.

Nasledujú kroky na zaznamenanie tohto makra:

1. Kliknite na kartu Vývojár.
2. V skupine Kód kliknite na tlačidlo Makro. Otvorí sa dialógové okno „Nahrať makro“.
3. V dialógovom okne Zaznamenať makro zadajte názov makra. Používam meno EnterText. Pri pomenovaní makra musíte dodržať niektoré podmienky pomenovania. Napríklad nemôžete používať medzery. Väčšinou dávam prednosť tomu, aby boli moje názvy makier ako jedno slovo, s rôznymi časťami a prvou abecedou s veľkými písmenami. Podčiarkovník môžete použiť aj na oddelenie dvoch slov - napríklad Enter_Text.
4. (Voliteľný krok) Ak chcete, môžete priradiť klávesovú skratku. V takom prípade použijeme skratku Ctrl + Shift + N. Nezabudnite, že skratka, ktorú tu priradíte, prepíše všetky existujúce skratky vo vašom zošite. Ak napríklad priradíte skratku Control + S, nebudete ju môcť použiť na ukladanie zošita (namiesto toho sa pri každom použití spustí makro).
5. Vo voľbe „Uložiť makro do“ skontrolujte, či je vybratá možnosť „Tento zošit“. Tento krok zaisťuje, aby bolo makro súčasťou zošita. Bude tam, keď ho uložíte a znova otvoríte, alebo ho dokonca budete s niekým zdieľať.
6. (Voliteľný krok) Zadajte popis. Obvykle to nerobím, ale ak ste veľmi organizovaní, môžete pridať, o čom je makro.
7. Kliknite na tlačidlo OK. Hneď ako kliknete na tlačidlo OK, začne zaznamenávať vaše akcie v programe Excel. Na karte Vývojár môžete vidieť tlačidlo „Zastaviť nahrávanie“, ktoré naznačuje, že prebieha nahrávanie makra.
8. Vyberte bunku A2.
9. Zadajte text Excel (alebo môžete použiť svoje meno).
10. Stlačte kláves Enter. Vyberie sa bunka A3.
11. Kliknite na tlačidlo Zastaviť nahrávanie na karte Vývojár.

Gratulujem

Práve ste zaznamenali svoje prvé makro v programe Excel. Už nie ste makro pannou.

Aj keď makro nerobí nič užitočné, bude slúžiť svojmu účelu pri vysvetľovaní fungovania záznamníka makier v programe Excel.

Teraz poďme na to a otestujeme toto makro.

Pri testovaní makra postupujte nasledovne:

1. Odstráňte text v bunke A2. Toto má otestovať, či makro vloží text do bunky A2 alebo nie.
2. Vyberte ľubovoľnú bunku - inú ako A2. Toto má otestovať, či makro vyberie bunku A2 alebo nie.
3. Kliknite na kartu Vývojár.
4. V skupine Kód kliknite na tlačidlo Makrá.
5. V dialógovom okne Makro kliknite na názov makra - EnterText.
6. Kliknite na tlačidlo Spustiť.

Všimnite si, že hneď ako kliknete na tlačidlo Spustiť, text „Excel“ sa vloží do bunky A2 a vyberie sa bunka A3.

Teraz sa to všetko môže stať v zlomku sekundy, ale v skutočnosti makro - rovnako ako poslušný škriatok - dodržalo presné kroky, ktoré ste mu ukázali pri zázname makra.

Makro teda najskôr vyberie bunku A2 a potom zadá text Excel v ňom a potom vyberie bunku A3.

Poznámka: Makro môžete spustiť aj pomocou klávesovej skratky Ctrl + Shift + N. (podržte klávesy Control a Shift a potom stlačte kláves N.) Je to rovnaká klávesová skratka, akú sme priradili makru pri jeho zaznamenávaní.

Čo robí nahrávanie makra v serveri

Teraz prejdeme k backendu programu Excel - editoru VB - a uvidíme, čo nahrávanie makra skutočne robí.

Tu sú kroky na otvorenie editora VB v programe Excel:

1. Kliknite na kartu Vývojár.
2. V skupine Kód kliknite na tlačidlo Visual Basic.

Alebo môžete použiť klávesovú skratku - ALT + F11 (namiesto dvoch vyššie uvedených krokov podržte kláves ALT a stlačte kláves F11). Táto skratka tiež otvára rovnaký editor VB.

Ak teraz vidíte editor VB prvýkrát, nenechajte sa ohromiť.

Dovoľte mi, aby som vás rýchlo zoznámil s anatómiou editora VB.

• Panel s ponukami: Tu máte všetky možnosti editora VB. Považujte to za stuhu VBA. Obsahuje príkazy, ktoré môžete použiť pri práci s editorom VB.
• Panel s nástrojmi - Je to ako panel s nástrojmi Rýchly prístup editora VB. Dodáva sa s niekoľkými užitočnými možnosťami a môžete k nim pridať ďalšie možnosti. Jeho výhodou je, že možnosť na paneli s nástrojmi je vzdialená len jedno kliknutie.
• Okno Project Explorer - Tu Excel uvádza všetky zošity a všetky objekty v každom zošite. Ak napríklad máme zošit s 3 pracovnými listami, zobrazí sa v programe Project Explorer. Tu sú niektoré ďalšie objekty, ako sú moduly, užívateľské formuláre a moduly tried.
• Okno s kódom - Tu sa zaznamenáva alebo zapisuje kód VBA. Pre každý objekt uvedený v Prieskumníkovi projektu je okno s kódom - napríklad pracovné hárky, zošity, moduly atď. Neskôr v tomto návode uvidíme, že zaznamenané makro prejde do okna kódu modulu.
• Okno vlastností - V tomto okne môžete vidieť vlastnosti každého objektu. Toto okno často používam na pomenovanie objektov alebo zmenu skrytých vlastností. Toto okno sa vám nemusí zobraziť, keď otvoríte editor VB. Ak to chcete zobraziť, kliknite na kartu zobrazenia a vyberte položku Okno vlastností.
• Okamžité okno - Pri písaní kódu často používam bezprostredné okno. Je to užitočné, keď chcete testovať niektoré vyhlásenia alebo ladiť. Štandardne nemusí byť viditeľný a môžete ho zobraziť tak, že kliknete na kartu Zobraziť a vyberiete možnosť Okamžité okno.

Keď sme zaznamenali makro - EnterTextV editore VB sa stali nasledujúce veci:

• Bol vložený nový modul.
• Bolo zaznamenané makro s názvom, ktorý sme zadali - EnterText
• Kód bol zapísaný do okna kódu modulu.

Ak teda dvakrát kliknete na modul (v tomto prípade na Modul 1), zobrazí sa okno s kódom, ako je uvedené nižšie.

Tu je kód, ktorý nám poskytol záznamník makier:

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

Vo VBA každý riadok, ktorý nasleduje po ‘ (znak apostrofu) sa nevykoná. Je to komentár, ktorý slúži len na informačné účely. Ak odstránite prvých päť riadkov tohto kódu, makro bude fungovať podľa očakávania.

Teraz mi rýchlo priblížte, čo jednotlivé riadky kódu robia:

Kód začína písmenom Sub, za ktorým nasleduje názov makra a prázdna zátvorka. Sub je skratka pre podprogram. Každý podprogram (nazývaný aj postup) vo VBA začína na Sub a končí na End Sub.

• Rozsah („A2“). Vyberte - Tento riadok vyberie bunku A2.
• ActiveCell.FormulaR1C1 = „Excel“ - tento riadok zadá text Excel do aktívnej bunky. Pretože sme ako prvý krok vybrali A2, stane sa našou aktívnou bunkou.
• Rozsah („A3“). Vyberte - Týmto sa vyberie bunka A3. Stáva sa to tak, že po zadaní textu stlačíme kláves Enter, výsledkom ktorého je výber bunky A3.

Dúfam, že už máte základné znalosti o tom, ako zaznamenať makro v programe Excel.

Majte na pamäti, že kód napísaný makro rekordérom nie je v žiadnom prípade efektívny kód.

Makro rekordér niekedy pridáva do kódu veľa vata, čo nie je niekedy potrebné. To však neznamená, že to nie je užitočné. Pre niekoho, kto sa učí VBA, môže byť záznamník makier skvelým spôsobom, ako analyzovať, ako veci vo VBA fungujú.

Absolútne vs. relatívne makro nahrávanie

Už viete o absolútnych a relatívnych referenciách v Exceli … nie?

Ak nie - prečítajte si najskôr tento návod o referenciách.

Prečítajte si to? Poďme ďalej.

Ďalej v tejto časti uvidíme, ako zaznamenávať makrá v absolútnych a relatívnych odkazoch. Ale predtým, dovoľte mi rýchlo zhrnúť rozdiel medzi absolútnym a relatívnym odkazom vo VBA (v prípade, že ste sa lenivili a neprečítali ste si odkaz, ktorý som uviedol pred niekoľkými riadkami):

Ak použijete na zaznamenanie makra možnosť absolútnej referencie, kód VBA by vždy odkazoval na rovnaké bunky, ktoré ste použili. Ak napríklad vyberiete bunku A2, zadajte text programu Excel a zakaždým stlačte kláves Enter - bez ohľadu na to, kde sa v hárku nachádzate a bez ohľadu na to, ktorá bunka je vybratá, váš kód najskôr vyberie bunku A2, zadá text programu Excel a potom sa presuňte do bunky A3.

Ak použijete na zaznamenanie makra možnosť relatívnej referencie, VBA by odkazy na bunky nekódovala natvrdo. V porovnaní s aktívnou bunkou by sa zameriavalo skôr na pohyb. Predpokladajme napríklad, že už máte vybratú bunku A1 a začnete nahrávať makro v relatívnom referenčnom režime.

Teraz vyberte bunku A2, zadajte text programu Excel a stlačte kláves Enter. Keď teraz spustíte toto makro, nevráti sa do bunky A2, namiesto toho sa presunie relatívne k aktívnej bunke. Ak je napríklad vybraná bunka K3, presunie sa do K4, zadajte text programu Excel a nakoniec vyberte bunku K5.

Teraz vám poviem, ako zaznamenať makro v režime relatívnych referencií:

1. Vyberte bunku A1.
2. Kliknite na kartu Vývojár.
3. V skupine Kód kliknite na tlačidlo „Použiť relatívne referencie“. Svieti na zeleno, čo znamená, že je zapnutá.
4. Kliknite na tlačidlo Zaznamenať makro.
5. V dialógovom okne Zaznamenať makro zadajte názov makra. Používam meno EnterTextRelRef.
6. V možnosti Uložiť makro v uistite sa, že je vybratá možnosť „Tento zošit“.
7. Kliknite na tlačidlo OK.
8. Vyberte bunku A2.
9. Zadajte text Excel (alebo môžete zadať svoje meno).
10. Stlačte kláves Enter. Tým sa kurzor presunie do bunky A3.
11. Kliknite na tlačidlo Zastaviť nahrávanie na karte Vývojár.

Toto by zaznamenalo makro v relatívnom referenčnom režime.

Teraz to urob.

1. Vyberte ľubovoľnú bunku (inú ako A1).
2. Kliknite na kartu Vývojár.
3. V skupine Kód kliknite na tlačidlo Makrá.
4. V dialógovom okne Makro kliknite na názov makra - EnterTextRelRef.
5. Kliknite na tlačidlo Spustiť.

Čo sa stane? Vrátil sa kurzor späť do bunky A3.

Nebolo by - pretože ste zaznamenali makro v relatívnom referenčnom režime. Kurzor by sa teda pohyboval relatívne k aktívnej bunke. Ak to napríklad urobíte, keď je vybratá bunka K3, zadá text Excel je bunka K4 a nakoniec vyberie bunku K5.

Tu je kód, ktorý sa zaznamená v backende (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 položku End Sub

Všimnite si toho, že celý tento kód sa nikde nevzťahuje na bunky K3 alebo K4. Namiesto toho používa Activecell na odkaz na vybratú bunku a Offset sa pohybuje relatívne k aktívnej bunke.

Nerobte si starosti s časťou rozsahu („A1“), ktorú kód obsahuje. Je to jeden z nepotrebných kódov, ktoré makro rekordér dodáva, ktoré slúžia na žiadny účel a je možné ich odstrániť. Kód by bez toho fungoval dobre.

Tlačidlo „Použiť relatívnu referenciu“ na karte Vývojár je prepínacie tlačidlo. Môžete ho vypnúť (a prepnúť späť na absolútnu referenciu) kliknutím naň.

Čo nedokáže záznamník makra

Makro rekordér je skvelý pri sledovaní vás v programe Excel a zaznamenávaní vašich presných krokov, ale môže zlyhať, keď budete potrebovať viac.

• Bez výberu objektu nemôžete spustiť kód. Ak chcete, aby záznamník makier prešiel na nasledujúci pracovný hárok a zvýraznil všetky vyplnené bunky v stĺpci A bez opustenia aktuálneho pracovného hárka, nebude to môcť urobiť. Je to preto, že ak vás o to požiadam, ani vy to nedokážete (bez opustenia aktuálneho hárka). A ak to nemôžete urobiť sami, ako makro záznamník zachytí vaše akcie. V takýchto prípadoch musíte manuálne ísť a vytvoriť/upraviť kód.
• Vlastnú funkciu nemôžete vytvoriť pomocou záznamníka makier. S VBA môžete vytvárať vlastné funkcie, ktoré môžete použiť v pracovnom hárku ako bežné funkcie. Môžete to vytvoriť ručným napísaním kódu.
• Kódy nemôžete spúšťať na základe udalostí: Vo VBA môžete použiť mnoho udalostí - napríklad otvorenie zošita, pridanie pracovného hárka, dvojité kliknutie na bunku atď., Na spustenie kódu spojeného s touto udalosťou. Na to môžete použiť záznamník makier.
• Pomocou záznamníka makier nemôžete vytvárať slučky. Keď kód zadáte manuálne, môžete využiť silu slučiek vo VBA (napríklad For Next, For Every Next, Do While, Do until). To však nemôžete urobiť, keď zaznamenávate makro.
• Nemôžete analyzovať podmienky: Podmienky v kóde môžete skontrolovať pomocou záznamníka makier. Ak píšete kód VBA ručne, môžete použiť príkazy IF Then Else na analýzu podmienky a spustenie kódu, ak je pravdivý (alebo iný kód, ak je nepravdivý).
• Argumenty nemôžete odovzdať v procedúre makra: Keď zaznamenáte makro, nikdy nebude mať žiadne argumenty. Podprogram môže obsahovať vstupné argumenty, ktoré je možné použiť v makre na vykonanie úlohy. Pri zázname makra to nie je možné vykonať, pretože zaznamenané makrá sú nezávislé a nie sú prepojené s inými existujúcimi makrami.

Prípony súborov s povoleným makrom

Keď zaznamenávate makro alebo ručne píšete kód VBA v programe Excel, musíte súbor uložiť s príponou súboru s povoleným makrom (.xlsm).

Pred Excelom 2007 stačil jeden jediný formát súboru - .xls.

Ale od roku 2007 bol .xlsx predstavený ako štandardná prípona súboru. Súbory, ktoré sú uložené ako .xlsx, nemôžu obsahovať makro. Ak teda máte súbor s príponou .xlsx a zaznamenáte/napíšete makro a uložíte ho, upozorní vás, aby ste ho uložili vo formáte s povolenými makrami, a zobrazí vám dialógové okno (ako je uvedené nižšie):

Ak vyberiete Nie, Excel vám umožní uložiť ho vo formáte s povolenými makrami. Ak však kliknete na tlačidlo Áno, Excel automaticky odstráni všetok kód zo zošita a uloží ho ako zošit .xlsx.

Ak teda máte v zošite makro, musíte si ho uložiť vo formáte .xlsm, aby ste si toto makro zachovali.

Rôzne spôsoby spustenia makra v programe Excel

Doposiaľ sme videli iba jeden spôsob spustenia makra v programe Excel - ktorý používa dialógové okno Makro.

Existuje však niekoľko spôsobov, ako môžete spúšťať makrá.

1. Spustite makro na páse s nástrojmi (karta Vývojár)
2. Použitie klávesovej skratky (ktorú musíte priradiť)
3. Priraďte makro k tvaru
4. Priraďte makro tlačidlu
5. Spustite makro z editora VB

Záver - Keď uviaznete, zaznamenajte makro

Už som spomenul, že záznamník makier je užitočný nástroj pre kohokoľvek, kto pracuje s VBA v programe Excel.

Keď niekoľkokrát použijete záznamník makier, všimnete si, že chrlí veľa nepotrebného kódu. Je to však stále užitočné a poskytne vám niekoľko nápadov, kde začať. Ak vás napríklad požiadam o filtrovanie stĺpca buniek pomocou jazyka VBA a vy neviete, čo je to syntax, môžete rýchlo nahrať makro a skontrolovať kód.

Makro rekordér je nepostrádateľný nástroj a aj po rokoch skúseností s kódovaním VBA pod mojím opaskom sa často obraciam o pomoc na makro rekordér.