Werken met arrays in een macro

In dit artikel behandelen wij de variabele genaamd array. Wat is deze array nu precies? Een array is een lijst met data van hetzelfde type (bijvoorbeeld integers, strings of dates). Als je de keuze hebt tussen data opslaan in een tabel op je werkblad of je werkt met een array in een macro dan zou ik altijd voor de laatste optie gaan. VBA kan namelijk veel sneller met een array werken dan dat hij een tabel uit een werkblad kan uitlezen. Het werken met arrays biedt aanzienlijke verbeteringen voor de performance van je macro.

Een array kan 1-dimensionaal zijn. Dit betekent dat alle data op één enkele rij of kolom staat opgeslagen. Je array kan ook meerdere dimensies bevatten. Hij kan maximaal tot 60 dimensies aan, maar in de praktijk heb je altijd wel aan maximaal 2 of 3 dimensies genoeg.

Voorbeeld van een 1-dimensionale array

We gebruiken als voorbeeld dat we een lijst met vijf namen willen opslaan in een 1-dimensionale array. Deze namen staan in kolom B van het tabblad ‘Blad1’. Het gaat hier om een vijftal namen. Een array begint vanaf het getal nul dus dimmen wij de array met het getal 4 (0,1,2,3,4 = 5 plekken). Voor namen hebben we de variabele van het type string (tekst) nodig. Vervolgens gebruik ik een For Next loop om de namen uit het werkblad op te slaan in de variabele. Je kunt nu in de onderstaande afbeelding zien onder het menu ‘lokale var.’ dat aan het einde van de code de namen netjes opgeslagen zijn in de 1-dimensionale array.

Sub ArrayVoorbeeld1Dimensionaal()

Dim ArrayNamen(4) As String

For i = 0 To 4
    ArrayNamen(i) = Ws.Range("B" & i + 2)
Next i

End Sub

Voorbeeld van een 2-dimensionale array

Je kan meerdere dimensies toevoegen aan een array mits de data van hetzelfde type blijft. Laten we het vorige voorbeeld iets uitbreiden en naast een naam meer data van de persoon vastleggen. Je geeft bij het declareren tussen haakjes aan hoeveel dimensies deze bevat door deze te scheiden met een komma. Indien we naast een voornaam ook een geslacht en het beroep van een persoon vast willen leggen dan is er dus sprake van 3 verschillende kolommen en nog steeds 5 rijen. Omdat je nu 3 soorten data vastlegt krijg je een extra dimensie erbij die je bij het declareren het cijfer 2 (0,1,2) meegeeft. De data sla je dan als volgt op in de array.

Sub ArrayVoorbeeld2Dimensionaal()

    Dim ArrayNamen(4, 2) As String
    
    For i = 0 To 4
        ArrayNamen(i, 0) = Ws.Range("B" & i + 2)
        ArrayNamen(i, 1) = Ws.Range("C" & i + 2)
        ArrayNamen(i, 2) = Ws.Range("D" & i + 2)
    Next i

End Sub

Loop door een array

Je kan op drie verschillende manieren door de elementen binnen een array heen gaan. De eerste loop die je kunt gebruiken is de For each item loop. Laten we opnieuw het voorbeeld van de 1-dimensionale array uit dit artikel gebruiken. In dit voorbeeld hadden we een array genaamd ‘ArraNamen’ waarin een aantal namen waren opgeslagen. Met de onderstaande code laat VBA je een berichtbox zien die alle namen in de array toont in een loop.

For Each Item In ArrayNamen
    MsgBox Item
Next

Je kunt ook door de array heen loopen door een for loop te combineren met Lbound en UBound. Lbound geeft in dit geval altijd de eerste waarde van een array en Ubound geeft de laatste waarde van de array.

For i = LBound(ArrayNamen) To UBound(ArrayNamen)
    MsgBox ArrayNamen(i)
Next i

In de bovenstaande voorbeelden had ik voor het gemak een voorbeeld gebruikt van een 1-dimensionale array. Als we nu het 2 dimensionale array voorbeeld gebruiken uit dit artikel en door deze data heen loopen met beide hiervoor beschreven manieren dan ziet alleen de code bij de laatste methode (LBound/UBound) er anders uit. De for each item loop loopt automatisch al door alle dimensies in de array heen. 

For i = LBound(ArrayNamen) To UBound(ArrayNamen)
    MsgBox ArrayNamen(i, 0)
    MsgBox ArrayNamen(i, 1)
    MsgBox ArrayNamen(i, 2)
