VBA-routines versnellen



Wanneer u net begint met het ontwikkelen van VBA-routines om werkzaamheden te automatiseren, zult u versteld staan van de tijdwinst die dit kan opleveren.

Maar ….  als dan de hoeveelheid gegevens, waarmee u werkt, gaat groeien dan kan het zo maar zijn dan zo’n routine tientallen secondes bezig is en dat u denkt: kan dat niet sneller!

Het antwoord is (hoogst waarschijnlijk): JA, dat kan zeker sneller.

In dit artikel enkele tips voor efficiëntere VBA-routines.

Gebruik van Select

Wanneer u bepaalde handelingen in Excel wilt automatiseren, is de macro-recorder van VBA een uiterst handig hulpmiddel. U neemt de toetsaanslagen op en volgende keer kunt u met één druk op een knop diezelfde toetsaanslagen herhalen. Vaak wordt zo’n recorder-opname dan gebruikt als basis voor een uitgebreidere routine.

In dit voorbeeld geldt dat ook (zie Blad1 in het Voorbeeldbestand):

  • we hebben een tabel (met de naam Tabel1) met 2 kolommen: in de eerste staan maanden en in de tweede worden willekeurige bedragen met 2 decimalen gegenereerd, liggend tussen 1 en 2:
    =ASELECTTUSSEN(100;200)/100
  • het eerste bedrag (cel C3) heeft de naam Bedrag1 gekregen, terwijl het bereik van alle bedragen de naam Bedr heeft.
  •  in kolom F worden het aantal regels, het totaalbedrag en het gemiddelde bepaald. Het tweede gemiddelde is gelijk aan het  totaalbedrag gedeeld door het aantal; een simpel voorbeeld van een ingebouwde controle.
  • als voorbeeld heb ik een VBA-routine gemaakt (gebaseerd op een macro-opname), die ook dezelfde berekeningen uitvoert.
    Deze routine selecteert cel Bedrag1  en “loopt” zolang er geen lege cel is naar beneden; ondertussen wordt het aantal geturfd en een lopend totaal bepaald:
  • op het einde van de VBA-routine zorgen we dat de cursor weer in cel A1 komt en worden de resultaten via een pop-up zichtbaar gemaakt.
    Ook de verstreken tijd is in de pop-up opgenomen.
  • klik op de button Select in het Voorbeeldbestand om de VBA-routine uit te voeren.

NB1 door de rechte haken rond een naam weet VBA, dat het een naam in de Excel-sheet is, dus niet de naam van een VBA-variabele.

NB2 zo ziet een info-scherm op de MAC er uit; de PC-versie oogt anders.

NB3 mijn MAC heeft dus ruim 11 seconden nodig voor de 1.000 verplaatsingen en berekeningen! Op een PC duurt het (uiteraard afhankelijk van de soort machine) nog geen seconde.

NB4 wil je de gebruikte routine zien: klik Alt-F11 of ga via de menutab Ontwikkelaars naar Visual Basic.

Gebruik van Select-2

Het grootste gedeelte van de benodigde tijd van de vorige VBA-routine gaat zitten in het fysiek verplaatsen van de cursor (via Offset).

Met behulp van het VBA-commando Application.ScreenUpdating = False kunnen we er voor zorgen, dat het verversen van het scherm niet meer plaats vindt. Het resultaat is, dat de MAC binnen een halve seconde klaar is!

Klik op de button Select2 om de alternatieve VBA-routine uit te voeren.

NB vergeet niet op het einde van zo’n routine de scherm-verversing weer aan te zetten: Application.ScreenUpdating = True

Gebruik van Range

Veel Excel-ontwikkelaars vinden het gebruik van de Select-methode uit den boze. Bij een prima alternatief wordt het belangrijkste gedeelte van de routine:

De berekeningen worden uitgevoerd voor iedere cel in het bereik Bedr. De computer gaat nu in sneltrein-verhaal langs de inhoud van de cellen zonder dat er een verplaatsing van de cursor nodig is.

De MAC is nu binnen 1/100 van een seconde klaar! Test de routine door op de button Range te klikken.

Gebruik van een array

In het vorige voorbeeld hebben we gebruik gemaakt van een Excel-bereik (in dit geval met de naam Bedr).
Bij heel grote bestanden kan er tijdwinst geboekt worden door dit bereik eerst in een aparte array (rij, reeks, matrix) te plaatsen:

  • het bereik met de naam Bedr (de truc met de rechte haken werkt nu niet meer) wordt cel voor cel in een array aBedr gestopt
  • voor ieder element van de array wordt het lopende totaal bepaald (Ubound=UpperBound=bovengrens)

Sneller dan het geluid!? De routine heeft minder dan 1/10.000 seconde nodig.

Gebruik van Tabel

Een alternatief voor de vorige oplossing is:

  • de kolom Bedrag uit Tabel1 wordt in de array aBedr gestopt
  • de bovengrens van de array plaatsen we in de variabele N (in het vorige voorbeeld moet de bovengrens iedere keer opnieuw worden bepaald; niet efficiënt)

Gebruik van Tabel-2

In een variant van de vorige oplossing schrijven we de resultaten ook nog weg in de Excel-sheet (in cellen van kolom G met de namen Aantal, Totaal en Gem1_):

NB1 cel G5 heeft NIET de naam Gem1. Er bestaat al een cel met die naam, namelijk de cel in kolom GEM, rij 1. Dit wordt vaak opgelost door een underscore er voor of er achter te zetten.

NB2 doordat er cellen in de sheet worden aangepast gaat Excel alle formules doorrekenen; dus alle bedragen in Bedr veranderen NADAT de VBA-berekening is uitgevoerd.

Effect van groter bestand op de berekeningen

Door Tabel1 uit te breiden naar bijvoorbeeld 100.000 regels is het verschil in benodigde tijd beter te beoordelen. Trek daartoe de vulgreep van de tabel rechtsonder naar beneden.


 

Geef een reactie

Het e-mailadres wordt niet gepubliceerd. Vereiste velden zijn gemarkeerd met *