VBA recursie

Recursieve functies of procedures zijn procedures of functies die zichzelf weer aanroepen. Een voordeel van het recursief programmeren kan zijn dat de programmacode korter en eleganter wordt. Daarnaast zijn er een aantal situaties waarbij het recursief aanroepen de enige mogelijkheid is om het te programmeren. In dit artikel zal recursie uitgelegd worden aan de hand van een paar voorbeelden met VBA.

Berekening faculteit

VBA heeft geen ingebouwde functie om de faculteit van een getal te berekenen. Het is eenvoudig om hiervoor een functie te maken. Die functie kan er als volgt uitzien:

Function FactorialLoop(x As Byte) As Double
    Dim i As Byte
    FactorialLoop = 1
    For i = 1 To x
        FactorialLoop = FactorialLoop * i
    Next i
End Function

Door middel van een loopconstructie wordt de faculteit uitgerekend. Maar deze functie kan ook als recursieve functie geprogrammeerd worden. De VBA-code ziet er dan bijvoorbeeld als volgt uit:

Function FactorialRecursive(x As Byte) As Double
    FactorialRecursive = 1
    If x > 1 Then FactorialRecursive = x * FactorialRecursive(x - 1)
End Function

In deze functie is te zien dat de functie zichzelf weer aanroept. De functie maakt gebruikt van de eigenschap: n!=n*(n-1)!

Bij bijvoorbeeld 4! worden achtereenvolgens de volgende berekeningen uitgevoerd:

4 x (4-1)!

4 x 3!

4 x 3 x (3-1)!

4 x 3 x 2!

4 x 3 x 2 x (2-1)!

4 x 3 x 2 x 1!

4 x 3 x 2 x 1

Bij bovenstaande functies is overigens geen foutcontrole ingebouwd. Er wordt vanuit gegaan dat de gebruiker geldige getallen invoert.

Spaties tellen

Nu volgt een voorbeeld van een functie om spaties te tellen in tekststring/zin. Het is een erg omslachtige manier om dat met een recursieve functie te doen. In feite hoeft de functie maar uit één coderegel te bestaan:

Function CountSpaceShort(TextString As String)
    CountSpaceShort = Len(TextString) - Len(Replace(TextString, " ", ""))
End Function

Maar onderstaande recursieve variant geeft wel mooi aan hoe je optionele variabelen in combinatie met recursieve functies kunt gebruiken. De functie voor het spaties tellen ziet er dan als volgt uit:

Function CountSpaces(TextString As String, Optional Start As Integer, Optional SpaceCount As Integer) As Integer
    Dim i As Long
    'bij eerste keer aanroepen functie Start instellen op 1
    If Start = 0 Then Start = 1
    i = InStr(Start, TextString, " ")
    If i > 0 Then
        SpaceCount = SpaceCount + 1
        'recursief aanroepen van de functie
        CountSpaces TextString, i + 1, SpaceCount
    End If
    CountSpaces = SpaceCount
End Function

In deze functie worden 2 optionele variabelen gebruikt. Met de variabele Start wordt ingesteld vanaf welke positie in het woord of de zin gezocht moet worden naar de volgende spatie. Bij het recursief aanroepen wordt deze waarde telkens ingesteld op één positie na de laatst gevonden spatie. Met de optionele variabele SpaceCount wordt de telling van het aantal spaties bijgehouden.

Bestandsnamen uitlezen uit folder inclusief alle subfolders

Nu volgt een voorbeeld waarbij recursief programmeren vaak gebruikt wordt. Stel dat alle bestandsnamen uit een bestaande map inclusief die uit de onderliggende submappen moeten worden uitgelezen. Omdat van te voren niet bekend is hoeveel submappen er zijn, kan dit goed opgelost worden door het recursief aanroepen van de procedure:

