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
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.
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:
Dim BreadCrumbs As String
Dim Person As String
'initially there aren't any breadcrumbs
BreadCrumbs = ""
'assume it's Wayne Rooney we're using as an example
'display accumulated breadcrumbs
Sub AccumulateBreadCrumbs(Id As Integer)
'accumulate breadcrumbs at a particular node
'find this person in the list of id numbers in column A
'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
'otherwise, add in details for the next person
The critical line is shown in bold above: this is the one which calls the macro recursively:
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|
25 Aytoun Street