Toto jsou moje show show z mého vystoupení v epizodě 33 Call for Help na TechTV Canada.
Záznam makra VBA
Každá kopie aplikace Excel prodaná od roku 1995 obsahuje neuvěřitelně výkonný Visual Basic for Applications (VBA), který se skrývá za buňkami. Pokud máte Excel, máte VBA.
VBA vám umožňuje automatizovat vše, co můžete dělat v aplikaci Excel, a dělat další věci, které v aplikaci Excel obecně nejsou možné.
Naštěstí pro nás společnost Microsoft zahrnula záznamník maker do aplikace Excel. To vám umožní psát kód VBA, aniž byste byli programátorem. Makro rekordér je bohužel vadný. Nebude produkovat kód, který bude fungovat pokaždé. Makro rekordér vás přiblíží, ale v mnoha případech musíte kód trochu opravit, aby spolehlivě fungoval. V ostatních případech potřebujete vědět, kdy použít relativní záznam, aby makro fungovalo.
Tracy Syrstad a já jsme napsali VBA a Makra Microsoft Excel 2016, abychom vám pomohli pochopit omezení zaznamenaného kódu a naučili vás, jak opravit zaznamenaný kód do něčeho, co bude fungovat pokaždé.
Na výstavě jsem předvedl proces nahrávání makra, které bude fungovat perfektně, pokud víte, jak použít tlačítko Relativní reference. Někdo mi dal list aplikace Excel se stovkami jmen a adres. Chtěli poštovní štítky. Pokud jste někdy používali funkci hromadné korespondence Microsoft Word, víte, že potřebujete každé pole v novém sloupci v listu. Místo toho měl tento konkrétní list data, která šla dolů do sloupce A.
Proces opravy jednoho štítku je docela zdlouhavý.
- Začněte ukazatelem buňky na název ve sloupci A.
- Stisknutím šipky dolů přejděte na adresu
- Ctrl + x pro oříznutí
- Šipka nahoru, šipka doprava pro přesun do sloupce B vedle jména
- Ctrl + v pro vložení
- Šipka dolů dvakrát, šipka doleva jednou pro přesun do města
- Ctrl + x pro oříznutí
- Šipka nahoru dvakrát, šipka vpravo trice
- Ctrl + v pro vložení
- Šipka vlevo dvakrát, šipka dolů jednou pro přesun na nyní prázdný řádek.
- Podržte klávesu Shift a dvakrát stiskněte šipku dolů
- Alt + edr smaže prázdné řádky
- Šipka nahoru a šipka dolů znovu vyberte pouze jméno.
Zde je animace zobrazující tyto kroky:
Nastavení aplikace Excel tak, aby umožňovala makra
Ačkoli každá kopie aplikace Excel obsahuje VBA, Microsoft ve výchozím nastavení vypíná možnost spouštění maker. Chcete-li povolit spuštění maker, musíte provést jednorázovou úpravu. Otevřete Excel. V nabídce vyberte Nástroje> Makro> Zabezpečení. Pokud je úroveň zabezpečení maker aktuálně nastavena na vysokou, změňte ji na střední.
Příprava záznamu makra
Přemýšlejte o krocích nezbytných k dosažení úkolu. Chcete se ujistit, že začínáte ukazatelem buňky na jméno a končíte ním na další jméno. To vám umožní opakovaně spustit makro mnohokrát. Až budete mít kroky připravené, zapněte záznamník maker. V nabídce vyberte Nástroje> Makro> Zaznamenat nové makro.
Pokud toto makro budete používat po mnoho dní, měli byste tomuto makru dát užitečný název. Pokud se jedná o jednorázové makro k automatizaci jednorázového úkolu, pak ponechání názvu makra jako Macro1 je pravděpodobně v pořádku. Důležitým polem je pole klávesové zkratky. Pokud vytváříte makro pro jednorázové použití, přiřaďte jej klávesové zkratce, jako je Ctrl + a - je docela snadné opakovaně stisknout Ctrl + a, protože klávesy jsou blízko u sebe. Pokud se chystáte vytvořit makro, které budete používat každý den, pak jej chcete přiřadit klíči, který ještě není důležitou kombinací klávesových zkratek. Ctrl + j nebo Ctrl + k jsou dobrá volba.
Makra pro jednorázové použití by měla být uložena v ThisWorkbook. Pokud potřebujete makro použít opakovaně v mnoha různých sešitech, můžete makro uložit do osobního sešitu maker.
Nyní se vám zobrazí nejmenší panel nástrojů v celém Excelu; panel nástrojů Zastavit nahrávání. Má jen dvě ikony a vypadá takto:
Pokud se vám tento panel nástrojů dostane do cesty, nezavřete jej stisknutím klávesy X. Místo toho uchopte modrý pruh a přetáhněte panel nástrojů na nové místo. Akt přesunutí panelu nástrojů není v makru zaznamenán. Pokud omylem zavřete panel nástrojů, získejte jej zpět pomocí Zobrazení> Panely nástrojů> Zastavit nahrávání. Tato akce však bude zaznamenána.
První tlačítko na panelu nástrojů je tlačítko „Zastavit nahrávání“. To je samozřejmé. Po dokončení záznamu makra stiskněte panel nástrojů pro zastavení záznamu.
Důležitějším (a málokdy srozumitelným) tlačítkem je tlačítko „Relativní reference“.
V našem aktuálním příkladu musíte předtím, než začnete, stisknout tlačítko Relativní reference. Pokud zaznamenáte makro se zapnutými relativními odkazy, Excel zaznamená takové kroky:
- Přesunout o jednu buňku dolů
- Vyřízněte aktuální buňku
- Přesunout o jednu buňku nahoru
- Přesuňte jednu buňku doprava
- Vložit
- atd.
Pokud byste místo toho zaznamenali makro bez relativních odkazů, makro by zaznamenalo tyto kroky:
- Přesunout do buňky A4
- Vyřízněte buňku A4
- Přesunout do buňky A3
- Přesuňte se do buňky B3
- Vložte do buňky B3
- atd.
To by bylo strašně špatné - potřebujeme, aby makro fungovalo na buňkách, které jsou 1 a 2 buňkami od počátečního bodu makra. Při opakovaném spuštění makra bude výchozím bodem řádek 4, řádek 5, řádek 6 atd. Záznam makra bez zapnutých relativních odkazů by makro vždy spustil na řádku 3 jako výchozí bod.
Následuj tyto kroky:
- Zvolte tlačítko Relativní reference na panelu nástrojů Zastavit nahrávání
- Ukazatel buňky by již měl být na jménu ve sloupci A.
- Stisknutím šipky dolů přejděte na adresu
- Ctrl + x pro oříznutí
- Šipka nahoru, šipka doprava pro přesun do sloupce B vedle jména
- Ctrl + v pro vložení
- Šipka dolů dvakrát, šipka doleva jednou pro přesun do města
- Ctrl + x pro oříznutí
- Šipka nahoru dvakrát, šipka vpravo trice
- Ctrl + v pro vložení
- Šipka vlevo dvakrát, šipka dolů jednou pro přesun na nyní prázdný řádek.
- Podržte klávesu Shift a dvakrát stiskněte šipku dolů
- Alt + edr smaže prázdné řádky
- Šipka nahoru a šipka dolů vyberte další jméno v seznamu.
- Stiskněte panel nástrojů Zastavit záznam
- Uložte sešit
- Otestujte makro stisknutím klávesové zkratky přiřazené výše. Mělo by to dokonale opravit další jméno v seznamu
Jakmile uvidíte, že makro funguje podle potřeby, můžete podržením kláves Ctrl + a rychle opravit několik jmen za sekundu. Celý seznam 500 jmen lze opravit za minutu nebo dvě.
Bonusový tip - není na výstavě
Makro můžete snadno zabalit do jednoduché smyčky, aby bylo možné opravit všech 500 jmen, aniž byste museli několikrát stisknout Ctrl +.
Stisknutím kláves Alt + F11 otevřete editor maker.
Pokud nevidíte podokno Projekt - VBAProject, stiskněte Ctrl + r.
Klikněte pravým tlačítkem na Module1 a vyberte Zobrazit kód. Uvidíte kód, který vypadá takto:
Nyní nemusíte rozumět tomu, co tento kód dělá, ale víte, že chceme spustit vše v tomto makru jednou pro každé jméno, které máme. Pokud víte, že existuje 462 jmen, můžete přidat 2 řádky, abyste kód spustili 462krát. V horní části makra vložte nový řádek se slovy „ For i = 1 to 462“. V dolní části makra vložte řádek s textem „ Next i“. To říká VBA, aby spustil kód uvnitř 462krát.
Závěr
Po tomto jednoduchém makru jsem ukázal příklad na ukázce velmi složitého makra. Toto makro vytvořilo kontingenční tabulky a grafy pro 46 oddělení společnosti. Tato sestava trvala každý měsíc 40 hodin. Makro VBA se nyní spustí a vytvoří všech 46 sestav za méně než 2 minuty.
Kniha VBA a makra Microsoft Excel 2016 vás provede křivkou učení, takže se můžete dostat od záznamu jednoduchých maker, jako je toto, k spouštění velmi složitých sestav, které vám mohou ušetřit stovky hodin ročně. Samozřejmě, pokud se nechcete učit VBA, najměte si Excel konzultanta, který vám navrhne zprávu.