Sub ReadFilesRecursive(MapName As String)
    Dim FileName As String, PathName As String
    Dim Subfolders() As String, SubFolderCount As Integer
    Dim i As Integer
    'zeker stellen dat mapnaam altijd met \ eindigt
    If Right(MapName, 1) <> "\" Then MapName = MapName & "\"
    FileName = Dir(MapName & "*.*", vbDirectory)
    While Len(FileName) <> 0
        If Left(FileName, 1) <> "." Then 'huidige map
            PathName = MapName & FileName
            If GetAttr(PathName) = vbDirectory Then
                'in array opslaan van gevonden subfolders
                ReDim Preserve Subfolders(0 To SubFolderCount)
                Subfolders(SubFolderCount) = PathName
                SubFolderCount = SubFolderCount + 1
            Else
                Debug.Print MapName & FileName
            End If
        End If
        FileName = Dir()
    Wend
    'uitlezen van array met subfolders en recursief procedure aanroepen
    For i = 0 To SubFolderCount - 1
        ReadFilesRecursive Subfolders(i)
    Next i
End Sub

Deze procedure kan bijvoorbeeld vanuit een andere procedure als volgt aangeroepen worden: ReadFilesRecursive ("D:\Data")

In deze procedure worden eerst alle files en submappen uit de opgegeven map uitgelezen. Als dit een submap betreft dan wordt deze submap weggeschreven in een array. Als het een bestandsnaam betreft, dan wordt deze met Debug.Print weggeschreven in het Venster Direct. Na het uitlezen van deze map wordt de procedure recursief aangeroepen, waarbij de array met opgeslagen submapnamen als argument van de functie wordt gebruikt. De gegevens uit deze submappen worden op dezelfde wijze behandeld als de gegevens uit de opgegeven map. Dus onderliggende mappen van de submap worden toegevoegd aan de array en bestandsnamen worden weergegeven in het Venster Direct. De procedure loopt net zolang totdat de gehele array is uitgelezen en daarmee alle bestanden uit de opgegeven map inclusief alle onderliggende submappen zijn weggeschreven naar het Venster Direct.

Maar toch is ook het mogelijk om bestanden uit een folder en de onderliggende subfolders uit te lezen zonder een recursieve procedure. Dit is een voorbeeld van zo’n procedure in Excel:

Sub ReadFilesNOTRecursive()
    Dim arr() As String
    arr = Split(CreateObject("wscript.shell").exec("cmd /c Dir ""D:\Data\"" /b /s").stdout.readall, vbCrLf)
    Range("A1").Resize(UBound(arr) + 1) = Application.Transpose(arr)
End Sub

Er wordt in deze procedure onder andere handig gebruik gemaakt van twee attributen van de DIR-functie, te weten /b en /s. Attribuut /b zorgt ervoor dat geen samenvattinginformatie en kolomkoppen worden gegenereerd en attribuut /s zorgt ervoor dat alle informatie uit de gekozen folder inclusief alle subfolders wordt gepresenteerd. De bestandsgegevens worden in deze procedure weergegeven in kolom A van de actieve sheet. Een nadeel van deze procedure is dat ook de mapnamen tussen de bestandsnamen staan. Dit kan voorkomen worden door alleen bestanden met een bepaalde extensie uit te lezen. Vervang bijvoorbeeld D:\Data\ door D:\Data\*.xlsx en alle normale Excel-bestanden worden weergegeven, zonder dat de mapnamen er ook tussen staan. Als alle bestandsnamen zonder de mapnamen moeten worden weergegeven dan kan dat met de recursieve procedure.

Samenvatting

Recursie is in sommige situaties een onmisbaar hulpmiddel. Vooral wanneer niet vooraf bekend is hoe vaak een procedure doorlopen moet worden. Bijvoorbeeld bij het analyseren van een boomstructuur, waarbij vooraf niet bekend is hoeveel vertakkingen er zijn. De grootste valkuil bij recursie is dat dit eindigt in een oneindige loop. Daarom is het van groot belang om goed na te denken over het eindigen van de procedure. Er moet altijd een uitweg zijn uit de procedure.

Vragen / suggesties

Hopelijk heeft dit artikel geholpen bij het begrijpen en toepassen van recursie in VBA. Als er verdere vragen over dit onderwerp zijn of suggesties voor verbetering, plaats dan een reactie hieronder.