Recursion in VBA – Listing the Outlook Folder Hierarchy

by Philipp Stiefel, originally published 2020-12-06

last revision on 2020-12-14

Photo by saskia fairfull on Unsplash

I just spotted an interesting question in a Microsoft Access forum where someone is asking how to list the whole Microsoft Outlook folder hierarchy with VBA. This question is so interesting that, instead of just answering briefly in the forum, I rather write a longer text here.

This matter is interesting not because of the topic itself, but because it is handing me the opportunity on a plate to explain an important programming concept: Recursion

Recursion in Theory

Recursion in Programming in general, not only in VBA, is when a procedure or function is calling itself. This is always combined with some sort of condition that will make the procedure stop calling itself. Otherwise this would sooner or later lead to your program running out of stack space and the procedure stopping with an error.

An important fact: When a procedure is called again by itself, it is a completely new “instance” of the procedure and local variables inside the procedure are newly initialized and completely independent of their counterpart in the calling “instance” of the same procedure.

Recursion is a powerful tool if you need to perform an action on each item in a hierarchy of items. There is only very little code required to make recursion work and that code usually feels quite elegant to me.

However, be aware that there are also some drawbacks to recursion. The fact that all arguments and the local variables (at least their pointers) are stored on the call stack can use quite a bit of memory and after several thousand calls will lead to an Error 28 - Out of stack space. – This should be a normal runtime error in VBA, but during my tests for this article I didn’t get this error but a complete crash of VBA and the host application (I tried both, Access, and Excel). In many programming environments this is not an unusual reaction to a stack overflow, but I wasn’t expecting this in VBA.

A lesser drawback is the additional execution time required for the context switch to another procedure. E.g., for copying the argument values to the call stack and then invoking the new “instance” of the function or procedure.

Most, probably all, logic implemented with recursion can also be implemented in an iterative approach, e.g., with a For-Next- or Do-Until-Loop in VBA. This will use less memory and probably execute faster. However, depending on the task at hand, the code for an iterative implementation will be much longer, less elegant, and often harder to understand.

The Outlook Folder Hierarchy

If you are using Microsoft Outlook, you certainly know the Outlook folder hierarchy. – If you don’t, don’t worry. It’s not essential to understanding this text, it is merely the backdrop for my sample code.

The Outlook folder hierarchy has a root object, the Namespace. The Namespace contains a collection property Folders. Each Folder object in the collection also has its own Folders collection. This can go on for any number of nest levels. (There certainly is a limited of nest levels, but I don’t know how deep it is.)

So, the original question, which I mentioned at the beginning, was how to list the name of each folder in that hierarchy.

Recursion in VBA in Practice

For completeness sake let’s start with some boiler plate code to create an Outlook Application instance, get the MAPI Namespace as root of the folder hierarchy, and finally get a Folder object representing one of the email accounts in that Outlook profile.

For our lesson on recursion, we now need to focus on the PrintSubFolders sub procedure called in the last code line above, the root folder of the email account is passed into the procedure as an argument.

Inside the procedure we iterate through the (sub) folders of the root folder that was passed in and print out the names of the folders to the VBA Immediate Pane. (To see this output, you might need to press [CTRL]+[G] to show the Immediate Pane, if it is not visible already.)

So, far this is simple. But on the next line of code, we call the PrintSubFolders procedure again, now passing in the reference to the current folder. This is where the recursion begins. The procedure is executed once again and now prints out the names of the next lower level of folders. And once again for each folder the PrintSubFolders is called for the now current folder. This will repeat until we reach a folder that has no sub folders. If that happened the current “instance” of the procedure will complete and procedure “instance” working on the higher level of the folder hierarchy resumes processing the next folder. This continues until all folders are processed.

The subLevel argument to the PrintSubFolders indicates the nesting depth of the folders and it is increments for each call to the procedure. It is just used to indent the output depending on the current nest level.

In my above implementation of PrintSubFolders I deliberately declared the parentFolder argument deliberately as Object. This makes it possible to alternatively pass in the root Namespace object to print the whole hierarchy of all folders in your Outlook profile.

Use Cases in Microsoft Access

The most common use case for recursion in Access is processing the controls collection of a form. E.g., you want to enable/disable all controls in a form. If the form contains a sub form you can employ recursion to process all controls in the sub form.

Another example is processing hierarchical data, like an organization structure or a manager-subordinate-relationship.

Conclusion

This might be an abrupt ending, but this is basically it. That’s all there is to recursion.

Once you understood the basic concept, recursion is fairly easy to use. You will not use recursion very often, but once you encounter a problem like the one in the example above, recursion is a simple and efficient (from the programmer’s point of view) solution. In most scenarios it is not required to write the more complex and more efficient (from the computer’s point of view) iterative implementation.

Video

This topic is also available as video on my YouTube Channel.

Share this article: