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!
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 !!!