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.

  1. Recursive Programming
  2. Using Recursion to Display the Contents of a Folder
  3. Using Recursion to Show Hierarchical Breadcrumbs (this blog)
  4. 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:

List of employees for a company

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:

List of breadcrumbs

The output from the program above shows the accumulated breadcrumbs.

Easy with recursion - very hard without it!

 

  1. Recursive Programming
  2. Using Recursion to Display the Contents of a Folder
  3. Using Recursion to Show Hierarchical Breadcrumbs (this blog)
  4. Using Recursion to Solve Factorials
This blog has 0 threads Add post