VBA in Excel is niet dood (5 stappen naar efficiënt proces)

VBA wordt wel onderhouden door Microsoft en dus verdwijnt VBA nooit uit Excel. Kortom ga gerust aan de slag met VBA. De meest aantrekkelijke kant van VBA is de macro recorder. Je leert hiermee spelenderwijs hoe je code schrijft. Maar dit artikel is bestemd voor Excel gebruikers, die al een beetje meer thuis zijn in deze programmeertaal en zelf code aanpassen.

Een van de belangrijkste zaken in VBA is de interactie tussen Excel en jouw code. Je wilt vaak jouw codegegevens uit een Excel werkblad naar VBA halen, vervolgens verricht de code enkele bewerkingen en uiteindelijk breng je het resultaat weer terug naar een Excel werkblad. 

Nu zie ik vaak dat gebruikers een rij per keer in VBA inlezen, de bewerkingen laat uitvoeren en vervolgens de aangepaste rij terugschrijven naar Excel. Dat betekent veel wisselen tussen VBA en Excel en dat kost waardevolle tijd. Dit artikel gaat over hoe je dit proces efficiënter uitvoert. We gebruiken hiervoor een aantal van Excel- en VBA bereik technieken(#1). 

De start positie is een Excel bereik (zie afbeelding 1). Wat je wilt is dat de hele tabel in het geheugen van VBA wordt ingelezen, dat er vervolgens bij de getallen uit kolom B het getal dat in cel D2 staat wordt opgeteld, en dat het resultaat daarna terug wordt geschreven naar een bereik dat begint vanaf cel F1. Let even op de naam in cel A1. De naam “PosBereik” is specifiek gebonden aan deze cel. Zo heeft cel D2 de naam “PosOptelGetal” en de cel F1 de naam “PosUitvoer” gekregen.

Stap 1 
Eerst open je VBA en stel je de omgeving in. Kies daarvoor de sneltoets ALT + F11: daarmee open je de VBA-editor. Om te beginnen heb je een module nodig. Klik in de menubalk op “Invoegen”, “Module”. Nu zie je een blanco module. Het eerste wat je typt zijn de omgevingsinstellingen: “Option explicit” en “Option Base 1” (zie afbeelding 2). De eerste regel zorgt ervoor dat je de variabelen altijd vooraf moet instellen, waardoor het per ongeluk verkeerd typen van een variabele naam wordt voorkomen. De tweede regel zorgt ervoor dat de tellingen in bijvoorbeeld een loop altijd beginnen met nummer 1, in plaats van de default waarde 0.

Stap 2
Nu dit is geregeld gaan we met het echte werk beginnen! Op een nieuwe regel typ je Public Sub BereikenBerekenen(). Zodra je de Entertoets hebt gebruikt, plaatst de VBA-editor zelf een eindregel, met de code End Sub. Tussen deze beide regels vindt de uitvoering van jouw code plaats.

De volgende stap is het binnenhalen van het eerste bereik. Heb je weleens een bereik geselecteerd in Excel? Dan weet je dat, als de actieve cel in het bereik staat, je met de CONTROL + * toetsenbord combinatie het gehele bereik selecteert. Doe je dit in de macro recorder, dan zie je de code, die je in afbeelding 3 ziet. 

Stap 3
Deze code ga je, in iets aangepaste vorm, gebruiken om in een keer het gehele bereik in een variabele te plaatsen. De variabele verandert daarmee in een keer in een matrix (array), zonder dat je deze als zodanig hoeft te declareren. Eerst ga je de variabele maken. Om je code netjes te houden, plaats je alle variabelen onder de regel Public sub. Type de volgende regel: Dim Bereik as Variant. Op de volgende regel voer je de volgende code in: Bereik = Range(“PosBereik”).CurrentRegion.Value. Met deze code plaats je het bereik, die is gekoppeld aan de cel met de naam “PosBereik” in de variabele. 

Een eenvoudiger versie van deze code gebruik je om de variabele, in de cel met de naam “PosOptelGetal” in de variabel “OptelWaarde” te plaatsen. Let even op de declaratie van de variabele: omdat het een nummer betreft gebruik je hier het datatype double. De code hiervoor zie je in afbeelding 4. Ik heb hierbij het venster “Lokale variabelen” geopend, zodat je ziet wat VBA in de beide variabelen plaatst. 

Stap 4
Nu wil je de waarde uit de tweede kolom optellen met de waarde in de variabele “OptelWaarde”. Hiervoor maak je een For loop, waarmee VBA eigenlijk door de waarde in de variabele “Bereik” ”wandelt”. Je hebt hiervoor een teller nodig. Die code zet je weer onder de andere twee variabelen “Dim i as Integer”. Nu kan je beginnen aan de loop. 

Die werkt als volgt: de eerste regel code luidt zo: “For i = 2 to Ubound(Bereik)”. Wat je hiermee zegt is dat de teller begint met de waarde 2 en zolang door gaat als er rijen zijn in de variabele. Waarom begint de loop bij de waarde 2? Omdat de eerste rij in de variabele de koprij bevat en de tekst daarvan niet aangepast kan en moet worden. Natuurlijk komt er een moment dat je wilt dat je naar de volgende rij wilt. Dat doe je door de volgende code “Next i´. 

In een rij tussen deze beide codes plaats je de volgende code: “Bereik(i,2) = Bereik(i,2) + OptelWaarde”. Kijk je naar het venster “Lokale variabelen”, dan zie je dat de variabelen nu de berekende waarde bevat (zie afbeelding 5). 

Stap 5
De inhoud van de variabele breng je nu terug naar Excel. Daar gebruik je de naam “PosUitvoer” voor. Het verschil met de invoer is dat je geen bereik hebt om te selecteren. Dus laat je eerst VBA het bereik selecteren. 

De volgende code zoekt de cel met de naam “Posuitvoer”, gebruik deze cel als linkerbovenhoek van het nieuw te vormen bereik, vergroot het aantal rijen met het aantal rijen in de variabele “Bereik” en het aantal kolommen met 2. Vervolgens dumpt het in één keer de inhoud van de variabele “Bereik” in de geselecteerde cellen. De code, die je ook ziet in afbeelding 6 luidt als volgt: “Range(“PosUitvoer”).resize(Ubound(Bereik),2).value = bereik”.

Terug in Excel kies je de sneltoets ALT + F8. In het venster Macro activeer je de macro BereikenBerekenen en het resultaat staat in op de gewenste positie (zie afbeelding 7). Natuurlijk is hier meer code nodig. Zo wil je, indien nodig, het uitvoerbereik eerst leeg maken. Wordt het invoer bereik namelijk kleiner, dan blijven rijen uit het verleden staan. Dat wil je natuurlijk niet. De uitwerking hiervan gaat echter te ver voor dit artikel. 

Wat zijn de voordelen van deze methode? 
– Doordat je een bereiknaam gebruikt, is een naam (of indexnummer) van een werkblad in VBA niet nodig. Verander je de naam van het werkblad of verplaats je de volgorde, dan werkt de macro nog steeds prima.
– Deze methode is veel sneller dan rij voor rij naar VBA te kopiëren, omdat je in een keer alle cellen in het bereik in de variabele plaatst.
– De verwerking van de berekeningen vindt plaats in het geheugen. Ook dat werkt het meest efficiënt. 
– Hetzelfde geldt voor het wegschrijven. Omdat VBA eerst het bereik selecteert en vervolgens in een keer alle waarden uit de variabelen in dat bereik dumpt, werkt het veel sneller dan dat je rij voor rij wegschrijft.
– Wordt het bereik in rijen uitgebreid, dan wordt het uitvoer bereik automatisch vergroot als je de macro opnieuw uitvoert.
– Hetzelfde geldt voor de variabele in cel D2. Pas je die waarde aan en voer je macro opnieuw uit, ook dan zal het resultaat bereik worden aangepast.

#1) Weet je niet wat een Excel bereik of een Excel naam is? In een volgend artikel ga ik nader op de definitie van een Excel bereik in.

Henk Vlootman geeft sinds 1994 Excel en Power BI trainingen aan managers over de gehele wereld. Vanaf 2013 ontving Henk jaarlijks de Microsoft MVP (Most Valuable Professional) award. In 2016 werd de MVP award voor de gebieden Excel en Data platform toegekend. Die laatste erkenning kreeg hij vanwege zijn werk met de Power BI gereedschappen. Henk is de auteur van “Excelmodellen voor financiële economische informatie” en “ExcelLeren, de Vlootman methode in de praktijk” en medeauteur van het “Praktijkboek Powerpivot in Excel” en “Handboek Power pivot”. Tijdens Excel en Power BI congressen en conferenties is hij een gewaardeerd spreker. Henk is voorzitter van de stichting Power BI gebruikersgroep Nederland.