BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
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.
- Recursive Programming
- Using Recursion to Display the Contents of a Folder
- Using Recursion to Show Hierarchical Breadcrumbs (this blog)
- 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 Show Hierarchical Breadcrumbs
A title like that for a blog deserves a bit of explanation!
Hierarchical Data
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
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
A Recursive Program in VBA to List Out Employees
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!
- Recursive Programming
- Using Recursion to Display the Contents of a Folder
- Using Recursion to Show Hierarchical Breadcrumbs (this blog)
- Using Recursion to Solve Factorials