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
Search our website
We also send out useful tips in a monthly email newsletter ...
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 blog is part of our Excel VBA tutorial series of blogs (consider also booking on one of our Visual Basic for Applications courses).
|
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 ""
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.
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.
Parts of this blog |
---|
|
Some other pages relevant to the above blogs include:
Kingsmoor House
Railway Street
GLOSSOP
SK13 2AA
Landmark Offices
99 Bishopsgate
LONDON
EC2M 3XD
Holiday Inn
25 Aytoun Street
MANCHESTER
M1 3AE
© Wise Owl Business Solutions Ltd 2023. All Rights Reserved.