Tento tutoriál bude diskutovat o tom, jak zrychlit makra VBA a další doporučené postupy pro VBA.
Nastavení pro zrychlení kódu VBA
Níže najdete několik tipů, jak zrychlit kód VBA. Tipy jsou volně uspořádány podle důležitosti.
Nejjednodušší způsob, jak zvýšit rychlost kódu VBA, je deaktivovat ScreenUpdating a zakázat automatické výpočty. Tato nastavení by měla být zakázána ve všech velkých postupech.
Zakázat aktualizaci obrazovky
Ve výchozím nastavení aplikace Excel zobrazí změny sešitů v reálném čase při spuštění kódu VBA. To způsobí obrovské zpomalení rychlosti zpracování, protože Excel nejvíce interpretuje a zobrazuje změny pro každý řádek kódu.
Vypnutí aktualizace obrazovky:
| 1 | Application.ScreenUpdating = False |
Na konci makra byste měli zapnout aktualizaci obrazovky:
| 1 | Application.ScreenUpdating = True |
Zatímco je váš kód spuštěn, budete možná muset „aktualizovat“ obrazovku. Neexistuje žádný příkaz „obnovit“. Místo toho budete muset znovu zapnout aktualizaci obrazovky a znovu ji deaktivovat.
Nastavte Výpočty na Ručně
Kdykoli se změní hodnota buňky, musí Excel postupovat podle „stromu výpočtu“, aby přepočítal všechny závislé buňky. Navíc při každé změně vzorce bude Excel muset kromě přepočtu všech závislých buněk aktualizovat také „strom výpočtu“. V závislosti na velikosti vašeho sešitu mohou tyto přepočty způsobit, že se vaše makra spustí nepřiměřeně pomalu.
Chcete -li nastavit výpočty na ruční:
| 1 | Application.Calculation = xlManual |
Chcete-li ručně přepočítat celý sešit:
| 1 | Vypočítat |
Pokud je to nutné pro zvýšení rychlosti, můžete také vypočítat pouze list, rozsah nebo jednotlivou buňku.
Obnovení automatických výpočtů (na konci postupu):
| 1 | Application.Calculation = xlAutomatic |
Důležité! Toto je nastavení aplikace Excel. Pokud znovu nenastavíte výpočty na automatické, váš sešit se nebude přepočítávat, dokud to neřeknete.
Uvidíte největší vylepšení z výše uvedených nastavení, ale existuje několik dalších nastavení, která mohou něco změnit:
Zakázat události
Události jsou „spouštěče“, které způsobují zvláštní postupy akce běžet. Mezi příklady patří: když se změní jakákoli buňka na listu, když je list aktivován, když je sešit otevřen, než je sešit uložen atd.
Zakázání událostí může způsobit menší vylepšení rychlosti při spuštění jakéhokoli makra, ale zlepšení rychlosti může být mnohem větší, pokud váš sešit používá události. A v některých případech je deaktivace událostí nezbytná, aby se zabránilo vytváření nekonečných smyček.
Zakázání událostí:
| 1 | Application.EnableEvents = False |
Opětovné zapnutí událostí:
| 1 | Application.EnableEvents = True |
Zakázat PageBreaks
Zakázání PageBreaks může pomoci v určitých situacích:
- Dříve jste pro příslušný list nastavili vlastnost PageSetup a váš postup VBA upravuje vlastnosti mnoha řádků nebo sloupců
- NEBO Vaše procedura VBA nutí Excel vypočítat prolomení stránky (zobrazení náhledu tisku nebo úprava jakýchkoli vlastností PageSetup).
Chcete -li zakázat PageBreaks:
| 1 | ActiveSheet.DisplayPageBreaks = False |
Chcete-li znovu povolit PageBreaks:
| 1 | ActiveSheet.DisplayPageBreaks = True |
Osvědčené postupy ke zvýšení rychlosti VBA
Vyhněte se aktivaci a výběru
Při záznamu makra uvidíte mnoho metod aktivace a výběru:
| 12345678 | Sub Slow_Example ()Tabulky („List2“). VyberteRozsah („D9“). VyberteActiveCell.FormulaR1C1 = "příklad"Rozsah („D12“). VyberteActiveCell.FormulaR1C1 = "demo"Rozsah („D13“). VyberteEnd Sub |
Aktivace a výběr objektů je obvykle zbytečný, přidávají do kódu nepořádek a jsou velmi časově náročné. Pokud je to možné, měli byste se těmto metodám vyhnout.
Vylepšený příklad:
| 1234 | Dílčí rychlý_příklad ()Listy („List2“). Rozsah („D9“). FormulaR1C1 = „příklad“Listy („List2“). Rozsah („D12“). FormulaR1C1 = „demo“End Sub |
Vyhněte se kopírování a vkládání
Kopírování vyžaduje značnou paměť. Bohužel nemůžete VBA říci, aby vymazal interní paměť. Místo toho aplikace Excel vyčistí interní paměť v (zdánlivě) konkrétních intervalech. Pokud tedy provádíte mnoho operací kopírování a vkládání, riskujete příliš mnoho paměti, což může výrazně zpomalit váš kód nebo dokonce havarovat Excel.
Místo kopírování a vkládání zvažte nastavení hodnotových vlastností buněk.
| 123456789 | Dílčí kopie Vložit ()"Pomaleji."Rozsah ("a1: a1000"). Rozsah kopírování ("b1: b1000")`` RychlejiRozsah ("b1: b1000"). Hodnota = rozsah ("a1: a1000"). HodnotaEnd Sub |
Místo For Loops použijte smyčky For Each
Při procházení objekty je smyčka For Each rychlejší než smyčka For. Příklad:
Toto pro smyčku:
| 123456 | Sub Loop1 ()ztlumit jako RozsahPro i = 1 až 100Buňky (i, 1). Hodnota = 1Příště jáEnd Sub |
| 123456 | Sub Loop2 ()Dim buňka jako rozsahPro každou buňku v dosahu („a1: a100“)buňka.Hodnota = 1Další buňkaEnd Sub |
Deklarace proměnných / použití možnosti explicitní
VBA nevyžaduje, abyste deklarovali své proměnné, pokud do horní části modulu nepřidáte Option Explicit:| 1 | Možnost Explicitní |
| 1234 | Sub OptionExplicit ()var1 = 10MsgBox varlEnd Sub |
Použít s - Konec s prohlášeními
Pokud vícekrát odkazujete na stejné objekty (např. Rozsahy, Pracovní listy, Sešity), zvažte použití příkazu With. Zpracovává se rychleji, usnadňuje čtení kódu a zjednodušuje kód.S příkladem příkazu:| 12345678 | Dílčí rychlejší_příklad ()S listy („List2“).Range ("D9"). FormulaR1C1 = "příklad".Range ("D12"). FormulaR1C1 = "demo".Range ("D9"). Font.Bold = True.Range ("D12"). Font.Bold = TrueKonec sEnd Sub |
| 123456 | Sub Slow_Example ()Listy („List2“). Rozsah („D9“). FormulaR1C1 = „příklad“Listy („List2“). Rozsah („D12“). FormulaR1C1 = „demo“Listy („List2“). Rozsah („D9“). Písmo.Bold = TrueListy („List2“). Rozsah („D12“). Písmo.Bold = TrueEnd Sub |
Pokročilé tipy pro osvědčené postupy
Chraňte pouze UserInterfaceOnly
Je dobrým zvykem chránit vaše listy před úpravou nechráněných buněk, abyste zabránili koncovému uživateli (nebo vám!) V náhodném poškození sešitu. To však také ochrání pracovní listy před povolením VBA provádět změny. Pracovní listy tedy musíte odemknout a znovu chránit, což je při zpracování na mnoha listech velmi časově náročné.
| 12345 | Sub UnProtectSheet ()Tabulky („sheet1“). Odemknout „heslo“`` Upravit list 1Listy („sheet1“). Chraňte „heslo“End Sub |
Místo toho můžete listy chránit nastavením UserInterfaceOnly: = True. To umožňuje VBA provádět změny v listech a přitom je chránit před uživatelem.
| 1 | Listy („sheet1“). Chránit heslo: = „heslo“, UserInterFaceOnly: = True |
Důležité! UserInterFaceOnly se resetuje na hodnotu False při každém otevření sešitu. Chcete -li tedy použít tuto úžasnou funkci, budete muset použít události Workbook_Open nebo Auto_Open k nastavení nastavení při každém otevření sešitu.
Umístěte tento kód do modulu Thisworkbook:
| 123456 | Soukromý dílčí sešit_Otevřít ()Dim ws As WorksheetZa každé ws v pracovních listechws.Protect Password: = "heslo", UserInterFaceOnly: = TrueDalší wsEnd Sub |
nebo tento kód v libovolném pravidelném modulu:
| 123456 | Soukromé Sub Auto_Open ()Dim ws As WorksheetZa každé ws v pracovních listechws.Protect Password: = "heslo", UserInterFaceOnly: = TrueDalší wsEnd Sub |
K úpravám velkých rozsahů použijte pole
Manipulace s velkými rozsahy buněk (např. 100 000+) může být velmi časově náročná. Namísto procházení oblastí buněk a manipulace s každou buňkou můžete buňky načíst do pole, zpracovat každou položku v poli a poté vyvést pole zpět do jejich původních buněk. Načítání buněk do polí pro manipulaci může být mnohem rychlejší.
| 1234567891011121314151617181920212223242526272829303132 | Sub LoopRange ()Dim buňka jako rozsahDim tStart As DoubletStart = ČasovačPro každou buňku v dosahu („A1: A100000“)cell.Value = cell.Value * 100Další buňkaDebug.Print (Timer - tStart) & "seconds"End SubSub LoopArray ()Dim arr As VariantDim položka jako variantaDim tStart As DoubletStart = Časovačarr = rozsah ("A1: A100000"). HodnotaZa každou položku In arritem = item * 100Další položkaRozsah ("A1: A100000"). Hodnota = arDebug.Print (Timer - tStart) & "seconds"End Sub |
