10 Manieren om uw macro's sneller te maken - dummies

Terwijl uw Excel-macro's steeds robuuster en complexer worden, zult u merken dat ze prestaties verliezen. Bij het bespreken van macro's is het woord optreden meestal synoniem met snelheid . Snelheid is hoe snel uw VBA-procedures hun beoogde taken uitvoeren. Hieronder volgen tien manieren om uw Excel-macro's op hun optimale prestatieniveau te houden.

Berekeningen met stilstaande vellen

Wist u dat elke keer dat een cel die van invloed is op een formule in uw spreadsheet wordt gewijzigd of gemanipuleerd, Excel het volledige werkblad opnieuw berekent? In werkbladen met een groot aantal formules kan dit gedrag uw macro's drastisch vertragen.

U kunt de applicatie gebruiken. Berekeningseigenschap om Excel te laten overschakelen naar handmatige berekeningsmodus. Wanneer een werkmap in de handmatige berekeningsmodus is, zal de werkmap niet opnieuw worden berekend totdat u expliciet een berekening activeert door op de F9-toets te drukken.

Plaats Excel in de handmatige berekeningsmodus, voer uw code uit en schakel vervolgens terug naar de automatische berekeningsmodus.

Submacro1 () applicatie. Berekening = xlCalculationManual 'Plaats hier uw macrocode Toepassing. Berekening = xlCalculationAutomatic End Sub

De berekeningsmodus opnieuw instellen op xlCalculationAutomatic activeert automatisch een herberekening van het werkblad, dus het is niet nodig om op de F9-toets te drukken nadat uw macro is uitgevoerd.

Werkbladupdate uitschakelen

U zult merken dat uw scherm tijdens het uitvoeren van uw macro's behoorlijk flikkert. Deze flikkering is Excel en probeert het scherm opnieuw te tekenen om de huidige status van het werkblad te tonen. Helaas neemt Excel elke keer dat het scherm opnieuw wordt getekend, geheugenbronnen in beslag.

U kunt de applicatie gebruiken. ScreenUpdating-eigenschap om schermupdates uit te schakelen totdat uw macro is voltooid. Als u het bijwerken van het scherm uitschakelt, bespaart u tijd en bronnen, waardoor uw macro iets sneller werkt. Nadat uw macrocode is voltooid, kunt u de schermupdate weer inschakelen.

Submacro1 () applicatie. Berekening = xlCalculationManual Application. ScreenUpdating = False 'Plaats hier uw macrocode Toepassing. Berekening = xlCalculationAutomatic Application. ScreenUpdating = True End Sub

Nadat u de eigenschap ScreenUpdating opnieuw hebt ingesteld op True, start Excel automatisch een nieuwe versie van het scherm.

Statusbalkupdates uitschakelen

De Excel-statusbalk, die onder in het Excel-venster wordt weergegeven, geeft normaal de voortgang van bepaalde acties in Excel weer.Als uw macro met veel gegevens werkt, neemt de statusbalk enkele bronnen in beslag.

Het is belangrijk om op te merken dat het uitschakelen van de schermbijwerking gescheiden is van het uitschakelen van de statusbalkweergave. De statusbalk blijft up-to-date, zelfs als u het updaten van het scherm uitschakelt. U kunt de applicatie gebruiken. DisplayStatusBar-eigenschap om updates van de statusbalk tijdelijk uit te schakelen, waardoor de prestaties van uw macro verder worden verbeterd:

Submacro1 () -toepassing. Berekening = xlCalculationManual Application. ScreenUpdating = False Application. DisplayStatusBar = False 'Plaats hier uw macrocode Toepassing. Berekening = xlCalculationAutomatic Application. ScreenUpdating = True Application. DisplayStatusBar = True End Sub

Excel laten zien om gebeurtenissen te negeren

U kunt macro's implementeren als gebeurtenisprocedures en Excel laten weten dat bepaalde code moet worden uitgevoerd wanneer een werkblad of werkmap wordt gewijzigd.

