Tento tutoriál bude diskutovať o tom, ako urýchliť makrá VBA a ďalšie osvedčené postupy VBA.
Nastavenia na urýchlenie kódu VBA
Nasleduje niekoľko tipov na urýchlenie kódu VBA. Tipy sú voľne usporiadané podľa dôležitosti.
Najjednoduchším spôsobom, ako zvýšiť rýchlosť kódu VBA, je vypnúť funkciu ScreenUpdating a zakázať automatické výpočty. Tieto nastavenia by mali byť deaktivované vo všetkých veľkých postupoch.
Zakázať aktualizáciu obrazovky
V predvolenom nastavení Excel pri spustení kódu VBA zobrazí zmeny v zošitoch v reálnom čase. To spôsobuje obrovské spomalenie rýchlosti spracovania, pretože Excel väčšinou interpretuje a zobrazuje zmeny pre každý riadok kódu.
Ak chcete vypnúť aktualizáciu obrazovky:
| 1 | Application.ScreenUpdating = False |
Na konci makra by ste mali znova zapnúť aktualizáciu obrazovky:
| 1 | Application.ScreenUpdating = True |
Kým je váš kód spustený, možno budete musieť „obnoviť“ obrazovku. Neexistuje žiadny príkaz „obnoviť“. Namiesto toho budete musieť aktualizáciu obrazovky znova zapnúť a vypnúť.
Nastaviť výpočty na manuálne
Pri každej zmene hodnoty bunky musí Excel pri prepočte všetkých závislých buniek postupovať podľa „stromu výpočtov“. Okrem toho, vždy, keď sa zmení vzorec, bude Excel musieť okrem prepočtu všetkých závislých buniek aktualizovať aj „strom výpočtu“. V závislosti od veľkosti zošita môžu tieto prepočty spôsobiť, že vaše makrá budú bežať neprimerane pomaly.
Ak chcete nastaviť výpočty na manuálne:
| 1 | Application.Calculation = xlManual |
Ak chcete manuálne prepočítať celý zošit:
| 1 | Vypočítajte |
Ak je to potrebné pre zvýšenie rýchlosti, môžete tiež vypočítať iba hárok, rozsah alebo jednotlivú bunku.
Ak chcete obnoviť automatické výpočty (na konci postupu):
| 1 | Application.Calculation = xlAutomatic |
Dôležité! Toto je nastavenie programu Excel. Ak neprepočítate výpočty na automatické, váš zošit sa neprepočíta, kým to nepoviete.
Uvidíte najväčšie vylepšenia z vyššie uvedených nastavení, ale existuje niekoľko ďalších nastavení, ktoré môžu znamenať rozdiel:
Zakázať udalosti
Udalosti sú „spúšťače“, ktoré spôsobujú špeciálne postupy udalostí bežať. Medzi príklady patria: keď sa zmení ľubovoľná bunka v hárku, keď je pracovný hárok aktivovaný, keď je zošit otvorený, pred uložením zošita atď.
Zakázanie udalostí môže spôsobiť mierne zvýšenie rýchlosti pri spustení akýchkoľvek makier, ale zlepšenie rýchlosti môže byť oveľa väčšie, ak váš zošit používa udalosti. A v niektorých prípadoch je vypnutie udalostí nevyhnutné, aby sa zabránilo vytváraniu nekonečných slučiek.
Ak chcete zakázať udalosti:
| 1 | Application.EnableEvents = False |
Ak chcete znova zapnúť udalosti:
| 1 | Application.EnableEvents = Pravda |
Zakážte PageBreaks
Zakázanie PageBreaks môže pomôcť v určitých situáciách:
- Predtým ste pre príslušný pracovný hárok nastavili vlastnosť PageSetup a váš postup VBA upravuje vlastnosti mnohých riadkov alebo stĺpcov
- ALEBO Váš postup VBA núti program Excel vypočítať zlomenie stránky (zobrazenie ukážky pred tlačou alebo úpravu akýchkoľvek vlastností PageSetup).
Ak chcete zakázať PageBreaks:
| 1 | ActiveSheet.DisplayPageBreaks = Nepravda |
Ak chcete znova povoliť PageBreaks:
| 1 | ActiveSheet.DisplayPageBreaks = True |
Osvedčené postupy na zvýšenie rýchlosti VBA
Vyhnite sa aktivácii a výberu
Pri zaznamenávaní makra sa vám zobrazí mnoho spôsobov aktivácie a výberu:
| 12345678 | Subslow_Example ()Tabuľky („List2“). VyberteRozsah („D9“). VyberteActiveCell.FormulaR1C1 = "príklad"Rozsah („D12“). VyberteActiveCell.FormulaR1C1 = "demo"Rozsah („D13“). VyberteKoniec pod |
Aktivácia a výber objektov je zvyčajne zbytočné, spôsobujú neporiadok vo vašom kóde a sú veľmi časovo náročné. Ak je to možné, mali by ste sa týmto metódam vyhnúť.
Vylepšený príklad:
| 1234 | Sub Fast_Priklad ()Listy („List2“). Rozsah („D9“). FormulaR1C1 = „príklad“Listy („List2“). Rozsah („D12“). FormulaR1C1 = „demo“Koniec pod |
Vyhnite sa kopírovaniu a vkladaniu
Kopírovanie vyžaduje značnú pamäť. VBA bohužiaľ nemôžete prikázať, aby vymazal vnútornú pamäť. Namiesto toho program Excel vyčistí svoju vnútornú pamäť v (zdanlivo) konkrétnych intervaloch. Ak teda vykonávate veľa operácií kopírovania a vkladania, riskujete príliš veľa pamäte, čo môže drasticky spomaliť kód alebo dokonca zlyhať program Excel.
Namiesto kopírovania a vkladania zvážte nastavenie hodnotových vlastností buniek.
| 123456789 | Vložiť kópiu ()„PomalšieRozsah ("a1: a1000"). Rozsah kopírovania ("b1: b1000")„RýchlejšieRozsah ("b1: b1000"). Hodnota = rozsah ("a1: a1000"). HodnotaKoniec pod |
Namiesto pre slučky použite slučky For Each
Pri slučke medzi objektmi je slučka For Each rýchlejšia ako slučka For. Príklad:
Toto pre slučku:
| 123456 | Sub Loop1 ()stmaviť ako rozsahPre i = 1 až 100Bunky (i, 1). Hodnota = 1Ďalej iKoniec pod |
| 123456 | Sub Loop2 ()Dim bunka ako rozsahPre každú bunku v rozsahu („a1: a100“)bunka. Hodnota = 1Ďalšia bunkaKoniec pod |
Deklarovať premenné / Použiť možnosť explicitne
VBA nevyžaduje, aby ste deklarovali svoje premenné, pokiaľ do hornej časti modulu nepridáte Explicitnú možnosť:| 1 | Možnosť explicitná |
| 1234 | Sub OptionExplicit ()var1 = 10MsgBox varlKoniec pod |
Používajte s - Koniec s vyhláseniami
Ak viackrát odkazujete na rovnaké objekty (napr. Rozsahy, pracovné hárky, zošity), zvážte použitie príkazu With. Spracováva sa rýchlejšie, môže uľahčiť čítanie kódu a zjednodušuje ho.S príkladom vyhlásenia:| 12345678 | Sub rýchlejší_priklad ()S tabuľkami („List2“).Rozsah („D9“). FormulaR1C1 = „príklad“.Range ("D12"). FormulaR1C1 = "demo".Range ("D9"). Font.Bold = True.Range ("D12"). Font.Bold = TrueUkončiť sKoniec pod |
| 123456 | Subslow_Example ()Listy („List2“). Rozsah („D9“). FormulaR1C1 = „príklad“Listy („List2“). Rozsah („D12“). FormulaR1C1 = „demo“Listy („List2“). Rozsah („D9“). Písmo.Bold = PravdaListy („List2“). Rozsah („D12“). Písmo.Bold = PravdaKoniec pod |
Pokročilé tipy pre osvedčené postupy
Chráňte iba UserInterface
Je dobrým zvykom chrániť svoje pracovné hárky pred úpravou nechránených buniek, aby ste zabránili tomu, aby koncový používateľ (alebo vy!) Omylom poškodili zošit. To však tiež ochráni pracovné hárky pred povolením VBA vykonávať zmeny. Preto musíte pracovné listy nechrániť a znova chrániť, čo je pri mnohých listoch veľmi časovo náročné.
| 12345 | Sub UnProtectSheet ()Tabuľky („sheet1“). Zrušte ochranu hesla ”„Upraviť list 1Hárky („sheet1“). Chráňte si hesloKoniec pod |
Namiesto toho môžete hárky chrániť nastavením UserInterfaceOnly: = True. To umožňuje VBA vykonávať zmeny v hárkoch a zároveň ich chrániť pred používateľom.
| 1 | Hárky („sheet1“). Chrániť heslo: = „heslo“, UserInterFaceOnly: = pravda |
Dôležité! UserInterFaceOnly sa resetuje na hodnotu False pri každom otvorení zošita. Ak chcete používať túto úžasnú funkciu, budete musieť použiť udalosti Workbook_Open alebo Auto_Open na nastavenie nastavenia pri každom otvorení zošita.
Vložte tento kód do modulu Tento pracovný zošit:
| 123456 | Súkromný čiastkový zošit_Otvorený ()Dim ws As worksheetZa každé ws v pracovných listochws.Protect Password: = "heslo", UserInterFaceOnly: = TrueĎalšie wsKoniec pod |
alebo tento kód v ľubovoľnom pravidelnom module:
| 123456 | Súkromné čiastkové automatické otváranie ()Dim ws As worksheetZa každé ws v pracovných listochws.Protect Password: = "heslo", UserInterFaceOnly: = TrueĎalšie wsKoniec pod |
Na úpravu veľkých rozsahov použite polia
Manipulácia s veľkými rozsahmi buniek (napr. 100 000+) môže byť veľmi časovo náročná. Namiesto cyklovania medzi rozsahmi buniek a manipulácie s každou bunkou môžete bunky načítať do poľa, spracovať každú položku v poli a potom pole odoslať späť do ich pôvodných buniek. Načítanie buniek do polí na manipuláciu môže byť oveľa rýchlejšie.
| 1234567891011121314151617181920212223242526272829303132 | Rozsah čiastkovej slučky ()Dim bunka ako rozsahDim tStart As DoubletStart = ČasovačPre každú bunku v rozsahu („A1: A100000“)cell.Value = cell.Value * 100Ďalšia bunkaDebug.Print (Timer - tStart) & "seconds"Koniec podSub LoopArray ()Dim arr As VariantDim položka ako variantDim tZačnite ako dvojitýtStart = Časovačarr = rozsah ("A1: A100000"). HodnotaZa každú položku v poradípoložka = položka * 100Nasledujúca položkaRozsah ("A1: A100000"). Hodnota = arDebug.Print (časovač - tStart) & „sekundy“Koniec pod |
