Keď sú vaše makrá Excelu čoraz robustnejšie a komplexnejšie, možno zistíte, že strácajú výkon. Keď sa hovorí o makrách, slovo výkon je zvyčajne synonymom rýchlosti . Rýchlosť vyjadruje, ako rýchlo vaše procedúry VBA vykonávajú zamýšľané úlohy. Nasleduje desať spôsobov, ktoré vám pomôžu udržať vaše makrá programu Excel v prevádzke na optimálnej úrovni výkonu.
Zastavenie výpočtov listu
Vedeli ste, že zakaždým, keď sa bunka, ktorá ovplyvňuje akýkoľvek vzorec vo vašom hárku, zmení alebo sa s ňou manipuluje, Excel prepočíta celý hárok? V pracovných hárkoch, ktoré obsahujú veľké množstvo vzorcov, môže toto správanie výrazne spomaliť vaše makrá.
Vlastnosť Application.Calculation môžete použiť na prikázanie Excelu, aby sa prepol do režimu manuálneho výpočtu. Keď je zošit v režime manuálneho výpočtu, zošit sa neprepočíta, kým explicitne nespustíte výpočet stlačením klávesu F9.
Umiestnite Excel do režimu manuálneho výpočtu, spustite kód a potom sa prepnite späť do režimu automatického výpočtu.
Pod makro1()
Application.Calculation = xlCalculationManual
Sem umiestnite kód makra
Application.Calculation = xlCalculationAutomatic
End Sub
Nastavenie režimu výpočtu späť na xlCalculationAutomatic automaticky spustí prepočet hárka, takže po spustení makra nie je potrebné stláčať kláves F9.
Zakázanie aktualizácie obrazovky hárka
Môžete si všimnúť, že keď sa spustia vaše makrá, obrazovka poriadne bliká. Toto blikanie sa Excel pokúša prekresliť obrazovku tak, aby zobrazovala aktuálny stav pracovného hárka. Bohužiaľ, zakaždým, keď Excel prekreslí obrazovku, zaberá pamäťové zdroje.
Vlastnosť Application.ScreenUpdating môžete použiť na zakázanie aktualizácií obrazovky, kým sa vaše makro nedokončí. Vypnutím aktualizácie obrazovky šetríte čas a zdroje, vďaka čomu bude vaše makro bežať o niečo rýchlejšie. Po dokončení spustenia kódu makra môžete aktualizáciu obrazovky znova zapnúť.
Pod makro1()
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Sem umiestnite kód makra
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = Pravda
End Sub
Po nastavení vlastnosti ScreenUpdating späť na hodnotu True Excel automaticky spustí prekreslenie obrazovky.
Vypnutie aktualizácií stavového riadku
Stavový riadok Excelu, ktorý sa zobrazuje v spodnej časti okna Excelu, zvyčajne zobrazuje priebeh určitých akcií v Exceli. Ak vaše makro pracuje s množstvom údajov, stavový riadok zaberie určité zdroje.
Je dôležité poznamenať, že vypnutie aktualizácie obrazovky je oddelené od vypnutia zobrazenia stavového riadka. Stavový riadok sa bude naďalej aktualizovať, aj keď zakážete aktualizáciu obrazovky. Vlastnosť Application.DisplayStatusBar môžete použiť na dočasné zakázanie akýchkoľvek aktualizácií stavového riadka, čím ďalej zlepšíte výkon svojho makra:
Pod makro1()
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Application.DisplayStatusBar = False
Sem umiestnite kód makra
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = Pravda
Application.DisplayStatusBar = True
End Sub
Prikázanie Excelu ignorovať udalosti
Makrá môžete implementovať ako procedúry udalostí, ktoré prikazujú Excelu spustiť určitý kód, keď sa zmení pracovný hárok alebo zošit.
Niekedy štandardné makrá vykonajú zmeny, ktoré spustia procedúru udalosti. Napríklad, ak máte štandardné makro, ktoré manipuluje s niekoľkými bunkami na Hárok1, zakaždým, keď sa bunka na tomto hárku zmení, vaše makro sa musí pozastaviť, kým sa spustí udalosť Worksheet_Change.
Môžete pridať ďalšiu úroveň zvýšenia výkonu pomocou vlastnosti EnableEvents, aby ste Excelu prikázali ignorovať udalosti počas spustenia makra.
Pred spustením makra nastavte vlastnosť EnableEvents na hodnotu False. Po dokončení spustenia kódu makra môžete vlastnosť EnableEvents nastaviť späť na hodnotu True.
Pod makro1()
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Application.DisplayStatusBar = False
Application.EnableEvents = False
Sem umiestnite kód makra
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = Pravda
Application.DisplayStatusBar = True
Application.EnableEvents = True
End Sub
Skrytie zlomov strán
Zakaždým, keď vaše makro upraví počet riadkov, upraví počet stĺpcov alebo zmení nastavenie strany hárka, Excel je nútený chvíľu trvať, kým prepočíta konce strán zobrazené na hárku.
Tomuto správaniu sa môžete vyhnúť jednoduchým skrytím zlomov strán pred spustením makra.
Ak chcete skryť zlomy strán, nastavte vlastnosť hárka DisplayPageBreaks na hodnotu False. Ak chcete po spustení makra naďalej zobrazovať zlomy strán, nastavte vlastnosť hárka DisplayPageBreaks späť na hodnotu True.
Pod makro1()
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Application.DisplayStatusBar = False
Application.EnableEvents = False
Activesheet.DisplayPageBreaks = False
Sem umiestnite kód makra
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = Pravda
Application.DisplayStatusBar = True
Application.EnableEvents = True
Activesheet.DisplayPageBreaks = True
End Sub
Pozastavenie aktualizácií kontingenčnej tabuľky
Ak vaše makro manipuluje s kontingenčnými tabuľkami, ktoré obsahujú veľké zdroje údajov, môžete zaznamenať slabý výkon pri vykonávaní vecí, ako je dynamické pridávanie alebo presúvanie kontingenčných polí.
Výkon svojho makra môžete zlepšiť pozastavením prepočítavania kontingenčnej tabuľky, kým sa nevykonajú všetky zmeny kontingenčného poľa. Jednoducho nastavte vlastnosť PivotTable.ManualUpdate na hodnotu True, aby ste odložili prepočet, spustite kód makra a potom nastavte vlastnosť PivotTable.ManualUpdate späť na hodnotu False, aby sa spustil prepočet.
Pod makro1()
ActiveSheet.PivotTables("PivotTable1").ManualUpdate=Pravda
Sem umiestnite kód makra
ActiveSheet.PivotTables("PivotTable1").ManualUpdate=False
End Sub
Vyhnite sa kopírovaniu a prilepeniu
Je dôležité si zapamätať, že hoci Macro Recorder šetrí čas písaním kódu VBA za vás, nie vždy napíše najefektívnejší kód. Skvelým príkladom je, ako Macro Recorder zachytí akúkoľvek akciu kopírovania a vkladania, ktorú vykonávate počas nahrávania.
Svoje makrá môžete mierne podporiť vystrihnutím prostredníka a vykonaním priameho kopírovania z jednej bunky do cieľovej bunky. Tento alternatívny kód používa argument Cieľ na obídenie schránky a skopírovanie obsahu bunky A1 priamo do bunky B1.
Rozsah("A1"). Kopírovať cieľ:=Rozsah("B1")
Ak potrebujete skopírovať iba hodnoty (nie formátovanie alebo vzorce), môžete výkon ešte zlepšiť tým, že sa vyhnete použitiu metódy Kopírovať. Jednoducho nastavte hodnotu cieľovej bunky na rovnakú hodnotu, aká sa nachádza v zdrojovej bunke. Táto metóda je približne 25-krát rýchlejšia ako metóda kopírovania:
Rozsah("B1").Hodnota = Rozsah("A1").Hodnota
Ak potrebujete skopírovať iba vzorce z jednej bunky do druhej (nie hodnoty alebo formátovanie), môžete nastaviť vzorec cieľovej bunky na rovnaký vzorec, ktorý obsahuje zdrojová bunka:
Rozsah("B1").Vzorec = Rozsah("A1").Vzorec
Použitie príkazu With
Pri nahrávaní makier budete často manipulovať s rovnakým objektom viackrát. Môžete ušetriť čas a zlepšiť výkon pomocou príkazu With na vykonanie niekoľkých akcií s daným objektom v jednom zábere.
Príkaz With použitý v nasledujúcom príklade hovorí programu Excel, aby použil všetky zmeny formátovania naraz:
S rozsahom("A1").Písmo
.Tučné = pravda
.Kurzíva = pravda
.Underline = xlUnderlineStyleSingle
Koniec s
Ak si zvyknete rozdeľovať akcie do príkazov With, vaše makrá budú nielen rýchlejšie fungovať, ale aj čítanie kódu makra bude jednoduchšie.
Vyhnite sa metóde Select
Macro Recorder s obľubou používa metódu Select na explicitný výber objektov pred vykonaním akcií. Vo všeobecnosti nie je potrebné objekty pred prácou s nimi vyberať. V skutočnosti môžete výrazne zlepšiť výkon makra tým, že nepoužívate metódu Select.
Po zaznamenaní makier si zvyknite zmeniť vygenerovaný kód, aby ste odstránili metódy Select. V tomto prípade bude optimalizovaný kód vyzerať takto:
Hárok("Hárok1").Rozsah("A1").VzorecR1C1 = "1000"
Hárok("Hárok2").Rozsah("A1").VzorecR1C1 = "1000"
Hárok("Hárok3").Rozsah("A1").VzorecR1C1 = "1000"
Všimnite si, že sa nevyberá nič. Kód jednoducho používa hierarchiu objektov na uplatnenie potrebných akcií.
Obmedzenie výletov do pracovného listu
Ďalším spôsobom, ako urýchliť makrá, je obmedziť počet odkazov na údaje pracovného hárka v kóde. Vždy je menej efektívne získavať údaje z pracovného hárka ako z pamäte. To znamená, že vaše makrá budú bežať oveľa rýchlejšie, ak nebudú musieť opakovane interagovať s pracovným hárkom.
Napríklad nasledujúci jednoduchý kód núti VBA neustále sa vracať do Sheets(“Sheet1”).Range(“A1”), aby získal číslo potrebné na vykonanie porovnania v príkaze If:
Pre mesiac prehľadu = 1 až 12
If Range("A1").Hodnota = ReportMonth Then
MsgBox 1000000 / mesiac prehľadu
Koniec Ak
Ďalší mesiac prehľadov
Oveľa efektívnejšou metódou je uložiť hodnotu do Sheets(“Sheet1”).Range(“A1”) do premennej s názvom MyMonth. Týmto spôsobom kód odkazuje na premennú MyMonth namiesto hárka:
Dim MyMonth ako Integer
Môj mesiac = Rozsah("A1").Hodnota
Pre mesiac prehľadu = 1 až 12
Ak MyMonth = ReportMonth Then
MsgBox 1000000 / mesiac prehľadu
Koniec Ak
Ďalší mesiac prehľadov
Zvážte využitie premenných na prácu s údajmi v pamäti na rozdiel od priameho odkazovania na pracovné hárky.
Vyhnite sa nadmerným referenciám
Pri volaní metódy alebo vlastnosti objektu je potrebné prejsť cez rozhranie IDispatch komponentu OLE. Volania týchto komponentov OLE si vyžadujú čas, takže zníženie počtu odkazov na komponenty OLE môže zvýšiť rýchlosť kódu makra.
Na vyvolanie vlastností alebo metód objektu sa všeobecne používa metóda reprezentácie Object.Method , to znamená "." symbol sa používa na vyvolanie vlastností a metód.
Preto je možné počet volaní metódy alebo vlastnosti posudzovať podľa počtu symbolov ".". Čím menej "." tým rýchlejšie kód beží.
Napríklad nasledujúce vyhlásenie obsahuje 3 symboly ".".
ThisWorkbook.Sheet1.Range("A1").Value = 100
Nasledujúce vyhlásenie má iba jeden symbol ".".
Activewindow.Top = 100
Tu je niekoľko trikov na zníženie počtu symbolov "." bežať rýchlejšie.
Po prvé, keď potrebujete opakovane odkazovať na ten istý objekt, môžete objekt nastaviť na premennú, aby ste znížili počet hovorov. Napríklad nasledujúci kód vyžaduje dva hovory na riadok.
ThisWorkbook.Sheets("Sheet1").Cells(1, 1) = 100
ThisWorkbook.Sheets("Sheet1").Cells(2, 1) = 200
ThisWorkbook.Sheets("Sheet1").Cells(3, 1) = 300
Pretože na objekt Sheets("Sheet1") je potrebné odkazovať opakovane, možno ho najprv nastaviť na premennú sht , takže každý kód stačí volať len raz.
Set sht = ThisWorkbook.Sheets("Sheet1")
sht.Cells(1, 1) = 100
sht.Cells(2, 1) = 200
sht.Cells(3, 1) = 300
Po druhé, ak nechcete deklarovať dočasnú premennú sht, môžete tiež použiť príkaz With spomenutý vyššie. Ako ukazuje nasledujúci príklad:
With ThisWorkbook.Sheets("Sheet1")
.Cells(1, 1) = 100
.Cells(2, 1) = 200
.Cells(3, 1) = 300
End With
Po tretie, keď existuje veľa slučiek, snažte sa ponechať vlastnosti a metódy mimo cyklu. Pri opätovnom použití hodnoty vlastnosti toho istého objektu v slučke môžete najskôr priradiť hodnotu vlastnosti zadanej premennej mimo slučky a potom použiť premennú v slučke, čím môžete dosiahnuť vyššiu rýchlosť. Ako ukazuje nasledujúci príklad:
For i = 1 To 1000
ThisWorkbook.Sheets("Sheet1").Cells(1, 1) = Cells(1, 2).Value
ThisWorkbook.Sheets("Sheet1").Cells(2, 1) = Cells(1, 2).Value
ThisWorkbook.Sheets("Sheet1").Cells(3, 1) = Cells(1, 2).Value
Next i
Každá slučka v tomto príklade získa vlastnosť Value bunky Cells(1,2). Ak priradíte vlastnosť Value Cells(1.2) premennej pred spustením cyklu, získate rýchlejší chod. Ako ukazuje nasledujúci príklad:
tmp = Cells(1, 2).Value
For i = 1 To 1000
ThisWorkbook.Sheets("Sheet1").Cells(1, 1) = tmp
ThisWorkbook.Sheets("Sheet1").Cells(2, 1) = tmp
ThisWorkbook.Sheets("Sheet1").Cells(3, 1) = tmp
Next i
Vyššie uvedený kód volá ThisWorkbook.Sheets("Sheet1") zakaždým, keď sa zacyklí. Môžete to urobiť rýchlejšie pomocou príkazu With na presun volania do ThisWorkbook.Sheets("Sheet1") mimo slučku. Ako ukazuje nasledujúci príklad:
tmp = Cells(1, 2).Value
With ThisWorkbook.Sheets("Sheet1")
For i = 1 To 1000
.Cells(1, 1) = tmp
.Cells(2, 1) = tmp
.Cells(3, 1) = tmp
Next i
End With
Vyhnite sa používaniu typov variantov
Začiatočníci zvyčajne uprednostňujú použitie premenných typu Variant, čo má tú výhodu, že je menej komplikované, pretože je možné použiť akýkoľvek typ údajov bez problému s pretečením pamäte, ak sú údaje príliš veľké pre typy údajov Integer alebo Long. Údaje typu Varienmt však vyžadujú viac miesta v pamäti ako ostatné špecifikované typy (2 bajty pre údaje typu Integer, 4 bajty pre údaje Long a 16 bajtov pre údaje typu Variant), VBA vyžaduje viac času na spracovanie údajov typu Variant ako iné špecifikované typy. údajov. Ako ukazuje nasledujúci príklad.
Sub VariantTest()
Dim i As Long
Dim ix As Integer, iy As Integer, iz As Integer
Dim vx As Variant, vy As Variant, vz As Variant
Dim tm As Date
vx = 100: vy = 50
tm = Timer
For i = 1 To 1000000
vz = vx * vy
vz = vx + vy
vz = vx - vy
vz = vx / vy
Next i
Debug.Print "Variant types take " & Format((Timer - tm), "0.00000") & " seconds"
ix = 100: iy = 50
tm = Timer
For i = 1 To 1000000
iz = ix * iy
iz = ix + iy
iz = ix - iy
iz = ix / iy
Next i
Debug.Print "Integer types take " & Format((Timer - tm), "0.00000") & " seconds"
End Sub
Vo vyššie uvedenom kóde riadky 8 až 13 vykonajú 1 milión operácií sčítania, odčítania, násobenia a delenia premenných Variant a riadky 17 až 22 vykonajú 1 milión operácií sčítania, odčítania, násobenia a delenia celočíselných premenných. Na mojom počítači operácia premennej Variant trvala približne 0,09375 sekundy, kým operácia premennej Integer približne 0,03125 sekundy. Výsledky sa môžu líšiť od počítača k počítaču, ale premenné Variant sú výrazne pomalšie ako premenné typu Integer .
Z tohto dôvodu sa odporúča vyhnúť sa používaniu premenných Variant, keď môžete explicitne použiť špecifikovaný typ údajov .