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.
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.
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.
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.