Recursive functions in VBA

 May 29, 2015

In programming, you use functions for two purposes. You either want to reuse a piece of code over and over again, therefore you encapsulate it as a function, or you may want to keep that piece of code separate from the main code for organisation purposes pretty much the same way an Appendix works in a document.

Whatever the reason, you normally call a function from another area outside the function. Once the function finishes its task the control goes back to the caller. However there are occasions when the caller is not outside the function but inside it, therefore the function calls itself repeatedly. These functions are called recursive functions and those occasions almost always occur when you are applying the function to a hierarchical structure of some sort and the hierarchy has a parent child relationship.

Here are a few examples:

  • Get the details of each employee and their supervisors
  • Get the names of each person and their children
  • Get the name of each folder and subfolders
  • Get the properties of each HTML tag and its child tag in an HTML document
As you can see, they all have two things in common:
  1. They all have parent-child hierarchical nature
  2. The object are all of the same type; in the first and second case they are Persons, in the third case they are all folders and the fourth one they are all HTML tags

These two conditions are important when considering if it is appropriate to use a recursive function and when writing a recursive function, one thing you should always remember; you have to have a provision to end the call, otherwise the function calls itself indefinitely and gets trapped into an infinite loop.

So here I am going to give you an example to understand it a bit more. Let’s take the third example above, i.e. list all the files in a folder and its subfolders. Here’s a simple code to list the file names in a folder called folderName;

Recursive functions in VBA

But how about the list of files in the subfolders of folderName? Well, if we have the name of each subfolder we could call this function repeatedly passing the name of the subfolder, but the problem is that we can only get the name of a subfolder when we are actually inside its parent folder. Therefore, the name of the subfolders are only revealed inside the function above. So the function is going to call itself repeatedly. At each call, it finds a list of subfolders first and then calls itself to list the relevant file names. So, below, I have the same code only added the segment in bold to grab the subfolders in a loop call the function again to list their fileNames;

  Recursive functions in VBA

You will appreciate that the function naturally keeps going forward as deep as there are subfolders for a particular folder. When there are no subfolders for that folder then the bolded segment will not run and therefore there will be no more calls and the function works itself backwards listing only the file names and terminating that level.

Here’s the complete code:

Recursive functions in VBA

The If statement is necessary because the function ShowFilesInFolder expects a “” at the end of its parameter folderName.

Hope this has helped you understand recursive functions in general. Happy coding!

How do your Excel skills stack up?   

Test Now  

About the Author:

Cyrus Mohseni  

Having worked within the education and training industry for over 15 years as well as the IT industry for 10 years, Cyrus brings to New Horizons a wealth of experience and skill. Throughout his career he has been involved in the development and facilitation of numerous training programs aimed at providing individuals with the skills they require to achieve formal qualification status. Cyrus is a uniquely capable trainer who possesses the ability to connect with students at all levels of skill and experience.

Read full bio