564 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 three 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).
|
A title like that for a blog deserves a bit of explanation!
For the purposes of this blog, 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!
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.