Optimaliseer de snelheid van je Excel macro’s (VBA)

Geplaatst in VBA door Exhelp | Tags: , , , , , , , , , , , ,
Van toepassing op: Excel 2003, Excel 2007, Excel 2010, Excel 2013

Des te langer je code, des te meer tijd een macro nodig heeft om succesvol te eindigen, daar kan je niet buiten. Wat je wel kan doen is je code optimaliseren. Er zijn namelijk heel wat factoren die de snelheid kunnen beïnvloeden.

20130129_speedup

1. Overbodige functies uitschakelen

Dit is heel eenvoudig toe te passen, zelf bij bestaande macro’s. Excel heeft heel wat tijd nodig bij het berekenen van formules, het updaten van het scherm, het weergeven van waarschuwingen, etc…

Je kan deze zaken tijdelijk uitschakelen, zodat Excel geen overbodige handelingen uitvoert.


With Application

    .Calculation = xlCalculationManual
    .ScreenUpdating = False
    .DisplayAlerts = False

End With

Onderaan je code zet je dan deze lijnen om alles terug aan te zetten. Bij het verwijderen van rijen hebben we gezien dat deze lijntjes code ons veel tijd kunnen besparen.


With Application

    .Calculation = xlCalculationAutomatic
    .ScreenUpdating = True
    .DisplayAlerts = True

End With

2. Selecteer geen cellen of objecten

Vaak laten we onze macro cellen of objecten selecteren alvorens hierop een handeling uit te voeren. Dit komt omdat de Macro Recorder de code zo produceert. Dit is echter niet nodig, dus door dit te vermijden werkt je macro sneller. Bovendien wordt je code korter en overzichtelijker.

Range("A1").value = "www.exhelp.be"

is véél sneller dan

Range("A1").select
Selection.Value = "www.exhelp.be"

3. Pas op met lussen (loops)

Lussen kan je best zoveel mogelijk vermijden. Lees: als je het op een andere manier kan programmeren; doe het dan.  Als het dan écht niet anders kan; ga er dan verstandig mee om.

Enkele voorbeelden:

Zoeken

Maak gebruik van de ingebouwde VBA functies ‘search’ of  ‘find’, gebruik de ingebouwd werkbladfuncties VERT.ZOEKEN (VLOOKUP) of MATCH (vergelijken)

Kopiëren

Als je een bereik wil kopiëren naar een ander bereik, wijs dan de waarden gewoon toe aan het nieuwe bereik zonder een lus. Vb:

Range("R2").value = Range("R1").value

Sorteren

Gebruik indien mogelijk Range.sort of een andere ingebouwde functie om een bereik te sorteren. Vb:

Activeworkbook.Sheets(1).Sort.SortFields.Add Key:= _
    Range("A2:A50"), _
    SortOn:=xlSortOnValues, _
    Order:=xlAscending, _
    DataOption:=xlSortNormal

Geneste lussen

Vermijd deze echt zoveel mogelijk. Een lus van 1.000 in een lus van 1.000 veroorzaakt 1.000.000 bewerkingen.

4. Gebruik zoveel mogelijk ingebouwde formules en functies.

Wanneer er een ingebouwde formule of functie bestaat, gebruik deze dan in plaats van zelf iets te ontwikkelen. Het heeft geen zin om het warme water opnieuw uit te vinden.

  • Objecten zoals “Application” hebben veel handige mogelijkheden.
  • Voor complexe berekeningen kan je ‘Draaitabellen’ gebruiken.

5. Gebruik ‘With’

Als je meerdere bewerkingen op het zelfde object wil doen, gebruik dan ‘With’. Het houdt je code netjes en zal ervoor zorgen dat Excel het object in cache houdt voor de verschillende bewerkingen.

With .Borders(xlEdgeLeft)

    .LineStyle = xlContinuous
    .Weight = xlMedium
    .ColorIndex = xlAutomatic

End With

6. Verdeel je code

Je macro kan op den duur ook traag worden doordat je alles in één beweging gaat uitvoeren. Probeer je code op te splitsen in meerdere modules en meerdere macro’s.

7. Communicatie met de gebruiker

Een macro kan soms traag aanvoelen doordat het een lange bewerking is. De gebruiker zit te wachten zonder te weten wanneer het resultaat er zal zijn. Enkele ideeën:

Hou je gebruikers op de hoogte met berichten in de statusbalk

Application.StatusBar = "Bezig met openen van www.exhelp.be"
' Plaats hier je code
Application.StatusBar = False

Laat het scherm af en toe updaten, zodat de vooruitgang zichtbaar is voor de gebruiker

With Application

    .ScreenUpdating = True
    .ScreenUpdating = False

End With

Laat de belangrijkste bewerkingen eerst gebeuren, en doe de rest op de achtergrond

8. Hou je code zo kort mogelijk

Elke regel overbodige code is een regel te veel! Doe de test: neem je meest complexe macro erbij, en zoek naar een lijn die je kan verwijderen. Herhaal dit tot je er geen meer vind. Enkel dit al versnelt je macro. Enkele bijkomende tips:

  • Denk goed na over je code voordat je eraan begint, maak een (stappen)plan.
  • Schrijf geen code voor ‘luie’ gebruikers, tenzij je ontwikkelt voor een groot publiek. Je code hoeft niet tot in de puntjes afgewerkt te zijn.  In een zakelijke omgeving zijn je gebruikers meestal slim en redelijk genoeg, zodat je bv. geen uitgebreide “error handling” moet programmeren.
  • Onderhandel met je gebruikers en verwijderen tijdvretende functionaliteiten indien mogelijk.
  • Gebruik ‘With’ om je code korter te maken

Conclusie

Er bestaan duizend en één manieren en tips om je macro te versnellen.  Ga ook niet te ver! Wanneer de snelheid van je macro acceptabel is, blijf dan niet oneindig zoeken naar verbeteringen.

Heb je zelf nog tips? Bezorg ze me gerust!

 

2 reacties op “Optimaliseer de snelheid van je Excel macro’s (VBA)”

  • Als je Worksheet – of WorkbookEvents hebt dan EnableEvents uitschakelen en na de uitvoering v/d code terug inschakelen. Zo voorkom je in oneindige loops terecht te komen.

    Bij grote aantallen gegevens laat je code zoveel mogelijk in het werkgeheugen uitvoeren ipv op het werkblad door gebruik te maken van arrays. Dit kan de uitvoeringssnelheid met 50% en meer verhogen.

  • inderdaad, zoals warme bakkertje aangeeft, zoveel mogelijk in het werkgeheugen doen met arrays, maar dictionaries zijn ook heel handige dingen.
    Snelheid gaat niet met 50% omhoog, ze verveelvoudigt !!!

Plaats een reactie

U moet ingelogd zijn om te reageren.

Login

Forums

Exhelp

Meest recente berichten

TAGS



Creative Commons Licentie
Op dit werk is een Creative
Commons Licentie
van toepassing.