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.
Volgens Microsoft:
Laten we een simpel voorbeeld nemen: de inkomsten en uitgaven per maand en het resultaat daarvan. Zie het tabblad InUit in het 
kies in de menutab Invoegen in het blok Sparklines de optie Lijn
In dit geval moeten die achter de gegevens (dus in kolom O) komen; we vullen bij Locatiebereik O4:O6 in.
vink in het blok Weergeven de opties Hoge punt en Negatieve punten aan
selecteer alleen cel O4; kies binnen de optie Markeringskleur bij Hoge punt een groene kleur
de sparklines worden in Excel als achtergrond van een cel opgenomen. Dat betekent dat we nog steeds andere informatie in de cel kunnen opnemen, bijvoorbeeld toelichtende tekst.
Aan de bron zijn hiertoe lege regels voor Nov en Dec toegevoegd; zie tabblad Data in het 
in het vervolgvenster geven we het nieuwe item een Naam, bijvoorbeeld Res (Resultaat is al toegevoegd) en als Formule tikken we in: = In-Uit en klikken op Toevoegen en OK
Er is al veel over geschreven: Nederland is er niet bij!
O ja, België heeft volgens dit Excel-model trouwens ongeveer 10% kans om kampioen te worden!
Voor de indeling van deze 4 is een ingewikkeld schema bedacht (zie hiernaast; bron is WIKIPEDIA)).
Eén van de weinige woorden, die in de Nederlandstalige versie van Excel niet zijn vertaald, is Slicer.
Om het gebruik van een Slicer toe te lichten hebben we basisgegevens nodig aan de hand waarvan we een draaitabel kunnen maken.
De gegevens zijn opgeslagen in de vorm van een tabel met behulp van de optie Invoegen/Tabel. Excel kent daarbij automatisch de naam Tabel1 aan dit gebied toe.
NB1 een ander voordeel van een tabel merk je, wanneer je in het bestand naar beneden scrolt: zonder dat er titels geblokkeerd hoeven te worden, blijven de kopjes van de tabel zichtbaar.
plaats de cursor ergens in de tabel met basisgegevens, bijvoorbeeld door in cel B4 te klikken
er opent zich een nieuw werkblad met rechts een overzicht van alle velden/kolommen uit de geselecteerde tabel.
“Sneller dan het geluid” zien we dat de verdeling van de omzet in dit geval redelijk evenredig over de regio’s en jaren heeft plaats gevonden (niet vreemd natuurlijk omdat we de gegevens aselect hebben gegenereerd!).
Om een filtering naar Jaar en/of Regio mogelijk te maken zijn die 2 velden in het blok Rapportfilter geplaatst. Deze 2 velden zijn daardoor boven de draaitabel terecht gekomen; door middel van de vinkjes in de cellen B1 en B2 kan dan een jaar of regio geselecteerd worden.
via het tabblad Ontwerpen in Hulpmiddelen voor Draaitabellen hebben we binnen Rapportindeling de Tabelweergave gekozen.
Eén van de nadelen van een standaard-draaitabel is dat de kolommen nogal breed worden doordat Excel namen genereert als Som van Omzet etc.
.
Op internet heb ik echter een UDF (User Definied Function) gevonden op de site 
) de formule: =Draai4!$B$7 of tik in = en klik met de muis op de betreffende cel
Vorige week kreeg ik de vraag, hoe je met Excel het makkelijkst de winnaars van een wedstrijd zou kunnen selecteren.
In het 
Op het tabblad Uitslag worden 3 winnaars geselecteerd:
De formule in C2 ziet er anders uit:
In het tabblad Loterij3 van het 
De bepaling van de winnaars gaat nu iets anders: in cel C3 wordt de eerste winnaar bepaald door het grootste getal (MAX) in de kolom Aselect van de tabel LoterijOvz3 op te zoeken. In D3 wordt met VERT.ZOEKEN het corresponderende lotnummer gevonden en in E3 met INDEX de naam (zoals uit de formules van kolom F blijkt kunnen we dat laatste ook met VERT.ZOEKEN in de hele tabel LoterijOvz3).