Writing recursive programs in VBA
Part two of a four-part series of blogs

In certain cases in VBA (displaying the contents of folders, listing hierarchical data) writing a program which calls itself is by far the easiest way to go - this blog gives worked examples of 3 such recursive programs.

  1. Recursive Programming
  2. Using Recursion to Display the Contents of a Folder (this blog)
  3. Using Recursion to Show Hierarchical Breadcrumbs
  4. Using Recursion to Solve Factorials

This blog is part of our Excel VBA tutorial series of blogs (consider also booking on one of our Visual Basic for Applications courses).

Posted by Andy Brown on 20 February 2012

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Using Recursion to Display a Folder's Contents

This program uses FileSystemObjects without further explanation - you may want to refer to my blog on working with files and folders in VBA.

What this Program is Meant to do

The aim of this exercise is to list out the name and path of every single file in a folder or in any of its subfolders.  Here's what we'll be aiming to list:

Folder and subfolders

We'll aim to print out the names of all files in the Wise Owl folder, and in all of its subfolders (however many levels nested these may be).

 

Part of the output our program could produce in the Immediate window might look like this:

Immediate window output

The recursive program will list out all files found in all folders.

The Subroutine to List Out a Folder's Files

At its heart, our program will contain a subroutine which lists out all of the files in a given folder, and then goes on to loop over all of that folder's subfolders:

Sub ListFiles(fol As Folder)

'each file within the folder

Dim fil As File

'each subfolder within the folder

Dim subfol As Folder

'hidden files may cause problems

On Error Resume Next

'list out all the files in this folder ...

If fol.Files.Count > 0 Then

Debug.Print ""

Debug.Print "FILES IN " & UCase(fol.Path); ""

Debug.Print ""

End If

For Each fil In fol.Files

Debug.Print fil.Path

Next fil

'now list out all of the files in the subfolders

For Each subfol In fol.SubFolders

ListFiles subfol

Next subfol

'reset error trapping to default

On Error GoTo 0

End Sub

I've shown the most important line in bold: the one which calls the subroutine ListFiles again, using a subfolder of the main folder as its argument.

The Routine to Start the Process

Finally, we need the code which will start the process off, by calling the ListFiles routine shown above with a folder as an argument:

Option Explicit

'need to reference Microsoft Scripting Runtime library

Dim fso As New FileSystemObject

Sub StartProcess()

'the folder whose contents we'll list

Dim fol As Folder

Set fol = fso.GetFolder("C:\Wise Owl\")

'start listing files and subfolder

ListFiles fol

End Sub

If you run the StartProcess routine above, it should list out all of the files in the Wise Owl folder and all of its subfolders!

Note that stepping through programs like this can be a nightmare, since it is hard to know which instance of the ListFiles routine you are currently debugging.

  1. Recursive Programming
  2. Using Recursion to Display the Contents of a Folder (this blog)
  3. Using Recursion to Show Hierarchical Breadcrumbs
  4. Using Recursion to Solve Factorials
This blog has 0 threads Add post