Denna handledning kommer att diskutera hur du påskyndar VBA -makron och andra bästa metoder för VBA.
Inställningar för att påskynda VBA -koden
Nedan hittar du flera tips för att påskynda din VBA -kod. Tipsen är löst organiserade efter vikt.
Det enklaste sättet att förbättra hastigheten på din VBA -kod är genom att inaktivera ScreenUpdating och inaktivera automatiska beräkningar. Dessa inställningar bör inaktiveras i alla stora procedurer.
Inaktivera skärmuppdatering
Som standard kommer Excel att visa ändringar i arbetsboken i realtid när VBA-koden körs. Detta orsakar en massiv avmattning i bearbetningshastigheten eftersom Excel mest tolkar och visar ändringar för varje kodrad.
Så här stänger du av skärmuppdatering:
| 1 | Application.ScreenUpdating = Falskt |
I slutet av ditt makro bör du slå på skärmuppdatering igen:
| 1 | Application.ScreenUpdating = True |
Medan koden körs kan du behöva "uppdatera" skärmen. Det finns inget "uppdatera" -kommando. Istället måste du slå på skärmuppdatering igen och inaktivera det igen.
Ställ in beräkningar på Manuell
När ett cellvärde ändras måste Excel följa "beräkningsträdet" för att beräkna alla beroende celler. Dessutom, när en formel ändras måste Excel uppdatera "beräkningsträdet" förutom att beräkna alla beroende celler. Beroende på din arbetsbokstorlek kan dessa omräkningar få dina makron att gå orimligt långsamt.
Så här ställer du in beräkningar på Manuell:
| 1 | Application.Calculation = xlManual |
Så här beräknar du hela arbetsboken manuellt:
| 1 | Beräkna |
Observera att du också kan beräkna endast ett ark, område eller enskild cell, om det behövs för förbättrad hastighet.
Så här återställer du automatiska beräkningar (i slutet av proceduren):
| 1 | Application.Calculation = xlAutomatic |
Viktig! Detta är en Excel -inställning. Om du inte återställer beräkningarna till automatisk kommer din arbetsbok inte att räkna om förrän du säger till den.
Du kommer att se de största förbättringarna från ovanstående inställningar, men det finns flera andra inställningar som kan göra skillnad:
Inaktivera händelser
Händelser är ”triggers” som orsakar speciella händelseförfaranden att springa. Exempel inkluderar: när någon cell i ett kalkylblad ändras, när ett kalkylblad aktiveras, när en arbetsbok öppnas, innan en arbetsbok sparas osv.
Inaktivering av händelser kan orsaka mindre hastighetsförbättringar när några makron körs, men hastighetsförbättringen kan vara mycket större om din arbetsbok använder händelser. Och i vissa fall är det nödvändigt att inaktivera händelser för att undvika att skapa oändliga loopar.
Så här inaktiverar du händelser:
| 1 | Application.EnableEvents = Falskt |
Så här aktiverar du händelser igen:
| 1 | Application.EnableEvents = True |
Inaktivera sidbrytningar
Inaktivera PageBreaks kan hjälpa i vissa situationer:
- Du har tidigare ställt in en PageSetup -egenskap för det relevanta kalkylbladet och ditt VBA -förfarande ändrar egenskaperna för många rader eller kolumner
- ELLER Din VBA -procedur tvingar Excel att beräkna sidbrytningar (visar förhandsgranskning av utskrift eller ändrar egenskaper för PageSetup).
Så här inaktiverar du sidbrytningar:
| 1 | ActiveSheet.DisplayPageBreaks = Falskt |
Så här återaktiverar du sidbrytningar:
| 1 | ActiveSheet.DisplayPageBreaks = True |
Bästa praxis för att förbättra VBA -hastigheten
Undvik att aktivera och välja
När du spelar in ett makro ser du många aktiverings- och markeringsmetoder:
| 12345678 | Sub Slow_Example ()Ark ("Ark2"). VäljOmråde ("D9"). VäljActiveCell.FormulaR1C1 = "exempel"Område ("D12"). VäljActiveCell.FormulaR1C1 = "demo"Område ("D13"). VäljAvsluta Sub |
Att aktivera och välja objekt är vanligtvis onödigt, de lägger till röror i din kod och de är mycket tidskrävande. Du bör undvika dessa metoder när det är möjligt.
Förbättrat exempel:
| 1234 | Sub Fast_Example ()Ark ("Ark2"). Område ("D9"). FormulaR1C1 = "exempel"Sheets ("Sheet2"). Range ("D12"). FormulaR1C1 = "demo"Avsluta Sub |
Undvik att kopiera och klistra in
Kopiering kräver betydande minne. Tyvärr kan du inte säga till VBA att rensa det interna minnet. Istället rensar Excel det interna minnet med (till synes) specifika intervall. Så om du utför många kopierings- och klistraoperationer riskerar du att hugga för mycket minne, vilket drastiskt kan sakta ner din kod eller till och med krascha Excel.
Istället för att kopiera och klistra in, överväg att ställa in värdena för celler.
| 123456789 | Sub CopyPaste ()'LångsammareOmråde ("a1: a1000"). Kopieringsintervall ("b1: b1000")'SnabbareOmråde ("b1: b1000"). Värde = intervall ("a1: a1000"). VärdeAvsluta Sub |
Använd För varje slingor istället för För slingor
När du slingrar genom objekt är For each loop snabbare än For Loop. Exempel:
Detta för loop:
| 123456 | Sub Loop1 ()dim i som RangeFör i = 1 till 100Celler (i, 1). Värde = 1Nästa iAvsluta Sub |
| 123456 | Sub Loop2 ()Dim cell As RangeFör varje cell i intervallet ("a1: a100")cell.Värde = 1Nästa cellAvsluta Sub |
Deklarera variabler / använd alternativet Explicit
VBA kräver inte att du deklarerar dina variabler, såvida du inte lägger till Alternativ Explicit högst upp i din modul:| 1 | Alternativ Explicit |
| 1234 | Sub OptionExplicit ()var1 = 10MsgBox varlAvsluta Sub |
Använd med - slut med uttalanden
Om du refererar till samma objekt flera gånger (t.ex. intervall, kalkylblad, arbetsböcker), överväg att använda With -uttalandet. Det är snabbare att bearbeta, kan göra din kod lättare att läsa och förenkla din kod.Med uttalandexempel:| 12345678 | Sub Faster_Example ()Med ark ("Sheet2").Range ("D9"). FormulaR1C1 = "exempel".Range ("D12"). FormulaR1C1 = "demo".Range ("D9"). Font.Bold = True.Range ("D12"). Font.Bold = TrueSluta medAvsluta Sub |
| 123456 | Sub Slow_Example ()Ark ("Ark2"). Område ("D9"). FormulaR1C1 = "exempel"Sheets ("Sheet2"). Range ("D12"). FormulaR1C1 = "demo"Sheets ("Sheet2"). Range ("D9"). Font.Bold = TrueSheets ("Sheet2"). Range ("D12"). Font.Bold = TrueAvsluta Sub |
Avancerade tips för bästa praxis
Skydda endast UserInterface
Det är bra att skydda dina kalkylblad från att redigera oskyddade celler för att förhindra att slutanvändaren (eller du!) Råkar förstöra arbetsboken. Detta kommer dock också att skydda kalkylbladet från att tillåta VBA att göra ändringar. Så du måste avskydda och skydda kalkylblad, vilket är mycket tidskrävande när det görs på många ark.
| 12345 | Sub UnProtectSheet ()Kalkylark ("blad1"). Avskydda "lösenord"'Redigera blad 1Ark ("blad1"). Skydda "lösenord"Avsluta Sub |
Istället kan du skydda ark med inställningen UserInterfaceOnly: = True. Detta gör att VBA kan göra ändringar i ark, samtidigt som de skyddas från användaren.
| 1 | Sheets (“sheet1”). Skydda lösenord: = "lösenord", UserInterFaceOnly: = True |
Viktig! UserInterFaceOnly återställs till Falskt varje gång arbetsboken öppnas. Så för att använda denna fantastiska funktion måste du använda händelserna Workbook_Open eller Auto_Open för att ställa in inställningen varje gång arbetsboken öppnas.
Placera denna kod i Thisworkbook -modulen:
| 123456 | Private Sub Workbook_Open ()Dim ws Som arbetsbladFör varje ws i kalkylbladws.Protect Password: = "password", UserInterFaceOnly: = TrueNästa wsAvsluta Sub |
eller denna kod i någon vanlig modul:
| 123456 | Privat del Auto_Open ()Dim ws Som arbetsbladFör varje ws i kalkylbladws.Protect Password: = "password", UserInterFaceOnly: = TrueNästa wsAvsluta Sub |
Använd matriser för att redigera stora intervall
Det kan vara mycket tidskrävande att manipulera stora cellområden (Ex. 100 000+). Istället för att slinga genom cellområden och manipulera varje cell kan du ladda cellerna i en array, bearbeta varje objekt i arrayen och sedan mata ut arrayen tillbaka till sina ursprungliga celler. Ladda cellerna i matriser för manipulation kan vara mycket snabbare.
| 1234567891011121314151617181920212223242526272829303132 | Sub LoopRange ()Dim cell As RangeDim tStart As DoubletStart = TimerFör varje cell i intervallet ("A1: A100000")cell.Value = cell.Value * 100Nästa cellDebug.Print (Timer - tStart) & "sekunder"Avsluta SubSub LoopArray ()Dim arr Som variantDim objekt som variantDim tStart As DoubletStart = Timerarr = Område ("A1: A100000"). VärdeFör varje objekt i arrartikel = artikel * 100Nästa objektOmråde ("A1: A100000"). Värde = arrDebug.Print (Timer - tStart) & "sekunder"Avsluta Sub |
