Práca s makrami programu Excel VBA
Makrá v programe Excel sú uložené ako kód VBA a niekedy budete chcieť tento kód priamo upraviť. Tento tutoriál sa zameriava na to, ako zobrazovať a upravovať makrá, popísať niektoré techniky ladenia makier a poskytnúť niekoľko bežných príkladov úprav.
Zobraziť makrá
Zoznam makier je možné zobraziť v dialógovom okne Makrá. Ak chcete zobraziť toto dialógové okno, vyberte na páse s nástrojmi kartu Vývojár a kliknite na tlačidlo Makrá.
Ak je otvorených viac zošitov, v zozname sa zobrazia makrá zo všetkých zošitov. Makrá v aktívnom zošite sa zobrazia iba podľa názvu, zatiaľ čo makrá v iných zošitoch budú mať pred názvom zošita a výkričníkom (t.j. „Kniha2! OtherMacro“).
Otvorte makro na úpravy
Dialógové okno Makro môžete použiť na otvorenie kódu makra tak, že vyberiete názov makra a kliknete na tlačidlo Upraviť. Makro sa otvorí v editore VB.
Alternatívne môžete editor VB otvoriť priamo kliknutím na tlačidlo Visual Basic na karte Vývojár alebo stlačením klávesovej skratky ALT+F11.
Pri použití tejto metódy budete musieť prejsť na požadované makro (nazýva sa tiež „postup“). Prejdeme si rozloženie editora VBA:
Prehľad editora VB
Editor VB má niekoľko okien; v tomto návode sa budeme venovať oknu projektu, oknu vlastností a oknu kódu.
Okno projektu
Okno projektu zobrazuje každý súbor programu Excel ako jeho vlastný projekt, pričom všetky objekty v tomto projekte sú kategorizované podľa typu. Zaznamenané makrá sa zobrazia v kategórii „Moduly“, zvyčajne v objekte „Modul1“. (Ak má váš projekt viacero modulov a nie ste si istí, kde je vaše makro uložené, jednoducho ho otvorte z vyššie uvedeného dialógového okna Makrá.)
Okno vlastností
Okno vlastností zobrazuje vlastnosti a súvisiace hodnoty objektu - napríklad kliknutím na objekt pracovného hárka v okne projektu sa zobrazí zoznam vlastností pracovného hárka. Názvy vlastností sú vľavo a hodnoty vlastností sú vpravo.
Výber modulu v okne Projekt ukáže, že má iba jednu vlastnosť „(Názov)“. Názov modulu môžete zmeniť dvojitým kliknutím na hodnotu vlastnosti, zadaním nového názvu a stlačením klávesu Enter. Zmenou názvu modulu ho premenujete v okne projektu, čo je užitočné, ak máte veľa modulov.
Okná s kódom
Okná s kódom sú špeciálne textové editory, v ktorých môžete upravovať kód VBA vášho makra. Ak by ste chceli vidieť kód pre makro umiestnené v Module1, dvakrát by ste klikli na ‘Module1’ v okne projektu.
Spustenie makier v editore VB
Makrá je možné spustiť priamo z editora VB, ktorý je užitočný pri testovaní a ladení.
Spustenie makra
- V okne projektu dvakrát kliknite na modul obsahujúci makro, ktoré chcete otestovať (aby sa otvorilo jeho okno Kód)
- V okne Kód umiestnite kurzor kdekoľvek na kód makra medzi „Sub“ a „End Sub“
- Kliknite na Utekaj na paneli s nástrojmi alebo stlačte klávesovú skratku F5
Makro „krok za krokom“
Namiesto spustenia makra naraz môžete makro spustiť po jednom riadku pomocou klávesovej skratky na „prechádzanie“ kódom. Makro sa pozastaví na každom riadku, čo vám umožní zaistiť, aby každý riadok kódu robil to, čo od programu Excel očakávate. Pomocou tejto metódy môžete tiež kedykoľvek zastaviť pokračovanie makra.
Postup „krokovaním“ v makre:
- V okne projektu dvakrát kliknite na modul obsahujúci makro, ktoré chcete otestovať (aby sa otvorilo jeho okno Kód)
- V okne Kód umiestnite kurzor kdekoľvek na kód makra
- Proces „krok za krokom“ spustíte stlačením klávesovej skratky F8
- Opakovaným stláčaním klávesu F8 spustíte vykonanie kódu, čo je označené žltým zvýraznením v okne Kód
- Ak chcete zastaviť pokračovanie makra, stlačte tlačidlo Resetovať tlačidlo
Prečo upravovať makrá VBA?
Makro rekordér - aj keď je účinný - je tiež veľmi obmedzený. V niektorých prípadoch vytvára pomalé makrá, zaznamenáva akcie, ktoré ste nemienili opakovať, alebo zaznamenáva veci, o ktorých ste si nemysleli, že ich robíte. Naučiť sa upravovať vaše makrá im pomôžu bežať rýchlejšie, efektívnejšie a predvídateľnejšie.
Okrem vyriešenia týchto problémov získate aj masívne zvýšenie produktivity, keď využijete plný výkon makier. Makrá nemusia byť len záznamom úloh - makrá môžu obsahovať logiku, takže vykonávajú úlohy iba za určitých podmienok. Za pár minút môžete nakódovať slučky, ktoré opakujú úlohu stokrát alebo tisíckrát naraz!
Nasleduje niekoľko užitočných tipov, ktoré vám pomôžu optimalizovať kód makra, a tiež nástroje, ktoré vám pomôžu lepšie a inteligentnejšie pracovať s makrami.
Bežné príklady úprav makier
Zrýchlite makrá
Ak máte makro, ktorého spustenie trvá dlho, môže to mať niekoľko dôvodov, prečo beží pomaly.
Po prvé: keď sa makro spustí, Excel zobrazí všetko tak, ako sa to deje v reálnom čase - aj keď môže pozri vlastne k tebe rýchloukazujúci dielo je významným výkonnostným hitom. Jedným zo spôsobov, ako dosiahnuť, aby bol program Excel spustený výrazne rýchlejšie, je povedať mu to zastaviť aktualizáciu obrazovky:
'Zakázať aktualizáciu obrazovky Application.ScreenUpdating = False' Povoliť aktualizáciu obrazovky Application.ScreenUpdating = True
Riadok „Application.ScreenUpdating = False“ znamená, že makro nebude fungovať, ale pobeží oveľa rýchlejšie. Všimnite si toho, že ScreenUpdating by ste mali vždy nastaviť na True na konci makra, inak by Excel neskôr nemusel fungovať tak, ako by ste chceli!
Ďalší spôsob, ako urýchliť makrá:vypnite automatický výpočet v makre. Ak ste pracovali s komplexnými tabuľkami, budete vedieť, že malé zmeny môžu vyvolať tisíce výpočtov, ktorých dokončenie trvá dlho, a preto mnoho ľudí vypína automatické počítanie v možnostiach Excelu. Môžete to tiež prepnúť pomocou kódu VBA, aby vaše makro stále fungovalo rýchlo na iných počítačoch. Pomáha to v prípadoch, keď kopírujete veľa buniek vzorcov alebo spôsobuje, že sa pri vkladaní údajov do rozsahu spustí mnoho výpočtov:
„Zakázať aplikáciu na automatický výpočet. Výpočet = xlCalculationManual“ Povoliť aplikáciu na automatický výpočet. Výpočet = xlCalculationAutomatic
Pridajte slučky a logiku (ak vyhlásenia)
Makro rekordér ukladá všetky vaše akcie ako kód v jazyku nazývanom VBA. VBA je viac než len spôsob zaznamenávania akcií v programe Excel - je to programovací jazyk, čo znamená, že môže obsahovať kód na rozhodovanie o tom, aké akcie sa majú vykonať, alebo akcie opakovať, kým nie je splnená podmienka.
Opakovanie
Povedzme, že ste chceli vytvoriť makro, ktoré pripravilo správu, a ako súčasť tohto makra ste museli do zošita pridať devätnásť listov, teda celkom dvadsať. Môžete sa zaznamenať, ako klikáte na tlačidlo (+) znova a znova, alebo môžete napísať slučku, ktorá za vás akciu zopakuje, napríklad takto:
Sub ReportPrep () Dim i As Long for i = 1 to 19 Sheets. Add Next i End Sub
V tomto prípade používame a Pre slučku, čo je druh slučky, ktorá iteruje cez celý rad položiek. Náš rozsah sú čísla 1 až 19 pomocou premennej s názvom „i“, aby mohla slučka sledovať. V našej slučke sa medzi nimi opakuje iba jedna akcia pre aĎalšie riadky (pridávanie hárkov), ale do slučky môžete pridať toľko kódu, koľko chcete, napríklad formátovať hárok alebo kopírovať a vkladať údaje do každého hárka - čokoľvek chcete opakovať.
Ak vyhlásenia
An Ak vyhlásenie sa používa na rozhodnutie, či sa nejaký kód spustí alebo nie, pomocou logického testu, ktorý urobí rozhodnutie. Tu je jednoduchý príklad:
Sub ClearIfSmall () If Selection.Value <100 Then Selection.Clear End If End Sub
Tento jednoduchý príklad ukazuje, ako funguje príkaz If - otestujete nejakú podmienku, ktorá je pravdivá alebo nepravdivá (je hodnota vybranej bunky menšia ako 100?), a ak test vráti hodnotu True, spustí sa kód vnútri.
Nedostatkom tohto kódu je, že testuje iba jednu bunku naraz (a zlyhal by, ak by ste vybrali viac buniek). To by bolo užitočnejšie, keby ste mohli … prejsť všetkými vybranými bunkami a otestovať každú z nich …
Sub ClearIfSmall () Dim c as Range for each c in Selection.Cells If c.Hodnota <100 Then c.Clear End If Next c End Sub
V tomto prípade existuje mierne odlišná slučka For - táto sa necyklí v rade čísel, ale namiesto toho prechádza všetkými bunkami vo výbere a na sledovanie používa premennú s názvom „c“. V cykle sa hodnota „c“ používa na určenie, či sa má bunka vymazať alebo nie.
Príkazy Loops a If je možné ľubovoľne kombinovať - slučky môžete vložiť do slučiek alebo jednu do druhej, alebo pomocou If rozhodnúť, či by sa mala slučka vôbec spustiť.
<<>>
Odstráňte efekty posúvania
Bežným dôvodom na úpravu kódu makra je odstránenie rolovania obrazovky. Pri zázname makra sa možno budete musieť posúvaním dostať do iných oblastí pracovného hárka, ale na prístup k údajom sa makrá nemusia posúvať.
Posúvanie môže váš kód zaplniť stovkami alebo dokonca tisíckami riadkov nepotrebného kódu. Tu je príklad kódu, ktorý sa zaznamená po kliknutí a potiahnutí na posúvači:
Tento druh kódu je úplne zbytočný a je možné ho odstrániť bez toho, aby to malo vplyv na ďalšie funkcie. Aj keď by ste chceli zachovať rolovanie, tento kód by bolo stále možné kondenzovať do slučky.
Odstráňte nadbytočný kód
Zaznamenané makrá zvyčajne pridávajú veľa nadbytočného kódu, ktorý nemusí nevyhnutne odrážať to, čo má makro robiť. Vezmite si napríklad nasledujúci zaznamenaný kód, ktorý zaznamenáva zmenu názvu písma v bunke:
Aj keď bol zmenený iba názov písma, bolo zaznamenaných jedenásť (11) zmien písma, ako je veľkosť písma, textové efekty atď. Ak bolo zmyslom makra zmeniť iba názov písma (pričom všetky ostatné vlastnosti boli ponechané osamote), zaznamenané makro by nefungovalo!
Toto makro je možné zmeniť tak, aby zmenilo iba názov písma:
Toto makro bude nielen fungovať tak, ako bolo teraz určené, ale bude aj oveľa jednoduchšie sa čítať.
Odstráňte pohyby kurzora
Ďalšou vecou, ktorá sa zaznamenáva v makrách, je výber pracovného hárka a buniek. Je to problém, pretože používateľ môže ľahko stratiť prehľad o tom, na čom práve pracoval, ak sa kurzor po spustení makra presunie na inú pozíciu.
Rovnako ako pri posúvaní, ty Na vykonanie úlohy môže byť potrebné presunúť kurzor a vybrať rôzne bunky, ale makrá nemusia na prístup k údajom používať kurzor. Zvážte nasledujúci kód, ktorý skopíruje rozsah a potom ho vloží do troch ďalších hárkov:
S týmto kódom je niekoľko problémov:
- Používateľ príde o svoje predchádzajúce miesto v zošite
- Makro nešpecifikuje, ktorý hárok kopírujemeod - to by mohol byť problém, ak by bolo makro spustené na nesprávnom hárku
Kód je navyše ťažko čitateľný a zbytočný. Tieto problémy je možné vyriešiť dostatočne jednoducho:
V tomto kóde je zrejmé, že kopírujeme z hárka 1, a na prilepenie údajov nie je potrebné meniť aktívny pracovný hárok ani vybratý rozsah. (Jednou významnou zmenou je použitie „PasteSpecial“ namiesto „Vložiť“ - Objekty rozsahu, ako napríklad „Rozsah (“ C4 ″), majú prístup iba k príkazu PasteSpecial.)
Kedykoľvek je kód plný odkazov na „.Vyber“ a „Výber“, je to stopa, že existuje priestor na optimalizáciu tohto kódu a jeho väčšiu efektivitu.
