VBA For loop

Indien je binnen je macro een bepaalde handeling meerdere keren achter elkaar wilt uitvoeren is het niet nodig dat je deze specifieke code meerdere keren achter elkaar plakt in je script. Je plaatst deze code dan simpelweg in een loop. Binnen VBA heb je twee soorten loops waar je van gebruik kunt maken. De eerste is de ‘For‘ loop. Het alternatief is de ‘Do‘ loop.

In dit artikel zal ik een beschrijving geven van de soorten VBA For loops binnen een macro en voorbeelden geven van situaties waarin je deze soorten loops kunt toepassen.

De definitie van For loops

Met de For loops binnen een macro geef je aan dat je een stuk code een bepaald aantal keer moet blijven herhalen. Dit aantal kun je meegeven door een cijfer hard neer te zetten, maar je kan bijvoorbeeld ook een variabele meegeven die een bepaald cijfer als waarde bevat. Je hebt van de VBA For loop twee verschillende soorten.

For … Next loop

Dit is de loop binnen VBA die ik eigenlijk het meeste gebruik tijdens mijn werkzaamheden. Met behulp van deze loop kun je eenvoudig aan geven dat je een blok code een aantal keer wilt laten uitvoeren. Indien je bijvoorbeeld 5 keer een message box wilt zien met daarin een oplopend cijfer van het aantal loops dan schrijf je dit als volgt.

Sub ForTest()

    For i = 1 To 5
        MsgBox i
    Next i

End Sub

In de bovenstaande loop wordt i dus bij iedere herhaling opgehoogd met 1. Dit getal kun je ook aanpassen.

Sub ForTest()

    For i = 0 To 10 Step 2
        MsgBox i
    Next i

End Sub

Je bent overigens ook niet verplicht om bij de step een positief getal mee te geven. Er is ook een optie om af te tellen van een hoger getal naar een lager getal met een negatieve step zoals in het onderstaande voorbeeld.

Sub ForTest()

    For i = 10 To 0 Step -2
        MsgBox i
    Next i

End Sub

Je kunt bij een For loop ook een onderdeel laten bepalen door de inhoud van een variabele. Als je bijvoorbeeld een bepaalde actie wilt laten uitvoeren op een lijst met medewerkers, maar deze lijst met medewerkers kan op ieder moment wijzigen. In dat geval wil je niet continu je code hoeven aanpassen op het moment dat er een mutatie plaatsvindt in het aantal medewerkers. Stel je voor dat de namen van deze medewerkers worden ingevuld in kolom A van je Blad1. Je code wordt dan

Sub ForTest()

    For i = 1 To ThisWorkbook.Sheets("blad1").Range("A100000").End(xlUp).Row
        'uit te voeren code
    Next i

End Sub

In het bovenstaande voorbeeld staat het begin van de for loop vast op het cijfer één. Voor het einde van de loop zoekt hij in de A kolom wat de onderste cel is die invoer bevat. Mocht er nu een werknemer aan de lijst toegevoegd worden dan wordt hij automatisch meegenomen met deze loop.

Nesten van For loops binnen een macro

Je ziet dat ik in de bovenstaande voorbeelden altijd gebruik heb gemaakt van de letter i die dan de functie van de ‘teller’ had. Je kunt als teller in principe alles neerzetten wat je wilt, maar de letter i is voor de buitenste loop wel de standaard. Nu zul je denken…wat bedoelt hij nu met ‘buitenste’ loop. Dat zal ik je uitleggen. Het kan ook voorkomen dat je binnen je for loop een tweede loop nodig hebt. Dit noem je dan een dubbel-geneste loop. In de binnenste loop kun je dan niet opnieuw gebruik maken van de letter i als je deze al gebruikt hebt voor de buitenste loop. Het is dan gebruikelijk dat je voor de binnenste loop de letter ‘j’ als teller gebruikt. 

Sub ForTestDubbelGenest()

    For i = 1 To ThisWorkbook.Sheets("blad1").Range("A10000").End(xlUp).Row
        For j = 3 To 5
            'uit te voeren code
        Next j
    Next i

End Sub

Stel je voor dat in het voorbeeld van de werknemers per werknemer (dus rij) een bepaalde actie uitgevoerd moet worden. De i geeft dan de rij aan waarop deze actie uitgevoerd wordt. Maar per rij dient de actie uitgevoerd te worden op de kolommen C,D & E (3,4 &5). De letter j functioneert in dit voorbeeld dan als de teller van de binnenste loop die de kolom aangeeft.

Exit For

Je hebt binnen een For loop ook nog de mogelijkheid om de loop te verlaten voordat de laatste iteratie bereikt is. Met de opdracht ‘Exit For’ kun je op ieder gewenst moment de For loop verlaten. In de praktijk zal je dit vooral toepassen als er aan een speciale voorwaarde voldaan is binnen de code van de For loop (anders komt je code namelijk niet eens voorbij de eerste iteratie van je loop). Je plaatst de ‘Exit For’ opdracht dan binnen een IF Statement. In het onderstaande voorbeeld zal de For loop bij de 4e iteratie aankomen als aan de voorwaarde van de IF statement voldaan wordt (i is dan namelijk 4). Via de Exit for regel loopt de code dan verder na de For loop.

Sub ForTest()
    For I = 1 To 10
        If I = 4 Then
            Exit For
        End If
    Next I
End Sub

For Each … Next loop

Met de For Each loop kun je handelingen uitvoeren door een verzameling objecten heen. Het aantal herhalingen is hierdoor dus afhankelijk door het aantal objecten waar je doorheen loopt. Ik zal een aantal voorbeelden hiervan geven.

