Práce s makry aplikace Excel VBA
Makra v aplikaci Excel jsou uložena jako kód VBA a někdy budete chtít tento kód přímo upravit. Tento tutoriál se bude zabývat tím, jak zobrazit a upravit makra, popsat některé techniky ladění maker a poskytnout některé běžné příklady úprav.
Zobrazit makra
Seznam maker lze zobrazit v dialogu Makra. Chcete -li zobrazit toto dialogové okno, vyberte na pásu karet kartu Vývojář a klikněte na tlačítko Makra.
Pokud je otevřeno více sešitů, v seznamu se zobrazí makra ze všech sešitů. Makra v aktivním sešitu se budou zobrazovat pouze podle názvu, zatímco makra v jiných sešitech budou mít před sebou název sešitu a vykřičník (tj. „Book2! OtherMacro“).
Otevřete makro pro úpravy
Dialogové okno Makro můžete použít k otevření kódu makra výběrem názvu makra a kliknutím na tlačítko Upravit. Tím se makro otevře v editoru VB.
Alternativně můžete editor VB otevřít přímo kliknutím na tlačítko Visual Basic na kartě Vývojář nebo stisknutím klávesové zkratky ALT+F11.
Pomocí této metody budete muset přejít na požadované makro (také nazývané „postup“). Projdeme rozložení editoru VBA:
Přehled editoru VB
Editor VB má několik oken; v tomto tutoriálu pokryjeme okno projektu, okno vlastností a okno kódu.
Okno projektu
Okno projektu zobrazuje každý soubor aplikace Excel jako jeho vlastní projekt, přičemž všechny objekty v tomto projektu jsou kategorizovány podle typu. Zaznamenaná makra se objeví v kategorii „Moduly“, obvykle v objektu „Modul1“. (Pokud má váš projekt více modulů a nejste si jisti, kde je vaše makro uloženo, jednoduše ho otevřete z výše uvedeného dialogu Makra.)
Okno vlastností
Okno vlastností zobrazuje vlastnosti a související hodnoty objektu - například kliknutím na objekt listu v okně projektu se zobrazí seznam vlastností listu. Názvy vlastností jsou vlevo a hodnoty vlastností jsou vpravo.
Výběr modulu v okně projektu ukáže, že má pouze jednu vlastnost, „(Název)“. Název modulu můžete změnit dvojitým kliknutím na hodnotu vlastnosti, zadáním nového názvu a stisknutím klávesy Enter. Změnou názvu modulu jej přejmenujete v okně projektu, což je užitečné, pokud máte mnoho modulů.
Okna s kódem
Okna s kódy jsou speciální textové editory, ve kterých můžete upravovat kód VBA vašeho makra. Pokud byste chtěli vidět kód pro makro umístěné v Module1, dvakrát byste klikli na ‘Module1’ v okně projektu.
Spouštění maker v editoru VB
Makra lze spouštět přímo z editoru VB, což je užitečné pro testování a ladění.
Spuštění makra
- V okně projektu poklepejte na modul obsahující makro, které chcete otestovat (otevřete jeho okno Kód)
- V okně Kód umístěte kurzor kamkoli na kód makra mezi „Sub“ a „End Sub“
- Klikněte na Běh na panelu nástrojů nebo stiskněte klávesovou zkratku F5
„Projděte“ makro
Místo spuštění makra najednou můžete makro spustit po jednom řádku pomocí klávesové zkratky k „procházení“ kódu. Makro se na každém řádku pozastaví, což vám umožní zajistit, aby každý řádek kódu dělal to, co od Excelu očekáváte. Pomocí této metody můžete také kdykoli zastavit pokračování makra.
Postup „procházením“ makra:
- V okně projektu poklepejte na modul obsahující makro, které chcete otestovat (otevřete jeho okno Kód)
- V okně Kód umístěte kurzor kamkoli na kód makra
- Proces „krok za krokem“ zahájíte stisknutím klávesové zkratky F8
- Opakovaným stisknutím klávesy F8 urychlíte provádění kódu, což je indikováno žlutým zvýrazněním v okně Kód
- Chcete -li zabránit pokračování makra, stiskněte Resetovat knoflík
Proč upravovat makra VBA?
Makro rekordér - přestože je účinný - je také velmi omezený. V některých případech produkuje pomalá makra, zaznamenává akce, které jste neměli v úmyslu opakovat, nebo zaznamenává věci, o kterých jste si nemysleli, že je děláte. Naučit se upravovat makra jim pomůže běžet rychleji, efektivněji a předvídatelněji.
Kromě vyřešení těchto problémů získáte také masivní zvýšení produktivity, když využijete plný výkon maker. Makra nemusí být pouze záznamy úkolů - makra mohou obsahovat logiku, takže provádějí úkoly pouze za určitých podmínek. Během pouhých několika minut můžete kódovat smyčky, které opakují úkol stokrát nebo tisíckrát najednou!
Níže najdete několik užitečných tipů, které vám pomohou optimalizovat kód makra, a také nástroje, díky nimž vaše makra fungují tvrději a chytřeji.
Běžné příklady úprav makra
Zrychlete makra
Pokud máte makro, jehož spuštění trvá dlouho, může existovat několik důvodů, proč běží pomalu.
Za prvé: když se makro spustí, Excel zobrazí vše tak, jak se to děje v reálném čase - i když může Koukni se vlastně k tobě rychleukazující práce je významným výkonnostním hitem. Jedním ze způsobů, jak výrazně zrychlit běh Excelu, je sdělit mu to zastavte aktualizaci obrazovky:
'Disable Screen Updating Application.ScreenUpdating = False' Enable Screen Updating Application.ScreenUpdating = True
Řádek „Application.ScreenUpdating = False“ znamená, že neuvidíte fungující makro, ale poběží mnohem rychleji. Všimněte si toho, že ScreenUpdating byste měli vždy nastavit na True na konci makra, jinak aplikace Excel nemusí později fungovat tak, jak byste očekávali!
Další způsob, jak zrychlit makra:vypnout automatický výpočet v makru. Pokud jste pracovali se složitými tabulkami, budete vědět, že malé změny mohou vyvolat tisíce výpočtů, jejichž dokončení vyžaduje čas, a proto mnoho lidí v možnostech Excelu vypíná automatický výpočet. Můžete to také přepnout pomocí kódu VBA, takže vaše makro bude i nadále rychle fungovat na jiných počítačích. To pomáhá v případech, kdy kopírujete mnoho buněk vzorců nebo způsobujete spouštění mnoha výpočtů při vkládání dat do rozsahu:
'Zakázat aplikaci automatického výpočtu.Calculation = xlCalculationManual' Povolit aplikaci automatického výpočtu.Calculation = xlCalculationAutomatic
Přidat smyčky a logiku (pokud prohlášení)
Makro rekordér ukládá všechny vaše akce jako kód v jazyce zvaném VBA. VBA je více než jen způsob záznamu akcí v aplikaci Excel - je to programovací jazyk, což znamená, že může obsahovat kód pro rozhodování o tom, jaké akce provést, nebo akce opakovat, dokud není splněna podmínka.
Opakování
Řekněme, že chcete vytvořit makro, které připraví zprávu, a jako součást tohoto makra jste museli do sešitu přidat devatenáct listů, tedy celkem dvacet. Můžete zaznamenat, jak kliknete na tlačítko (+) znovu a znovu, nebo můžete napsat smyčku, která pro vás akci opakuje, například takto:
Sub ReportPrep () Dim i As Long For i = 1 to 19 Sheets. Add Next i End Sub
V tomto příkladu používáme a Pro smyčku, což je druh smyčky, která prochází řadou položek. Zde je náš rozsah čísel 1 až 19 pomocí proměnné s názvem „i“, aby smyčka mohla sledovat. V naší smyčce se mezi nimi opakuje pouze jedna akce pro adalší řádky (přidání listu), ale do smyčky můžete přidat tolik kódu, kolik chcete, například formátovat list nebo zkopírovat a vložit data na každý list - cokoli chcete opakovat.
Pokud prohlášení
An If prohlášení se používá k rozhodnutí, zda se nějaký kód spustí nebo ne, pomocí logického testu k rozhodnutí. Zde je jednoduchý příklad:
Sub ClearIfSmall () If Selection.Value <100 Then Selection.Clear End If End Sub
Tento jednoduchý příklad ukazuje, jak funguje příkaz If - otestujete nějakou podmínku, která je pravdivá nebo nepravdivá (je hodnota vybrané buňky menší než 100?), a pokud test vrátí True, spustí se kód uvnitř.
Nedostatkem tohoto kódu je, že testuje pouze jednu buňku najednou (a pokud byste vybrali více buněk, selhal by). To by bylo užitečnější, kdybyste mohli … procházet každou vybranou buňku a testovat každou …
Sub ClearIfSmall () Dim c jako rozsah pro každý c ve výběru.Cells If c.Hodnota <100 Then c.Clear End If Next c End Sub
V tomto příkladu existuje mírně odlišná smyčka For - tato smyčka neprochází řadou čísel, ale místo toho prochází všemi buňkami ve výběru pomocí proměnné s názvem „c“ pro sledování. Uvnitř smyčky se pomocí hodnoty „c“ určuje, zda má být buňka vymazána nebo ne.
Smyčky a příkazy If lze libovolně kombinovat - můžete do smyček vložit smyčky, nebo jednu do jiné, nebo pomocí If rozhodnout, zda má smyčka vůbec běžet.
<<>>
Odstraňte efekty posouvání
Běžným důvodem pro úpravu kódu makra je odstranění posouvání obrazovky. Při záznamu makra se možná budete muset dostat do jiných oblastí listu posouváním, ale k přístupu k datům se makro nemusí posouvat.
Posouvání může váš kód zaplnit stovkami nebo dokonce tisíci řádky nepotřebného kódu. Zde je příklad kódu, který se zaznamenává po kliknutí a přetažení na posuvníku:
Tento druh kódu je zcela zbytečný a lze jej odstranit, aniž by to mělo vliv na jakékoli další funkce. I kdybyste chtěli zachovat rolování, tento kód by mohl být stále zhuštěn do smyčky.
Odstraňte nadbytečný kód
Zaznamenaná makra obvykle přidávají spoustu nadbytečného kódu, který nemusí nutně odrážet to, co chcete, aby makro dělalo. Vezměte si například následující zaznamenaný kód, který zaznamenává změnu názvu písma v buňce:
I když byl změněn pouze název písma, bylo zaznamenáno jedenáct (11) změn písma, jako je velikost písma, textové efekty atd. Pokud bylo záměrem makra pouze změnit název písma (přičemž všechny ostatní vlastnosti byly ponechány samotné), zaznamenané makro by nefungovalo!
Toto makro je možné změnit tak, aby změnilo pouze název písma:
Toto makro bude nejen fungovat tak, jak bylo zamýšleno nyní, ale je také mnohem lépe čitelné.
Odstraňte pohyby kurzoru
Další věc, která se zaznamenává v makrech, je výběr listu a buněk. To je problém, protože uživatel může snadno ztratit přehled o tom, na čem právě pracoval, pokud se kurzor po spuštění makra přesune na jinou pozici.
Stejně jako při rolování, vy k provedení úkolu může být nutné přesunout kurzor a vybrat různé buňky, ale makra nemusí k přístupu k datům používat kurzor. Zvažte následující kód, který zkopíruje rozsah a poté jej vloží do tří dalších listů:
S tímto kódem je několik problémů:
- Uživatel ztratí své předchozí místo v sešitu
- Makro neurčuje, jaký list kopírujemez - to by mohl být problém, kdyby bylo makro spuštěno na špatném listu
Navíc je kód obtížně čitelný a nehospodárný. Tyto problémy lze vyřešit dostatečně snadno:
V tomto kódu je jasné, že kopírujeme z Listu1, a ani aktivní list, ani vybraný rozsah není třeba měnit, aby bylo možné data vložit. (Jednou významnou změnou je použití „PasteSpecial“ namísto „Paste“ - objekty rozsahu, jako „Range („ C4 ″) “, mají přístup pouze k příkazu PasteSpecial.)
Kdykoli je kód plný odkazů na „.Select“ a „Selection“, je to vodítko, že existuje prostor pro optimalizaci tohoto kódu a jeho zefektivnění.
