This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

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. 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).

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.

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

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!

Parts of this blog
Recursive Programming Using Recursion to Display the Contents of a Folder Using Recursion to Show Hierarchical Breadcrumbs (this blog) Using Recursion to Solve Factorials

Some other pages relevant to the above blogs include:

This blog has 0 threads

Add a new post

Book any course as classroom, onsite or online training, or as one-to-one consultancy

Free resources

Paid services

Getting help

Where we are

Where you are

Online

Transparent reviews

Delivery of courses

Nicer to work with

Reliable and established

Our training courses

C#

Excel

MySQL

Power Apps

Power Automate

Power BI and DAX

Python

Report Builder

SQL

SQL Server

VBA Macros

Other training resources

Blogs

Exercises

YouTube tutorials

YouTube shorts

Test your skills

Consultancy

Courseware

Self-paced courses

Newsletters

Publications

Our training venues

London

Manchester

Across the UK

At your site

Online

Why we are different

Hundreds of published reviews

Small class sizes

Outstanding courseware

Online classroom

(Almost) no cancellations

Wise Owl trainers only

Genuine integrity

Invoices after training

30+ years of Wise Owl

Our top 100 clients

BLOGS BY TOPIC