Soms maken standaardmacro's wijzigingen die een gebeurtenisprocedure activeren. Als u bijvoorbeeld een standaardmacro hebt die verschillende cellen in Sheet1 manipuleert, moet uw macro tijdens het uitvoeren van de gebeurtenis WorksheetChange elke keer dat een cel op dat blad wordt gewijzigd, pauzeren.

U kunt nog een niveau van prestatieverhoging toevoegen door de eigenschap EnableEvents te gebruiken om Excel te laten weten dat gebeurtenissen moeten worden genegeerd terwijl uw macro wordt uitgevoerd.

Stel de eigenschap EnableEvents in op False voordat u uw macro uitvoert. Nadat uw macrocode is voltooid, kunt u de eigenschap EnableEvents opnieuw instellen op Waar.

Submacro1 () applicatie. Berekening = xlCalculationManual Application. ScreenUpdating = False Application. DisplayStatusBar = False Application. EnableEvents = False 'Plaats hier uw macrocode Toepassing. Berekening = xlCalculationAutomatic Application. ScreenUpdating = True Application. DisplayStatusBar = True Application. EnableEvents = True End Sub

Pagina-einden verbergen

Telkens wanneer uw macro het aantal rijen wijzigt, het aantal kolommen wijzigt of de pagina-instelling van een werkblad wijzigt, wordt Excel gedwongen de tijd te nemen om de weergegeven pagina-einden opnieuw te berekenen op het blad.

U kunt dit voorkomen door de pagina-einden eenvoudigweg te verbergen voordat u uw macro start.

Stel de eigenschap DisplayPageBreaks in op False om pagina-einden te verbergen. Als u pagina-einden wilt blijven weergeven nadat uw macro is uitgevoerd, stelt u de eigenschap DisplayPageBreaks opnieuw in op True.

Submacro1 () applicatie. Berekening = xlCalculationManual Application. ScreenUpdating = False Application. DisplayStatusBar = False Application. EnableEvents = False Activesheet. DisplayPageBreaks = False 'Plaats hier uw macrocode Toepassing. Berekening = xlCalculationAutomatic Application. ScreenUpdating = True Application. DisplayStatusBar = True Application. EnableEvents = True Activesheet. DisplayPageBreaks = True End Sub

Updates voor draaitabel onderbreken

Als uw macro draaitabellen manipuleert die grote gegevensbronnen bevatten, kunt u slechte prestaties ondervinden bij zaken als dynamisch toevoegen of verplaatsen van draaivelden.

U kunt de prestaties van uw macro verbeteren door de herberekening van de draaitabel op te schorten totdat alle wijzigingen in het spilveld zijn aangebracht. Stel simpelweg de draaitabel in. ManualUpdate-eigenschap naar True om herberekening uit te stellen, voer uw macrocode uit en stel vervolgens de draaitabel in. ManualUpdate-eigenschap terug naar False om de herberekening te activeren.

Submacro1 () ActiveSheet. Draaitabellen ("PivotTable1"). ManualUpdate = True 'Plaats hier uw macrocode ActiveSheet. Draaitabellen ("PivotTable1"). ManualUpdate = False End Sub

Sturen zonder kopiëren en plakken

Het is belangrijk om te onthouden dat hoewel Macro Recorder tijd bespaart door VBA-code voor u te schrijven, het niet altijd de meest efficiënte code schrijft. Een goed voorbeeld is hoe Macro Recorder elke kopieer- en plakactie vastlegt die u tijdens het opnemen uitvoert.

Je kunt je macro's een lichte boost geven door de tussenpersoon uit te schakelen en een directe kopie van een cel naar een doelcel uit te voeren. Deze alternatieve code gebruikt het argument Bestemming om het klembord te omzeilen en de inhoud van cel A1 rechtstreeks naar cel B1 te kopiëren.

