In deze zelfstudie wordt besproken hoe u VBA-macro's en andere VBA-best practices kunt versnellen.
Instellingen om VBA-code te versnellen
Hieronder vind je enkele tips om je VBA-code te versnellen. De tips zijn losjes geordend op belangrijkheid.
De eenvoudigste manier om de snelheid van uw VBA-code te verbeteren, is door ScreenUpdating uit te schakelen en Automatische berekeningen uit te schakelen. Deze instellingen moeten in alle grote procedures worden uitgeschakeld.
Scherm bijwerken uitschakelen
Excel zal standaard wijzigingen in werkmappen in realtime weergeven terwijl VBA-code wordt uitgevoerd. Dit veroorzaakt een enorme vertraging van de verwerkingssnelheid, aangezien Excel de meeste wijzigingen voor elke regel code interpreteert en weergeeft.
Scherm bijwerken uitschakelen:
| 1 | Application.ScreenUpdating = False |
Aan het einde van uw macro moet u Scherm bijwerken weer inschakelen:
| 1 | Application.ScreenUpdating = True |
Terwijl uw code actief is, moet u mogelijk het scherm "verversen". Er is geen "vernieuwen" commando. In plaats daarvan moet u Scherm bijwerken weer inschakelen en weer uitschakelen.
Berekeningen instellen op Handmatig
Telkens wanneer een celwaarde wordt gewijzigd, moet Excel de "berekeningsboom" volgen om alle afhankelijke cellen opnieuw te berekenen. Bovendien moet Excel, wanneer een formule wordt gewijzigd, de "berekeningsboom" bijwerken en alle afhankelijke cellen opnieuw berekenen. Afhankelijk van de grootte van uw werkmap, kunnen deze herberekeningen ertoe leiden dat uw macro's onredelijk traag worden uitgevoerd.
Berekeningen instellen op Handmatig:
| 1 | Toepassing. Berekening = xlHandmatig |
De hele werkmap handmatig opnieuw berekenen:
| 1 | Berekenen |
Merk op dat u ook alleen een blad, bereik of individuele cel kunt berekenen, indien nodig voor een betere snelheid.
Automatische berekeningen herstellen (aan het einde van uw procedure):
| 1 | Toepassing.Berekening = xlAutomatisch |
Belangrijk! Dit is een Excel-instelling. Als u berekeningen niet opnieuw instelt op automatisch, wordt uw werkmap pas opnieuw berekend als u dit aangeeft.
Je zult de grootste verbeteringen van de bovenstaande instellingen zien, maar er zijn verschillende andere instellingen die een verschil kunnen maken:
Evenementen uitschakelen
Gebeurtenissen zijn "triggers" die speciale gebeurtenis procedures rennen. Voorbeelden zijn: wanneer een cel op een werkblad verandert, wanneer een werkblad wordt geactiveerd, wanneer een werkmap wordt geopend, voordat een werkmap wordt opgeslagen, enz.
Het uitschakelen van gebeurtenissen kan kleine snelheidsverbeteringen veroorzaken wanneer macro's worden uitgevoerd, maar de snelheidsverbetering kan veel groter zijn als uw werkmap gebeurtenissen gebruikt. En in sommige gevallen is het uitschakelen van gebeurtenissen nodig om te voorkomen dat er eindeloze lussen ontstaan.
Gebeurtenissen uitschakelen:
| 1 | Application.EnableEvents = False |
Gebeurtenissen weer inschakelen:
| 1 | Application.EnableEvents = True |
Pagina-onderbrekingen uitschakelen
Het uitschakelen van PageBreaks kan in bepaalde situaties helpen:
- U hebt eerder een PageSetup-eigenschap ingesteld voor het relevante werkblad en uw VBA-procedure wijzigt de eigenschappen van veel rijen of kolommen
- OF Uw VBA-procedure dwingt Excel om pagina-einden te berekenen (afdrukvoorbeeld weergeven of eigenschappen van PageSetup wijzigen).
PageBreaks uitschakelen:
| 1 | ActiveSheet.DisplayPageBreaks = False |
PageBreaks opnieuw inschakelen:
| 1 | ActiveSheet.DisplayPageBreaks = True |
Best practices om de VBA-snelheid te verbeteren
Vermijd activeren en selecteren
Wanneer u een macro opneemt, ziet u veel activerings- en selectiemethoden:
| 12345678 | Sub Slow_Example()Bladen ("Blad2"). SelecteerBereik ("D9"). SelecteerActiveCell.FormulaR1C1 = "voorbeeld"Bereik ("D12"). SelecteerActiveCell.FormulaR1C1 = "demo"Bereik ("D13"). SelecteerEinde sub |
Het activeren en selecteren van objecten is meestal niet nodig, ze maken je code rommelig en ze kosten veel tijd. U moet deze methoden waar mogelijk vermijden.
Verbeterd voorbeeld:
| 1234 | Sub Fast_Example()Spreadsheets ("Blad2").Bereik ("D9").FormulaR1C1 = "voorbeeld"Spreadsheets ("Blad2").Bereik ("D12").FormuleR1C1 = "demo"Einde sub |
Vermijd kopiëren en plakken
Kopiëren vereist veel geheugen. Helaas kun je VBA niet vertellen om het interne geheugen te wissen. In plaats daarvan zal Excel het interne geheugen wissen met (schijnbaar) specifieke intervallen. Dus als u veel kopieer- en plakbewerkingen uitvoert, loopt u het risico te veel geheugen in beslag te nemen, wat uw code drastisch kan vertragen of zelfs Excel kan laten crashen.
Overweeg om de waarde-eigenschappen van cellen in te stellen in plaats van te kopiëren en te plakken.
| 123456789 | Sub CopyPaste()'Langzamer'Bereik ("a1:a1000"). Bereik kopiëren ("b1:b1000")'Sneller'Bereik ("b1: b1000"). Waarde = Bereik ("a1: a1000"). WaardeEinde sub |
Gebruik de For Each-loops in plaats van For Loops
Bij het doorlopen van objecten is de For Each-lus sneller dan de For-lus. Voorbeeld:
Dit voor lus:
| 123456 | Sublus1()dim i als bereikVoor i = 1 tot 100Cellen (i, 1). Waarde = 1volgende iEinde sub |
| 123456 | Sublus2()Dim cel als bereikVoor elke cel binnen bereik ("a1:a100")cel.Waarde = 1Volgende celEinde sub |
Variabelen declareren / optie expliciet gebruiken
VBA vereist niet dat u uw variabelen declareert, tenzij u Option Explicit bovenaan uw module toevoegt:| 1 | Optie expliciet |
| 1234 | SuboptieExplicit()var1 = 10MsgBox varlEinde sub |
Gebruik met - Eindig met instructies
Als u meerdere keren naar dezelfde objecten verwijst (bijv. Bereiken, Werkbladen, Werkmappen), kunt u overwegen de Met-instructie te gebruiken. Het is sneller te verwerken, kan uw code leesbaarder maken en vereenvoudigt uw code.Met verklaring voorbeeld:| 12345678 | Sub Faster_Example()Met bladen ("Blad2").Bereik ("D9").FormuleR1C1 = "voorbeeld".Bereik ("D12").FormuleR1C1 = "demo".Bereik("D9").Lettertype.Bold = True.Bereik ("D12"). Lettertype. Vet = TrueEindigt metEinde sub |
| 123456 | Sub Slow_Example()Spreadsheets ("Blad2").Bereik ("D9").FormulaR1C1 = "voorbeeld"Spreadsheets ("Blad2").Bereik ("D12").FormuleR1C1 = "demo"Bladen ("Blad2"). Bereik ("D9"). Lettertype. Vet = TrueBladen ("Blad2"). Bereik ("D12"). Lettertype. Vet = TrueEinde sub |
Geavanceerde praktische tips
Alleen gebruikersinterface beveiligen
Het is een goede gewoonte om uw werkbladen te beschermen tegen het bewerken van onbeveiligde cellen om te voorkomen dat de eindgebruiker (of u!) Per ongeluk de werkmap beschadigt. Dit zal echter ook de werkblad (en) beschermen tegen VBA om wijzigingen aan te brengen. U moet de beveiliging van werkbladen dus opheffen en opnieuw beveiligen, wat erg tijdrovend is wanneer u dit op veel vellen doet.
| 12345 | Sub UnProtectSheet()Spreadsheets ("sheet1"). Beveiliging van "wachtwoord" opheffen'Blad1 bewerken'Spreadsheets ("sheet1"). Bescherm "wachtwoord"Einde sub |
In plaats daarvan kunt u werkbladen beveiligen met de instelling UserInterfaceOnly:=True. Hierdoor kan VBA wijzigingen aanbrengen in bladen, terwijl ze nog steeds worden beschermd tegen de gebruiker.
| 1 | Spreadsheets ("sheet1").Bescherm wachtwoord:="wachtwoord", UserInterFaceOnly:=True |
Belangrijk! UserInterFaceOnly wordt elke keer dat de werkmap wordt geopend, opnieuw ingesteld op False. Dus om deze geweldige functie te gebruiken, moet u de gebeurtenissen Workbook_Open of Auto_Open gebruiken om de instelling in te stellen telkens wanneer de werkmap wordt geopend.
Plaats deze code in de module Thisworkbook:
| 123456 | Privé subwerkboek_Open()Dim ws als werkbladVoor elke ws In werkbladenws.Protect Password:="wachtwoord", UserInterFaceOnly:=TrueVolgende wsEinde sub |
of deze code in een reguliere module:
| 123456 | Privé Sub Auto_Open()Dim ws als werkbladVoor elke ws In werkbladenws.Protect Password:="wachtwoord", UserInterFaceOnly:=TrueVolgende wsEinde sub |
Arrays gebruiken om grote bereiken te bewerken
Het kan erg tijdrovend zijn om grote reeksen cellen te manipuleren (bijv. 100.000+). In plaats van door celbereiken te bladeren en elke cel te manipuleren, kunt u de cellen in een array laden, elk item in de array verwerken en de array vervolgens terug naar hun oorspronkelijke cellen uitvoeren. Het laden van de cellen in arrays voor manipulatie kan veel sneller zijn.
| 1234567891011121314151617181920212223242526272829303132 | Sub LoopBereik()Dim cel als bereikDim tStart als dubbeltStart = TimerVoor elke cel binnen bereik ("A1:A100000")cel.Waarde = cel.Waarde * 100Volgende celDebug.Print (Timer - tStart) & "seconden"Einde subSub LoopArray()Dim arr As VariantDim item als variantDim tStart als dubbeltStart = Timerarr = Bereik ("A1:A100000"). WaardeVoor elk item In aankomstartikel = artikel * 100Volgende itemBereik ("A1:A100000"). Waarde = arrDebug.Print (Timer - tStart) & "seconden"Einde sub |