Sub ForEachWsvoorbeeld()

    Dim wb As Workbook
    Dim Ws As Worksheet
    
    For Each Ws In ThisWorkbook.Worksheets
        If Ws.Name = "MacroExcel" Then
            Ws.Name = "aangepast"
        End If
    Next Ws

End Sub

De bovenstaande loop voert een handeling uit op elke worksheet (tabblad) binnen je workbook. Komt hij daarin een worksheet tegen met de naam ‘MacroExcel’ dan past hij deze aan naar ‘aangepast’.

Ik kan de For Each loop ook toepassen op cellen binnen een bepaalde range. Stel ik wil alle cellen op het tabblad “aangepast” binnen de range A1:D10 die een waarde hebben die hoger is dan negen aanpassen naar nul. Dat kan ik uitvoeren met de onderstaande code.

Sub ForEachCellInRangevoorbeeld()
    Dim rng As Range
    For Each rng In ThisWorkbook.Sheets("aangepast").Range("A1:D10")
        If Abs(rng.Value) > 9 Then
           rng.Value = 0
        End If
    Next
End Sub

5 praktijkvoorbeelden voor toepassing van de VBA for loop

Zoals je net hebt gelezen is  een “for loop” in VBA Excel  een krachtig hulpmiddel waarmee je taken kunt automatiseren die herhaald moeten worden over een reeks waarden of elementen. Hier zijn drie veelvoorkomende scenario’s waarin een for loop nuttig kan zijn:

1. Data-cleaning met behulp van een VBA for loop:

In veel datasets kan het voorkomen dat cellen ontbrekende of ongewenste waarden bevatten. Een for loop kan worden gebruikt om door elke cel in een kolom of rij te gaan om deze waarden te identificeren en te vervangen of te verwijderen.

Dim LastRow As Long
Dim i As Long

LastRow = ThisWorkbook.Sheets("Sheet1").Cells(ThisWorkbook.Sheets("Sheet1").Rows.Count, "A").End(xlUp).Row

For i = 1 To LastRow
    If ThisWorkbook.Sheets("Sheet1").Cells(i, 1).Value = "" Then
        ThisWorkbook.Sheets("Sheet1").Cells(i, 1).Value = "N/A" 'Vervang lege cellen met "N/A"
    End If
Next i

2. Gegevens aggregatie:

Stel dat je een werkblad hebt met verkoopgegevens voor verschillende producten op verschillende datums. Je kunt een for loop gebruiken om door elke rij te lopen en een som te berekenen voor een bepaald product of een bepaalde periode.

Dim TotalSales As Double
TotalSales = 0

For i = 2 To LastRow 'Begin bij 2 om de koprij over te slaan
    If ThisWorkbook.Sheets("Sheet1").Cells(i, 2).Value = "SpecifiekProduct" Then
        TotalSales = TotalSales + ThisWorkbook.Sheets("Sheet1").Cells(i, 3).Value
    End If
Next i

MsgBox "Totale verkoop voor SpecifiekProduct is: " & TotalSales

3. Cellen opmaken op basis van criteria:

Je kunt een for loop gebruiken om door een reeks cellen te gaan en de opmaak te wijzigen op basis van de waarde van elke cel. Bijvoorbeeld, het markeren van alle waarden boven een bepaalde drempel in rood.

For i = 1 To LastRow
    If ThisWorkbook.Sheets("Sheet1").Cells(i, 4).Value > 100 Then
        ThisWorkbook.Sheets("Sheet1").Cells(i, 4).Interior.Color = RGB(255, 0, 0) 'Kleur cel in rood
    End If
Next i

4. Gegevens over meerdere werkbladen consolideren met een VBA for loop:

Stel je voor dat je een Excel-werkboek hebt waarin elke maand een nieuw werkblad wordt toegevoegd met verkoopgegevens van die maand. Aan het einde van het jaar wil je misschien alle gegevens in één overzichtelijk werkblad consolideren. Een “for loop” kan hierbij helpen.

Dim ws As Worksheet
Dim DestRow As Long

For Each ws In ThisWorkbook.Worksheets
    If ws.Name <> "Consolidated" Then 'Vermijd het geconsolideerde werkblad zelf
        For i = 2 To ws.Cells(ws.Rows.Count, "A").End(xlUp).Row 'Begin bij 2 om koprijen te vermijden
            DestRow = ThisWorkbook.Sheets("Consolidated").Cells(ThisWorkbook.Sheets("Consolidated").Rows.Count, "A").End(xlUp).Row + 1
            ws.Rows(i).EntireRow.Copy Destination:=ThisWorkbook.Sheets("Consolidated").Rows(DestRow)
        Next i
    End If
Next ws

5. Data-transpositie:

In sommige situaties wil je misschien gegevens uit rijen naar kolommen verplaatsen of vice versa. Een VBA for loop kan worden gebruikt om deze transpositieoperatie uit te voeren.

Dim LastCol As Long

LastCol = ThisWorkbook.Sheets("Sheet1").Cells(1, ThisWorkbook.Sheets("Sheet1").Columns.Count).End(xlToLeft).Column

For i = 1 To LastCol
    ThisWorkbook.Sheets("Sheet2").Cells(i, 1).Value = ThisWorkbook.Sheets("Sheet1").Cells(1, i).Value
Next i

Ik hoop dat het nu een stuk duidelijker voor je geworden is hoe je VBA For loops moet gebruiken. Indien dit niet het geval is. Laat het weten in de reacties!