Toto sú moje show show z môjho vystúpenia v epizóde 33 Call for Help na TechTV Canada.
Záznam makra VBA
Každá kópia programu Excel predaná od roku 1995 obsahuje neuveriteľne výkonný jazyk Visual Basic for Applications (VBA), ktorý sa skrýva za bunkami. Ak máte Excel, máte VBA.
VBA vám umožňuje automatizovať všetko, čo môžete robiť v Exceli, a robiť viac vecí, ktoré v Exceli nie sú všeobecne možné.
Našťastie pre nás Microsoft zahrnul záznamník makier do Excelu. Toto vám umožní písať kód VBA bez toho, aby ste boli programátorom. Makro rekordér je bohužiaľ chybný. Nebude produkovať kód, ktorý bude fungovať zakaždým. Makro záznam vás zblíži, ale v mnohých prípadoch musíte kód trochu opraviť, aby spoľahlivo fungoval. V ostatných prípadoch musíte vedieť, kedy použiť relatívne nahrávanie, aby makro fungovalo.
Tracy Syrstad a ja sme napísali VBA a Makrá Microsoft Excel 2016, aby sme vám pomohli pochopiť obmedzenia zaznamenaného kódu a naučili vás, ako opraviť zaznamenaný kód do niečoho, čo bude fungovať kedykoľvek.
Na výstave som predviedol proces nahrávania makra, ktoré bude fungovať perfektne, ak viete, ako používať tlačidlo Relatívna referencia. Niekto mi dal pracovný hárok programu Excel so stovkami mien a adries. Chceli poštové štítky. Ak ste niekedy používali funkciu hromadnej korešpondencie programu Microsoft Word, viete, že každé pole potrebujete v novom stĺpci tabuľky. Namiesto toho tento konkrétny pracovný hárok obsahoval údaje nadol v stĺpci A.
Proces opravy jedného štítku je dosť zdĺhavý.
- Začnite ukazovateľom bunky na mene v stĺpci A.
- Stlačením šípky nadol sa presuňte na adresu
- Ctrl + x rezať
- Šípka hore, šípka doprava pre presun do stĺpca B vedľa mena
- Ctrl + v prilepiť
- Šípka dole dvakrát, šípka doľava raz, aby ste sa presunuli do mesta
- Ctrl + x rezať
- Šípka hore dvakrát, šípka doprava trice
- Ctrl + v prilepiť
- Šípka doľava dvakrát, šípka nadol raz, aby ste sa presunuli do teraz prázdneho riadku.
- Podržte stlačenú klávesu Shift a dvakrát stlačte šípku nadol
- Alt + edr odstránite prázdne riadky
- Šípka hore a šípka dole vyberte opäť iba meno.
Tu je animácia zobrazujúca tieto kroky:
Nastavenie programu Excel na povolenie makier
Aj keď každá kópia programu Excel obsahuje VBA, Microsoft predvolene vypína možnosť spúšťania makier. Ak chcete povoliť spustenie makier, musíte vykonať jednorazovú úpravu. Otvorte program Excel. Z ponuky vyberte Nástroje> Makro> Zabezpečenie. Ak je úroveň zabezpečenia makra momentálne nastavená na vysokú, zmeňte ju na strednú.
Príprava na záznam makra
Popremýšľajte, aké kroky sú potrebné na splnenie úlohy. Chcete sa ubezpečiť, že začínate ukazovateľom bunky na meno a končíte ním na ďalšie meno. To vám umožní opakovane spustiť makro mnohokrát. Keď máte pripravené kroky, zapnite makro rekordér. V ponuke vyberte Nástroje> Makro> Zaznamenať nové makro.
Ak budete toto makro používať mnoho dní, mali by ste mu dať užitočné meno. Ak sa jedná o jednorazové makro na automatizáciu jednorazovej úlohy, potom je pravdepodobne dobré ponechať názov makra ako Macro1. Dôležitým poľom je tu klávesová skratka. Ak vytvárate makro na jednorazové použitie, priraďte ho klávesovej skratke ako Ctrl + a - je dosť ľahké opakovane stlačiť Ctrl + a, pretože klávesy sú blízko seba. Ak sa chystáte vytvoriť makro, ktoré budete používať každý deň, potom ho chcete priradiť klávesu, ktorá ešte nie je dôležitou kombináciou klávesových skratiek. Ctrl + j alebo Ctrl + k sú dobrou voľbou.
Makra na jednorazové použitie by mali byť uložené v ThisWorkbook. Ak potrebujete makro opakovane použiť v mnohých rôznych zošitoch, môžete ho uložiť do osobného zošita osobných makier.
Teraz sa vám zobrazí najmenší panel s nástrojmi v celom programe Excel; panel nástrojov Zastaviť nahrávanie. Má iba dve ikony a vyzerá takto:
Ak vám tento panel s nástrojmi prekáža, nezavrite ho stlačením klávesy X. Namiesto toho chyťte modrý pruh a presuňte panel s nástrojmi na nové miesto. Akt posunutia panela s nástrojmi sa v makre nezaznamenáva. Ak náhodou zatvoríte panel s nástrojmi, získate ich späť v ponuke Zobraziť> Panely s nástrojmi> Zastaviť nahrávanie. Táto akcia sa však zaznamená.
Prvým tlačidlom na paneli nástrojov je tlačidlo „Zastaviť nahrávanie“. Je to samozrejmé. Po dokončení nahrávania makra stlačte panel s nástrojmi na zastavenie záznamu.
Dôležitejšie (a málokedy pochopené) tlačidlo je tlačidlo „Relatívna referencia“.
V našom súčasnom príklade musíte predtým, ako začnete, stlačiť tlačidlo Relatívna referencia. Ak nahrávate makro so zapnutými relatívnymi odkazmi, Excel zaznamená také kroky:
- Posun o jednu bunku nadol
- Vyrežte aktuálnu bunku
- Posunúť o jednu bunku nahor
- Posunúť o jednu bunku doprava
- Vložiť
- atď.
Ak by ste namiesto toho zaznamenali makro bez relatívnych odkazov, makro by zaznamenalo tieto kroky:
- Presunúť do bunky A4
- Vyrežte celu A4
- Presunúť do bunky A3
- Presuňte sa do bunky B3
- Prilepte do bunky B3
- atď.
To by bolo strašne nesprávne - potrebujeme, aby makro fungovalo na bunkách, ktoré sú od počiatočného bodu makra 1 a 2 bunky. Pri opakovanom spúšťaní makra bude počiatočným bodom riadok 4, riadok 5, riadok 6 atď. Záznam makra bez zapnutých relatívnych odkazov by spôsobil spustenie makra vždy ako riadok 3 ako počiatočný bod.
Nasleduj tieto kroky:
- Vyberte tlačidlo relatívnej referencie na paneli s nástrojmi Zastaviť nahrávanie
- Ukazovateľ na bunke by už mal byť na mene v stĺpci A.
- Stlačením šípky nadol sa presuňte na adresu
- Ctrl + x rezať
- Šípka hore, šípka doprava pre presun do stĺpca B vedľa mena
- Ctrl + v prilepiť
- Šípka dole dvakrát, šípka doľava raz, aby ste sa presunuli do mesta
- Ctrl + x rezať
- Šípka hore dvakrát, šípka doprava trice
- Ctrl + v prilepiť
- Šípka doľava dvakrát, šípka nadol raz, aby ste sa presunuli do teraz prázdneho riadku.
- Podržte stlačenú klávesu Shift a dvakrát stlačte šípku nadol
- Alt + edr odstránite prázdne riadky
- Šípka hore a šípka dole vyberte ďalšie meno v zozname.
- Kliknite na panel nástrojov Zastaviť nahrávanie
- Uložte zošit
- Vyskúšajte makro stlačením klávesovej skratky priradenej vyššie. Mal by dokonale opraviť ďalšie meno v zozname
Keď uvidíte, že makro funguje podľa želania, môžete podržaním klávesovej skratky Ctrl + a rýchlo opraviť niekoľko mien za sekundu. Celý zoznam 500 mien možno opraviť za minútu alebo dve.
Bonusový tip - nie je na výstave
Makro môžete ľahko zabaliť do jednoduchej slučky, aby opravilo všetkých 500 mien bez toho, aby ste museli stlačiť Ctrl + niekoľko stokrát.
Stlačením klávesovej skratky Alt + F11 otvorte editor makier.
Ak nevidíte tablu Projekt - VBAProject, stlačte Ctrl + r.
Pravým tlačidlom myši kliknite na Module1 a zvoľte Zobraziť kód. Uvidíte kód, ktorý vyzerá takto:
Teraz nemusíte rozumieť tomu, čo tento kód robí, ale viete, že chceme spustiť všetko v tomto makre raz pre každé meno, ktoré máme. Ak viete, že existuje 462 mien, môžete pridať 2 riadky, aby ste spustili kód 462-krát. V hornej časti makra vložte nový riadok so slovami „ For i = 1 to 462“. V dolnej časti makra vložte riadok s textom „ Next i“. Toto povedie VBA, aby spustil kód vnútri 462 krát.
Záver
Po tomto jednoduchom makre som ukázal príklad na veľmi zložitom makre. Toto makro vytvorilo kontingenčné tabuľky a grafy pre 46 oddelení spoločnosti. Táto správa trvala každý mesiac 40 hodín. Makro VBA sa teraz spustí a vytvorí všetkých 46 prehľadov za menej ako 2 minúty.
Kniha VBA a makrá Microsoft Excel 2016 vás prevedie procesom učenia, aby ste sa mohli dostať od nahrávania jednoduchých makier, ako je toto, k spusteniu veľmi zložitých správ, ktoré vám môžu ročne ušetriť stovky hodín. Samozrejme, ak sa nechcete učiť VBA, najmite si Excel konzultanta, ktorý vám navrhne správu.