Range ("A1"). Kopieerbestemming: = Bereik ("B1")

Als u alleen waarden (niet formattering of formules) wilt kopiëren, kunt u de prestaties nog verbeteren door de methode Kopiëren allemaal samen te vermijden. Stel eenvoudig de waarde van de doelcel in op dezelfde waarde als gevonden in de broncel. Deze methode is ongeveer 25 keer sneller dan de methode Kopiëren:

Bereik ("B1"). Waarde = bereik ("A1"). Waarde

Als u alleen formules van de ene cel naar de andere wilt kopiëren (geen waarden of opmaak), kunt u de formule van de doelcel instellen op dezelfde formule in de broncel:

Bereik ("B1")). Formule = Bereik ("A1"). Formule

De With-instructie

gebruiken Bij het opnemen van macro's manipuleert u vaak hetzelfde object meerdere keren. U kunt tijd besparen en de prestaties verbeteren door de With -instructie te gebruiken om verschillende acties op een bepaald object in één keer uit te voeren.

De With-instructie die in het volgende voorbeeld wordt gebruikt, vertelt Excel om alle opmaakwijzigingen in één keer toe te passen:

Met bereik ("A1"). Lettertype. Vet = waar. Cursief = waar. Onderstrepen = xlUnderlineStyleEindelijk beëindigen met

Gewend raken aan chunking-acties Met met statements blijven niet alleen uw macro's sneller werken, maar wordt het ook eenvoudiger om uw macrocode te lezen.

De methode Select vermijden

Macro Recorder maakt graag gebruik van de methode Selecteren om objecten expliciet te selecteren voordat er acties op worden uitgevoerd. Het is over het algemeen niet nodig objecten te selecteren voordat u ermee werkt. In feite kunt u de macroprestaties drastisch verbeteren door de Select-methode niet te gebruiken.

Nadat u uw macro's hebt vastgelegd, maakt u er een gewoonte van om de gegenereerde code te wijzigen om de selectiemethoden te verwijderen. In dit geval ziet de geoptimaliseerde code er als volgt uit:

Sheets ("Sheet1"). Range ("A1"). FormulaR1C1 = "1000" -bladen ("Blad2"). Range ("A1"). FormulaR1C1 = "1000" -bladen ("Sheet3"). Range ("A1"). FormulaR1C1 = "1000"

Merk op dat het niets wordt geselecteerd.De code gebruikt eenvoudigweg de objecthiërarchie om de benodigde acties toe te passen.

Reizen naar het werkblad beperken

Een andere manier om uw macro's te versnellen, is om het aantal keer dat u verwijst naar werkbladgegevens in uw code te beperken. Het is altijd minder efficiënt om gegevens uit het werkblad te halen dan uit het geheugen. Dat wil zeggen dat uw macro's veel sneller zullen werken als ze niet herhaaldelijk hoeven te werken met het werkblad.

Bijvoorbeeld, de volgende eenvoudige code dwingt VBA om voortdurend terug te keren naar Spreadsheets ("Sheet1"). Bereik ("A1") om het nummer te krijgen dat nodig is voor de vergelijking die wordt uitgevoerd in de If-instructie:

For ReportMonth = 1 To 12 If Range ("A1"). Value = ReportMonth Then MsgBox 1000000 / ReportMonth End If Next ReportMonth

Een veel efficiëntere methode is om de waarde op te slaan in Sheets ("Sheet1"). Bereik ("A1") tot een variabele genaamd MyMonth. Op deze manier verwijst de code naar de MyMonth-variabele in plaats van het werkblad:

Dim MyMonth als Integer MyMonth = Range ("A1"). Waarde voor ReportMonth = 1 tot 12 If MyMonth = ReportMonth Then MsgBox 1000000 / ReportMonth End If Next ReportMonth

Overweeg om variabelen te gebruiken om met gegevens in het geheugen te werken in plaats van rechtstreeks naar werkbladen te verwijzen.