Next i

De Split functie

Indien je in een Excel bestand een lijst hebt staan in een cel kun je deze lijst gemakkelijk opnemen met behulp van de split functie. Je schrijft deze functie als volgt ‘arrayVoorbeeld = Split(lijst,”scheidingsteken”)’. In de eerste parameter plaats je dan de cel waar de lijst zich bevindt. In de 2e parameter plaats je het scheidingsteken dat gebruiker wordt tussen dubbele haakjes. In de array worden dan netjes alle elementen gescheiden opgeslagen.

De Join functie

In VBA biedt de Join-functie een handige manier om de elementen van een array samen te voegen tot een enkele tekenreeks. De Join-functie accepteert twee parameters: de eerste is het array dat je wilt samenvoegen, en de tweede is een optionele tekenreeks die als scheidingskarakter fungeert tussen de samengevoegde elementen.

Hier is de syntax van de Join-functie in VBA:

Join(array, [delimiter])

• array: Dit is het array dat je wilt samenvoegen. Het kan een een- of meerdimensionaal array zijn.
• delimiter (optioneel): Dit is een tekenreeks die als scheidingskarakter wordt gebruikt tussen de samengevoegde elementen van het array. Als je dit argument weglaat, worden de elementen zonder scheiding samengevoegd.

Hier is een voorbeeld om het gebruik van de Join-functie in VBA te illustreren:

Sub JoinExample()
    Dim myArray(2) As String
    myArray(0) = "Appel"
    myArray(1) = "Banaan"
    myArray(2) = "Sinaasappel"
    
    Dim result As String
    result = Join(myArray, ", ")
    
    MsgBox result ' Toont: "Appel, Banaan, Sinaasappel"
End Sub

In dit voorbeeld wordt een eendimensionaal array ‘myArray’ gedeclareerd en geïnitialiseerd met drie elementen. De Join-functie wordt vervolgens gebruikt om de elementen samen te voegen, waarbij een komma en een spatie als scheidings karakter worden gebruikt. De resulterende samengevoegde tekenreeks wordt opgeslagen in de variabele result en wordt weergegeven in een berichtvenster via de MsgBox-functie.

Merk op dat de Join-functie een tekenreeks teruggeeft als resultaat. Je kunt dit resultaat toewijzen aan een variabele of direct gebruiken in andere delen van je code, afhankelijk van je vereisten.

 

De Filter functie

De Filter functie is een handig hulpmiddel in VBA om snel en efficiënt specifieke elementen uit een array te selecteren op basis van een bepaald criterium. Dit is vooral handig bij het werken met grote datasets of wanneer je alleen geïnteresseerd bent in een subset van je gegevens.

Hoe werkt het?

De Filter functie neemt drie belangrijke argumenten:

1. InputStrings: Dit is de originele array waarvan je elementen wilt filteren.
2. Value: Dit is de waarde of het patroon waarop je wilt filteren.
3. Include: (Optioneel) Dit is een boolean (True/False) die aangeeft of je de overeenkomende elementen wilt opnemen (True) of juist wilt uitsluiten (False). Standaard is dit ingesteld op True.

De functie retourneert een nieuwe array die alleen de elementen bevat die voldoen aan het opgegeven criterium.

Toepassingen:

• Gegevens opruimen: Verwijder ongewenste waarden of fouten uit een dataset.
• Zoeken: Vind snel alle elementen die overeenkomen met een specifiek patroon.
• Categoriseren: Verdeel een array in subarrays op basis van bepaalde criteria.

Belangrijke opmerkingen:

• Filter werkt alleen met tekstuele gegevens (strings).
• De functie is hoofdlettergevoelig.
• Als er geen overeenkomsten worden gevonden, retourneert Filter een lege array.

Voorbeeld van een filter functie toepassing:

Sub FilterVB()

    Dim arr() As String
    arr = Split("appel,banaan,peer,citroen", ",") ' Maak een array van fruit

    Dim gefilterd() As String
    gefilterd = Filter(arr, "peer") ' Filter op het woord "peer"

    ' gefilterd bevat nu alleen: "peer"

End Sub

ReDim Array

De ReDim-instructie wordt gebruikt om de grootte van een array aan te passen. De ReDim-instructie kan worden gebruikt om een array te vergroten of te verkleinen.

Er zijn twee manieren om ReDim te gebruiken in VBA, afhankelijk van het gewenste gedrag:

