Recursion Demystified: Creating Subfolders

This is part 1 of 3 of a series of articles on creating folder trees in VBA.

There are lots of times when I find myself needing to create a series of subfolders in Windows.  For example, I have written several applications where I allow users to associate photographs or scanned images with records in the database.  Instead of embedding the binary files into the tables, though, I save them in a shared network folder.  

I learned the hard way the importance of incorporating subfolders into such a design.  It turns out that Windows hits a performance wall once you get about 300,000 files in a single folder.  (That's a story for another day.)  

One side effect of using subfolders this way is that I find myself creating lots of subfolders.  Oftentimes, I'm creating multiple levels of subfolders at a time.  For example, I have a folder mapped as G:\Photos and I need to create the following folder hierarchy, G:\Photos\312\564312\.

Adding a single new folder is relatively straightforward.  Adding an arbitrary number of subfolders becomes trickier.  

Recursion is the only thing we have to fear about recursion

Dealing with hierarchies and tree structures are problems that often lend themselves to recursive solutions.  If you've ever wondered what you could use recursion for besides calculating factorials, this is a great practical example.

Recursion, if you're not familiar with the term, is any procedure that calls itself during its execution.  Here's a visual example of the concept.  In the screenshot below, I'm using OBS to preview my screen.  On the screen, I've opened an image that I drew for an earlier article.  However, I have the OBS preview window open in front of the image.  So, part of the image being captured is the preview window itself.  

Writing a recursive procedure requires two things: 1) a call to the routine itself and 2) a limiting condition that prevents the procedure from calling itself indefinitely.  Without such a condition, we end up with infinite recursion.  In practice, the recursion is cut short when the call stack runs out of space.  The term for that is WorldsMostPopularProgrammingQandASite "stack overflow."

Learning by example

Below is a version of my VerifyFolder routine optimized for debugging.  I added line numbers to aid in our discussion.  You can leave these in place; the VBA works fine with them.  

^{Refer to the bottom of the article for the fully documented version without the Debug.Print statements.  The version at the end also includes a guard clause to check for properly formatted paths.}

Sub VerifyFolder(FolderPath As String)
10    Debug.Print "Starting '" & FolderPath & "'"
    
20    Dim fs As Object
30    Set fs = CreateObject("Scripting.FileSystemObject")
40    If Not fs.FolderExists(FolderPath) And FolderPath <> "" Then
50        VerifyFolder Left(FolderPath, InStrRev(FolderPath, "\", Len(FolderPath) - 1))
60        fs.CreateFolder FolderPath
70        Debug.Print "Created  '" & FolderPath & "'"
80    End If
    
90    Debug.Print "Exiting  '" & FolderPath & "'"
End Sub

Walkthrough

Let's walk through the function call to understand how the recursive function works.  To begin, you'll need to start with a folder where you have write permissions.  I'll use my profile folder, C:\Users\Mike.  I am going to create four subfolders inside this parent folder, \TestA\TestB\TestC\TestD.

Here's what this looks like when we run it from the Immediate Window:

VerifyFolder "C:\Users\Mike\TestA\TestB\TestC\TestD"
Starting 'C:\Users\Mike\TestA\TestB\TestC\TestD'
Starting 'C:\Users\Mike\TestA\TestB\TestC\'
Starting 'C:\Users\Mike\TestA\TestB\'
Starting 'C:\Users\Mike\TestA\'
Starting 'C:\Users\Mike\'
Exiting  'C:\Users\Mike\'
Created  'C:\Users\Mike\TestA\'
Exiting  'C:\Users\Mike\TestA\'
Created  'C:\Users\Mike\TestA\TestB\'
Exiting  'C:\Users\Mike\TestA\TestB\'
Created  'C:\Users\Mike\TestA\TestB\TestC\'
Exiting  'C:\Users\Mike\TestA\TestB\TestC\'
Created  'C:\Users\Mike\TestA\TestB\TestC\TestD'
Exiting  'C:\Users\Mike\TestA\TestB\TestC\TestD'

So, what is happening here?  Notice that the routine is called five times before it ever exits.  The first time through, the code checks to see if the path C:\Users\Mike\TestA\TestB\TestC\TestD exists.  It sees that it does not, but it does not immediately try to create that folder.  If it did, the FileSystemObject would complain because there is also no \TestC\ folder yet.  

Instead of trying to create subfolder \TestD\, the routine calls itself (line 50).  However, rather than call itself with the same path, it chops off the last folder and calls itself passing \TestC\ as the last folder in the path.  Of course, \TestB\ is also missing.  The routine calls itself again, passing \TestB\ as the final folder in the path.

This continues until the routine calls itself with an existing path, C:\Users\Mike\. This is our limiting condition.  When this happens, the conditional check in line 40 returns False.  Thus, the routine is able to reach the end.

Once our routine reaches the End Sub line, the recursion begins unwinding.  The caller is able to continue from line 60.  Thus, folder \TestA\ is created.  The routine then exits allowing the next caller up the stack to also continue from line 60.  Folder \TestB\ is created.  And so on until the routine creates all four subfolders and finally exits for the last time.

Final thoughts

Recursion is a difficult concept for developers to wrap their heads around.  It's notoriously hard to troubleshoot, can gobble up memory if you're not careful, and can lead to some nasty bugs.  But, given the right situation it can lend itself to some truly elegant solutions.  It's an important tool to have in your developer toolbox, even if it only comes out every once in a while.

The Code

For information about the Throw() function, refer to this article: Throwing Errors in VBA.

'---------------------------------------------------------------------------------------
' Procedure : VerifyFolder
' DateTime  : 9/28/2007 - 11/29/2020 15:05
' Author    : Mike
' Purpose   : Verify that a folder exists.  If not, create the folder.
' Notes     - The folder can be passed with or without a trailing \.
'           - This procedure takes care of creating the entire path, so that if
'             a path with three non-existent folders is passed all three folders
'             are created.
'---------------------------------------------------------------------------------------
'
Sub VerifyFolder(FolderPath As String)
    If Not (FolderPath Like "?:\*" Or FolderPath Like "\\*") Then
        Throw "{0} is not a valid folder path.", FolderPath
    End If

    Dim fs As Object
    Set fs = CreateObject("Scripting.FileSystemObject")
    If Not fs.FolderExists(FolderPath) And FolderPath <> "" Then
        VerifyFolder Left(FolderPath, InStrRev(FolderPath, "\", Len(FolderPath) - 1))
        fs.CreateFolder FolderPath
    End If
End Sub

Image by Gianni Crestani from Pixabay