VBA recursion

Recursive functions or procedures are procedures or functions that call themselves again. An advantage of recursive programming can be that the program code becomes shorter and more elegant. In addition, there are a number of situations where the recursive invocation is the only way to program it. In this article, recursion will be explained with a few examples with VBA.

Factorial calculation

VBA does not have a built-in function to calculate the factorial of a number. It is easy to create a function for this. That function may look like this:

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

The faculty is calculated by means of a loop construction. But this function can also be programmed as a recursive function. The VBA code will then look like this, for example:

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

In this function you can see that the function is calling itself again. The function uses the property: n!=n*(n-1)!

For example 4! the following calculations are performed successively:

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

Incidentally, no error check is built into the above functions. It is assumed that the user enters valid numbers.

Count spaces

Now an example of a function to count spaces in text string/sentence. It's a very cumbersome way to do that with a recursive function. In fact, the function only needs to consist of one line of code:

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

But the recursive variant below shows nicely how you can use optional variables in combination with recursive functions. The function for counting spaces then looks like this:

Function CountSpaces(TextString As String, Optional Start As Integer, Optional SpaceCount As Integer) As Integer
    Dim i As Long
    'on first call set function Start to 1
    If Start = 0 Then Start = 1
    i = InStr(Start, TextString, " ")
    If i > 0 Then
        SpaceCount = SpaceCount + 1
        'recursively calling the function
        CountSpaces TextString, i + 1, SpaceCount
    End If
    CountSpaces = SpaceCount
End Function

In this function 2 optional variables are used. The variable Start sets from which position in the word or sentence the next space should be searched. When calling recursively, this value is set to one position after the last space found. The optional SpaceCount variable keeps track of the space count.

Read file names from folder including all subfolders

The following is an example where recursive programming is often used. Suppose that all file names from an existing folder including those from the underlying subfolders are to be read. Because it is not known in advance how many subfolders there are, this can be solved by recursively calling the procedure:

Sub ReadFilesRecursive(MapName As String)
    Dim FileName As String, PathName As String
    Dim Subfolders() As String, SubFolderCount As Integer
    Dim i As Integer
    'make sure folder name always ends with \
    If Right(MapName, 1) <> "\" Then MapName = MapName & "\"
    FileName = Dir(MapName & "*.*", vbDirectory)
    While Len(FileName) <> 0
        If Left(FileName, 1) <> "." Then 'current folder
            PathName = MapName & FileName
            If GetAttr(PathName) = vbDirectory Then
                'save found subfolders in an array
                ReDim Preserve Subfolders(0 To SubFolderCount)
                Subfolders(SubFolderCount) = PathName
                SubFolderCount = SubFolderCount + 1
            Else
                Debug.Print MapName & FileName
            End If
        End If
        FileName = Dir()
    Wend
    'reading of array with subfolders and calling procedure recursively
    For i = 0 To SubFolderCount - 1
        ReadFilesRecursive Subfolders(i)
    Next i
End Sub

For example, this procedure can be called from another procedure as follows: ReadFilesRecursive ("D:\Data")

In this procedure, all files and subfolders are first read from the specified folder. If this is a subfolder, this subfolder will be written to an array. If it is a file name, it is written with Debug.Print in the Immediate Window.After reading this folder, the procedure is called recursively, using the array of stored subfolder names as the argument of the function. The data from these subfolders is treated in the same way as the data from the specified folder. So sub-folders of the subfolder are added to the array and file names are displayed in the Immediate Window. continues until the entire array has been read and thus all files from the specified folder including all sub-folders have been written to the Immediate Window.

But it is also possible to read files from a folder and the underlying subfolders without a recursive procedure. This is an example of such a 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

Among other things, this procedure conveniently uses two attributes of the DIR function, namely /b and /s. Attribute /b ensures that no summary information and column headings are generated and attribute /s ensures that all information from the chosen folder including all sub-folders is presented. The file information is displayed in column A of the active sheet in this procedure. A disadvantage of this procedure is that the folder names are also included between the file names. This can be prevented by only reading files with a certain extension. For example, replace D:\Data\ with D:\Data\*.xlsx and all normal excel files will be displayed, without the folder names also appearing in between. If all file names are to be displayed without the folder names then this can be done with the recursive procedure.

Resume

Recursion is an indispensable tool in some situations. Especially when it is not known in advance how often a procedure must be followed. For example when analyzing a tree structure, where it is not known in advance how many branches there are. The biggest pitfall in recursion is that it ends in an endless loop. That is why it is very important to think carefully about ending the procedure. There must always be a way out of the procedure.

Questions / suggestions

Hopefully this article has helped you understand and apply recursion in VBA. If you have any questions about this topic or suggestions for improvement, please post a comment below.