563 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owls only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
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.
This program uses FileSystemObjects without further explanation - you may want to refer to my blog on working with files and folders in VBA.
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:
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:
The recursive program will list out all files found in all folders.
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 "FILES IN " & UCase(fol.Path); ""
For Each fil In fol.Files
'now list out all of the files in the subfolders
For Each subfol In fol.SubFolders
'reset error trapping to default
On Error GoTo 0
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.
Finally, we need the code which will start the process off, by calling the ListFiles routine shown above with a folder as an argument:
'need to reference Microsoft Scripting Runtime library
Dim fso As New FileSystemObject
'the folder whose contents we'll list
Dim fol As Folder
Set fol = fso.GetFolder("C:\Wise Owl\")
'start listing files and subfolder
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.
|Parts of this blog|
25 Aytoun Street