Read our blogs, tips and tutorials
Try our exercises or test your skills
Watch our tutorial videos or shorts
Take a self-paced course
Read our recent newsletters
License our courseware
Book expert consultancy
Buy our publications
Get help in using our site
550 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owl trainers 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 ...
Written by Andy Brown
In this tutorial
The dictionary definition of Recursion says it all:
Recursion (n.) - see under "Recursion"
And if you didn't get (or like) that joke, here's a real definition: recursive programming is when you write a program which calls itself.
In practice there are few occasions when you'll actually use recursion - but when you do need it, it's nearly indispensable! The examples this tutorial will show are:
Example | Notes |
---|---|
Folder contents | Recursively looping over folders and subfolders |
Creating breadcrumbs | Creating menu breadcrumbs for hierarchical data |
Calculating a factorial | Calculating the factorial of a number |
Of these, it would be much harder to write the first two examples without using recursion.
This program uses FileSystemObjects without further explanation - you may want to refer to my tutorial 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.
For the purposes of this tutorial, I've defined hierarchical data as any database where each record points to its immediate parent in the hierarchy, which is stored in the same table. Here's an example, which should make things clearer:
For this list of employees, the record for each person shows who is their immediate line manager. So Wayne Rooney is managed by Basil Brush (id 5), who is managed by Simon Templar (id 1), who is managed by Alastair Campbell (id 6).
Storing data like this is an elegant and compact way to store any hierarchy, but it's hard to get the information back out without recursion.
Breadcrumbs provide an audit trail of where you are in a website or system. For example, if you're trying to buy a spare wheel for your bicycle, the trail might read:
Sport and Leisure --> Cycling --> Parts --> Wheels and tyres
For our example above, we want to produce the following breadcrumbs:
Alastair Campbell --> Simon Templar --> Basil Brush --> Wayne Rooney
Here is a program which would do the trick. To keep it simple, I've omitted any checks or error trapping:
Option Explicit
Dim BreadCrumbs As String
Sub ShowBreadcrumbs()
Dim Person As String
'initially there aren't any breadcrumbs
BreadCrumbs = ""
'assume it's Wayne Rooney we're using as an example
AccumulateBreadCrumbs 4
'display accumulated breadcrumbs
MsgBox BreadCrumbs
End Sub
Sub AccumulateBreadCrumbs(Id As Integer)
'accumulate breadcrumbs at a particular node
'find this person in the list of id numbers in column A
Columns("A").Find(What:=Id, Lookat:=xlWhole).Select
'add in their name from column B
If Len(BreadCrumbs) > 0 Then BreadCrumbs = " --> " & BreadCrumbs
BreadCrumbs = ActiveCell.Offset(0, 1).Value & BreadCrumbs
'find their manager (or stop if they don't have one)
Dim ManagerId As Integer
ManagerId = ActiveCell.Offset(0, 2).Value
If ManagerId = 0 Then
'if this person is ultimate bossman, stop
Exit Sub
Else
'otherwise, add in details for the next person
AccumulateBreadCrumbs ManagerId
End If
End Sub
The critical line is shown in bold above: this is the one which calls the macro recursively:
AccumulateBreadCrumbs ManagerId
When you run the ShowBreadcrumbs subroutine above, you should get:
The output from the program above shows the accumulated breadcrumbs.
Easy with recursion - very hard without it!
The factorial of a number is written (in the UK at any rate) as n!. For example:
5! = 5 x 4 x 3 x 2 x 1 = 120
3! = 3 x 2 x 1 = 6
Notice that for any n:
n! = n * (n-1)!
To the experienced programmer, this is crying out for a program which will call itself recursively.
The program below will show one of two things:
Output if the factorial isn't too big | Output if the number is too large |
Here is the suggested program. First we set some global variables (including the seed whose factorial we'll calculate):
Option Explicit
'allow fairly large numbers
Dim CurrentFactorial As Long
'detect if this wasn't enough
Dim IfOverflow As Boolean
'number whose factorial we'll calculate
Const Seed As Integer = 10
Next, we'll write our subroutine which calls itself recursively:
Sub GetFactorial(n As Integer)
'if we've had an overflow, no point continuing
If IfOverflow Then Exit Sub
'if an error happens, set flag and exit
On Error GoTo Overflow
'if we've got down to 1, exit
If n <= 1="">Then Exit Sub
'multiply by this number and recursively get the next factorial down
CurrentFactorial = CurrentFactorial * n
GetFactorial (n - 1)
'finished - exit
Exit Sub
Overflow:
IfOverflow = True
End Sub
The important line is the one I've put in bold: the GetFactorial subroutine calls itself, with a number one less each time.
Finally, we need to write a program to call the GetFactorial subroutine in the first place, and to display its result:
Sub ShowFactorials()
'initially there is no overflow problem ...
IfOverflow = False
'... and factorial equals 1
CurrentFactorial = 1
'call routine to find factorial
Call GetFactorial(Seed)
'if a problem say so - otherwise, return answer
If IfOverflow Then
MsgBox "Too large a number generated - try something smaller"
Else
MsgBox "Factorial of " & Seed & " is " & CurrentFactorial
End If
End Sub
When you run this last subroutine, you'll see the factorial of the number you chose as a seed!
You can learn more about this topic on the following Wise Owl courses:
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 2024. All Rights Reserved.