I am supporting CandidateX

CandidateX is a startup that focuses on creating inclusion-focused hiring solutions, designed to increase access to job opportunities for underestimated talent. Check them out if you have a few minutes to spare. They need visibility!

Introduction to roadmapping

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 FormsRecursion 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 apps script

Before diving into this you may want to know that there is also an apps script version of this here.

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 SubPrivate 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     sctframeEnd Enum Private pscType As scTypeSPrivate pShape As ShapePrivate pDataRow As cDataRowPrivate pChildren As CollectionPublic 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 CollectionEnd FunctionPublic Property Get scType() As scTypeS    scType = pscTypeEnd PropertyPublic Property Get Shape() As Shape    Set Shape = pShapeEnd PropertyPublic Property Set Shape(p As Shape)    Set pShape = pEnd PropertyPublic Property Get Children() As Collection    Set Children = pChildrenEnd PropertyPublic Property Get Text() As String    If pDataRow Is Nothing Then        Text = "Roadmap Frame"    Else        Text = pDataRow.Value("Description")    End IfEnd PropertyPublic 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.debugReportEnd 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 FrameChildren 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. For help and more information join our community,  follow the blog or  follow me on Twitter.