1. ReDim Preserve: Deze syntax wordt gebruikt wanneer je de grootte van een array wilt behouden en alleen de bovengrens (Upper Bound) wilt wijzigen. Hierdoor worden de bestaande waarden in de array behouden en worden er extra elementen toegevoegd indien nodig. Merk op dat het Preserve-sleutelwoord ervoor zorgt dat de bestaande waarden behouden blijven bij het wijzigen van de grootte van de array.

2. ReDim: Deze syntax wordt gebruikt wanneer je de grootte van een array wilt wijzigen en nieuwe waarden wilt initialiseren. Dit betekent dat de bestaande waarden in de array worden gewist en opnieuw worden geïnitialiseerd. Door simpelweg Redim te gebruiken zonder het Preserve-sleutelwoord, worden de bestaande waarden in de array gewist en wordt de array opnieuw geïnitialiseerd met de nieuwe grootte.

Zie hieronder een voorbeeld van een toepassing van een ReDim Preserve-instructie. Stel je loopt door een lijst met pizza gerechten en je wilt deze opslaan in een array:

 

Sub PizzaArray()

    Dim PizzaVerzameling() As String
    
    EinderijPizzas = Blad1.Range("C100000").End(xlUp).Row
    teller = 0
    For i = 12 To EinderijPizzas
        ReDim Preserve PizzaVerzameling(teller)
        PizzaVerzameling(teller) = Blad1.Range("C" & i)
        teller = teller + 1
    Next i
    
End Sub

In de onderstaande afbeelding kun je zien dat ik het script na de loop op pauze heb gezet. Je ziet daar in het lokale variabelen menu dat de array netjes in het geheugen opgeslagen staat:

Statische arrays versus dynamische arrays

n VBA heb je de keuze uit twee belangrijke soorten arrays: statische arrays en dynamische arrays. Elk heeft zijn eigen voor- en nadelen, en de keuze tussen beide hangt af van hoe je de array in je code wilt gebruiken.

Statische arrays

Een statische array heeft een vaste grootte die wordt bepaald wanneer je de array declareert. Dit betekent dat je van tevoren moet weten hoeveel elementen de array zal bevatten.

Voordelen:

• Eenvoud: Statische arrays zijn eenvoudig te declareren en te gebruiken, vooral voor beginners.
• Snelheid: Omdat de grootte vaststaat, kan de computer geheugen efficiënter toewijzen, wat kan leiden tot iets snellere uitvoering.

Nadelen:

• Onflexibel: Als je tijdens het uitvoeren van je code meer elementen nodig hebt dan oorspronkelijk gepland, kun je de grootte van een statische array niet veranderen. Dit kan leiden tot fouten of de noodzaak om je code opnieuw te ontwerpen.

Wanneer gebruik je statische arrays?

• Als je van tevoren precies weet hoeveel elementen je array nodig heeft.
• Als je werkt met een relatief klein aantal elementen.
• Als snelheid een kritieke factor is in je code.

Dynamische arrays

Een dynamische array heeft een variabele grootte die tijdens het uitvoeren van je code kan worden aangepast. Je kunt elementen toevoegen of verwijderen zonder de array opnieuw te declareren.

Voordelen:

• Flexibiliteit: Dynamische arrays zijn ideaal als je niet van tevoren weet hoeveel elementen je nodig hebt, of als het aantal elementen tijdens het uitvoeren van je code kan veranderen.
• Geheugenbeheer: Dynamische arrays gebruiken geheugen efficiënter omdat ze alleen de benodigde ruimte in beslag nemen.

Nadelen:

• Complexiteit: Het werken met dynamische arrays kan iets complexer zijn vanwege de noodzaak om de grootte te beheren en aan te passen. Dit doe je met behulp van de redim-instructie die we in de vorige paragraaf beschreven hebben.
• Snelheid: Het wijzigen van de grootte van een dynamische array kan iets meer tijd in beslag nemen dan het werken met een statische array.

Wanneer gebruik je dynamische arrays?

• Als je niet van tevoren weet hoeveel elementen je array nodig heeft.
• Als het aantal elementen tijdens het uitvoeren van je code kan veranderen.
• Als je werkt met een groot aantal elementen en geheugenbeheer belangrijk is.

 

Ik hoop dat je nu een beter beeld hebt gekregen van hoe je arrays kunt gebruiken in een macro. Mocht je nog vragen hebben, laat het weten in de  reacties!