This section shows how to build a roadmapper. You can of course download the finished project
if you don't want to follow along with the development and just need a ready made one. It's the workbook called roadmapper.xlsm and produces output like this from a simple Excel data table. Try it out
, then come back here to see how it was made if you are interested. Here is the user guide - How to use the Excel Roadmapper
Roadmapping is a great topic to illustrate recursion
. The problem with roadmaps is that you dont know how many things will evolve into other things, or how items will be related to each other. You also probably need to do some sorting - another good recursion candidate, as well to make sure that items that have the same target state are plotted next to each other. See the example below.
Before we get started, you might want to become familiar with a few supporting procedures and classes that are covered elsewhere, but that we are going to use to generate this roadmapper tool, starting with cDataSet
which we will use to read our data into a structure we can work with, without getting distracted into where it is on your spreadsheet.
Google apps script
Before diving into this you may want to know that there is also an apps script version of this here
Here is our input data - very straightforward.
Essentially our activate and deactivate dates determine where the items appear on the roadmap, and the target determines what any particular item - the child - evolves to - the parent. We will worry about formatting later. We will use cDataSet to get hold of the data above, so download the cDataSet workboo
k, create a module called roadmap with the procedure below, and a worksheet called InputData with the data shown above.
Public Sub RoadMapper()
Dim dSet As cDataSet
Set dSet = New cDataSet
' just need to provide the range where data headings are
If .Where Is Nothing Then
MsgBox ("No data to process")
' check we have fields we need
If .HeadingRow.Validate(True, "Activate", "Deactivate", "ID", "Target", "Description") Then
Private Sub doTheMap(dSet As cDataSet)
Roadmap data structure
We are going to define and use a class
called cShapeContainer. There will be one of these for each roadmap item - so in our example - 10, plus one for the frame that contains all the shapes. These 11 objects will be organized in a parent/ child relationship such that every object that is a target will have its 'precedents' as children.
This can be represented grpahically as below, using the data items ID.
Loading the data
Lets examine our first shot at the cShapeContainer class. Insert a new class and call it cShapeContainer. You'll notice that there are minimal properties and methods for now. We'll add to that later. Note that I've also added a method called debugReport. When dealing with recursion, its usually a good idea to be able report on what your tree looks like.In fact this method gives us the first look at recursion - it calls itself. This is the technique we will look at in more detail.
Public Enum scTypeS
Private pscType As scTypeS
Private pShape As Shape
Private pDataRow As cDataRow
Private pChildren As Collection
Public Function create(Optional pr As cDataRow = Nothing)
If pr Is Nothing Then
pscType = sctframe
pscType = sctdata
Set pDataRow = pr
Set pChildren = New Collection
Public Property Get scType() As scTypeS
scType = pscType
Public Property Get Shape() As Shape
Set Shape = pShape
Public Property Set Shape(p As Shape)
Set pShape = p
Public Property Get Children() As Collection
Set Children = pChildren
Public Property Get Text() As String
If pDataRow Is Nothing Then
Text = "Roadmap Frame"
Text = pDataRow.Value("Description")
Public Sub debugReport()
Dim sc As cShapeContainer
If pChildren.Count = 0 Then
Debug.Print "--Nochildren:" & Text
Debug.Print "Me:" & Text
Debug.Print "Children of:" & Text
For Each sc In pChildren
Now that we've created the class, we can modify our program to populate it. You can see that we have created a frame, plus a container for every other shape that for now we have associated with the frame. So if we were to plot this now, we would see every item as a child of the frame.
Private Sub doTheMap(ByRef dSet As cDataSet)
Dim scRoot As cShapeContainer, sc As cShapeContainer, dr As cDataRow
' this will be the root - the frame
Set scRoot = New cShapeContainer
' create for each datarow
For Each dr In dSet.Rows
Set sc = New cShapeContainer
You will see that I've called a report at the end to see that everything is as expected. Here is the output, using our recursive debugReport.
Children of:Roadmap Frame