Quick Links

Other stuff

Ask questions, get help, comment, collaborate

Site owners

  • Bruce Mcpherson
Services‎ > ‎Excel Liberation‎ > ‎Recursion‎ > ‎

Roadmap Generation

Introduction to roadmapping get it now

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

This project illustrates a number of important topics such as jSon and Dynamic Forms, Recursion and Hiding data in Excel Objects

 

Developing a 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 visualization

Before diving into this you may want to know that there is also a web enabled google visualization gadget version of this roadmapper available. It is not quite as feature rich as the Excel version, but you can of course include it in your web site or google docs. If you prefer javaScript to VBA, you want to participate in this open Source project, or you just want to see how it is done,  visit http://www.pepada.com/tools

Input data

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 workbook, 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
    With dSet
        .populateData Range("InputData!$a$1:$e$1")
        
        If .Where Is Nothing Then
            MsgBox ("No data to process")
        Else
            ' check we have fields we need
            If .HeadingRow.Validate(True, "Activate", "Deactivate", "ID", "Target", "Description") Then
                Call doTheMap(dSet)
            End If
        End If
    End With

End Sub

Private Sub doTheMap(dSet As cDataSet)

End Sub

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.
'
'cShapeContainer Class

Public Enum scTypeS
     sctdata
     sctframe
End Enum

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
    Else
        pscType = sctdata
    End If
    Set pDataRow = pr
    Set pChildren = New Collection
End Function
Public Property Get scType() As scTypeS
    scType = pscType
End Property
Public Property Get Shape() As Shape
    Set Shape = pShape
End Property
Public Property Set Shape(p As Shape)
    Set pShape = p
End Property
Public Property Get Children() As Collection
    Set Children = pChildren
End Property
Public Property Get Text() As String
    If pDataRow Is Nothing Then
        Text = "Roadmap Frame"
    Else
        Text = pDataRow.Value("Description")
    End If
End Property
Public Sub debugReport()
    Dim sc As cShapeContainer

    If pChildren.Count = 0 Then
        Debug.Print "--Nochildren:" & Text
    Else
        Debug.Print "Me:" & Text
        Debug.Print "Children of:" & Text

        For Each sc In pChildren
            sc.debugReport
        Next sc
    End If

End Sub

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
    scRoot.create
    
    ' create for each datarow
    For Each dr In dSet.Rows
        Set sc = New cShapeContainer
        sc.create dr
        scRoot.Children.Add sc
    Next dr
    
    'debug
    scRoot.debugReport
End Sub

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. 

Me:Roadmap Frame
Children of:Roadmap Frame
--Nochildren:Roller Skates
--Nochildren:Bike
--Nochildren:Motor Scooter
--Nochildren:Motor Bike
--Nochildren:Smart Car
--Nochildren:Truck
--Nochildren:SUV:4x4
--Nochildren:Wheelbarrow
--Nochildren:Transport Plan
--Nochildren:Money

Since everything is currently a child of Frame, then in fact we could have reported this without recursion - but this is the last time it would work. Next we are going to move these around so the children are associated with the correct parents.