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.

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!