Excel Generate High Quality RoadMaps

Nothing illustrates an intention to change better than a good roadmap, especially where convergence is concerned. Normally, roadmaps are created manually in Powerpoint, and they very quickly get out of date, and are often confusing with varying formats and degrees of detail. With extensive use of recursion and classes, this article implements (and provides as a download), a tool that will take very simple Excel data and create standardized roadmaps that can be directly used in presentations.

Automatically roadmapping from Excel Data

Searching for a tool to automate the creation technology roadmaps at work, I quickly realized that there was really nothing out there that was flexible enough , or that could produce something of presentation quality that didnt need tweaking. This article is about how to implement one in Excel, and will provide a fully functional downloadable roadmap generator.

Roadmap format

For our example, we will use data that represents a Personal Technology roadmap, The roadmap will look like this, and will be constructed from grouped excel shapes that can be pasted into Powerpoint, Word or any other document that takes Excel Shapes. To resultant group of shapes can be pasted into a powerpoint as is, or as an bitmap as required.

Data Format

One of the design objectives was to keep the data format as simple as possible. Here is the data that created the roadmap. The first 4 columns are self explanatory, the 'target' column is the ID of the roadmap outcome (for example both smart and ipod -> iphone), and the custom column is used to identify a particular format to apply to that item, as opposed to the default.


The final thing needed before we dive into the code is a parameter sheet to define the colors, size, shapes, behaviors associated with the roadmap. The idea is that a parameter sheet represents a style that is used for a family of roadmaps so they all have the same look and feel. The parameter sheet looks like this. For the purposes of this article we will only briefly touch on the parameters needed to demonstrate the techniques, since this is not a tutorial on the provided example application. The concept here is that the parameters are divided into 'blocks', separated by a blank row - for example 'Containers' and 'Roadmap Colors' are both parameter blocks. The Row and Column headings are used to identify the position of a parameter value - so roadmap colors.recent.shape = 'Pentagon' in the example below. This is a straightforward technique I use in many projects, and the classes required to make this simple are provided in the downloadable code. Specifically, the 'Pentagon' example mentioned would be addressed as dSets("Roadmap Colors").Cell("recent","Shape").Value using the provided cDataSets family of classes, thus abstracting the parameter from its physical spreadsheet location. Occassionally the format of a cell (rather than a value), is used as a parameter, for example, roadmap items that have the Custom Format 'stream' specified in the custom column of their data would inherit their shape's format (fill,color,font,text alignment) from the cell referenced by the range dSets("Custom Bars").Cell("stream","Format").Where

Getting Started

The main code is rather straightforward, and makes use of a set of classes that abstract the data from its physical location on the sheet. Although these are not the topic of this article you can read more about them here. Once dSets.Create is executed, the physical location of both the parameters and the sheet data are no longer referenced. The main work is done in the custom class cRoadMapShape, which will be the main topic of this article. One thing you will see here is that recursion is used extensively. When you look at the layout of the roadmap and data, you can see that many items have a 'target'. This target is the ID of the item into which it converges. For the purposes of plotting a roadmap, the depth of this parent (target)/child relationship is unknown and variable. This is why recursion must be used. In the wrapper application below, scroot refers to the outer frame of the roadmap, which is the utilimate parent of all roadmap items.

Option Explicit
Public Sub RoadMapper()
  Dim dSets As cDataSets
  Dim rData As Range, rParam As Range, rplot As Range
' where the parameters are
  Set rParam = Range("Parameters!$a:$g")
' automatically find where the data is
  Set rData = getLikelyColumnRange
' plot on the same page as the data
  Set rplot = rData.Resize(1, 1)
' get the data and the parameters
  Set dSets = New cDataSets
  With dSets
      .init rData, , "data"
      .init rParam, , , True, "roadmap colors"
      .init rParam, , , True, "containers"
      .init rParam, , , True, "options"
      .init rParam, , , True, "custom bars"
    End With

  With dSets.DataSet("data")
     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
            Call doTheMap(dSets, rplot)
       End If
     End If
End With
End Sub
Private Sub doTheMap(ByRef dSets As cDataSets, rplot As Range)

Dim scRoot As cShapeContainer, sc As cShapeContainer, dr As cDataRow

' this will be the root - the frame
Set scRoot = New cShapeContainer
scRoot.create scRoot, , rplot, dSets

With dSets.DataSet("data")
' create for each datarow
   For Each dr In .Rows
     Set sc = scRoot.Find(dr.toString("ID"))
     If sc Is Nothing Then
       Set sc = New cShapeContainer
        sc.create scRoot, dr
        scRoot.Children.Add sc, sc.ID
        MsgBox sc.ID & " is a duplicate - skipping"
    End If
   Next dr
End With

' sort out the parent/child relationships and delete all the existing shapes on this sheet
' make the scale & sort
' plot the shapes and group them

End Sub

Recursion and the cShapeContainer class

Every roadmap shape, including the outer frame, is an instantiation of the cShapeContainer class. The full code of this class is in the downloable example, but here are some key methods that illustrate the necessary use of recursion. For example, deciding the height of a particular shape not only depends on its own height and gap between shapes, but it is also affected by each of its childrens' (and its childrens' children etc. etc.) height, since it needs to be big enough to show each child landing on it. This is accomplished through a series of methods as follows. The mySpace property below, will return the total height a particular cShapeContainer needs to be to accomodate its own height, plus the height of all its children and their descendents as well as any gaps or other formatting requirements inherited from the parameter sheet. Each element of space is implemented as property as below. Note the recursive nature of some of these properties as they work through the same properties for each of their children and further descendents.

' this is the gap after me
Public Property Get MyGapAfterMe() As Single
  MyGapAfterMe = paramGap
End Property

' the gap to leave before i plot my children if i have any
Public Property Get MyGapBeforeChildren() As Single
  If pChildren.Count > 0 Then
    MyGapBeforeChildren = paramGap
    MyGapBeforeChildren = 0
  End If
End Property

' how much to allow myself to expand
Public Property Get MyExpansion() As Boolean
  MyExpansion = paramExpansion
  If Not paramExpansion Then
    MyExpansion = biggestBranch() > 1
  End If
End Property

Public Property Get MySpace() As Single
  Dim sc As cShapeContainer
  Dim ht As Single
  If pChildren.Count = 0 Then
    ht = paramHeight + MyGapAfterMe
    If MyExpansion Then
      ht = ht + MyGapBeforeChildren
      For Each sc In pChildren
        ht = ht + sc.MySpace
      Next sc
      ht = ht + MyGapAfterMe()
      ht = paramHeight + MyGapAfterMe()
    End If
  End If
  MySpace = ht
End Property

Public Property Get MyShapeHeight() As Single
  MyShapeHeight = MySpace - MyGapAfterMe
End Property

' calculate the longest branch from here.
Public Function biggestBranch() As Long
  Dim sc As cShapeContainer
  Dim ht As Long, t As Long
     ht = pChildren.Count
     For Each sc In pChildren
       t = sc.biggestBranch()
       If t > ht Then
         ht = t
      End If
    Next sc
    biggestBranch = ht
End Function

Creating the shapes

Creating the shapes is the most complex activity, since we have to not only figure out their height, as discussed in the MySpace property but also deal with the width (calculated as a proportion of the total roadmap scale), their left position (as a proprtion of time passed from the beginning of the roadmap to the item's activation date), and of course apply all the formats (some are date based as per the default formatting in the 'roadmap colors' parameter block) or they might be inherited from a custom format. In addition the type of shape (pentagon, rectange etc) is variable. Some shapes also have a 'notch' at the beginning (for example a chevron). In this case, the length of the shape might need to be modified so that one chevron fits neatly into another. The additional width needed to achieve this is a function of the height of the item (basic trig), and so on it goes. Here is the function that adds the shape and makes the necessary adjustments through the myWidth and myLeft properties. It is of course also recursive since the z-Order between parents and children needs to be carefully preserved.

' this is the most complex part - creating the shapes of the correct size and placing them in the right spot
Public Sub makeShape(Optional xTop As Single = -1)
    Dim sc As cShapeContainer, s As Shape, xNextTop As Single

    ' this would be the default call - place the frame at the place defined in the parameters
    If xTop = -1 Then
        xTop = paramFrameTop
    End If
    ' make a shape
    Set pShape = Plot.Worksheet.Shapes.AddShape(paramShapeType, paramFrameLeft, xTop, paramFrameWidth, MyShapeHeight)
    ' apply the format asked for in the parameters and add a label
    shapeTemplate pShape, paramShapeTemplate, Text
    With pShape
        ' we are going to group the shapes later - this is so we can find them
        .Name = nameStub & .Name
         If pscType = sctframe Then
            ' width and left are the default

            ' we have to calculate width and start point using dates relative to scale
            .Width = myWidth
             .Left = myLeft
        End If

    End With
    ' this is where it gets tricky
    xNextTop = pShape.Top
    If MyExpansion Then
        ' if we are allowing expansion of targets then need to make a gap to accommodate my children
        xNextTop = xNextTop + MyGapBeforeChildren
    End If
    For Each sc In pChildren
        ' make a shape for each of my children
        sc.makeShape xNextTop
        ' figure out how much space my child needed and start the next one after it
        xNextTop = xNextTop + sc.MySpace
    Next sc

End Sub

'calculates my width relative to root width using start/finish dates
Private Property Get myWidth() As Single
    If pRoot.Shape Is Nothing Then
        myWidth = paramFrameWidth
        myWidth = pRoot.Shape.Width * Duration / pRoot.Duration
    End If
End Property

'calculates my left start relative to root width using start/finish dates
Private Property Get myLeft() As Single
    If pRoot.Shape Is Nothing Then
        myLeft = paramFrameLeft
        myLeft = pRoot.Shape.Left + (Activate - pRoot.Activate + 1) / pRoot.Duration * pRoot.Shape.Width
     End If
End Property


Roadmap generation turned out to be a pretty complex problem, maybe better achieved in a language other than VBA. However, as usual it rose to challenge. Please download the roadmapper example. It's called Roamapgenerator.xlsm. You will find the full code of all the classes that have been discussed here as well as an example wrapper application. As always, you can use the code as you wish for non-commercial use. I welcome feedback, suggestions, and improvements. I did not have space in this article to cover all the topics that I came across in the development of this tool, but if there is demand I may cover some of those, including some new features such as being able to update the source data through shape manipulation and creating area graphs of costs assigned to roadmap items over time in a future article.

By bruce mcpherson   Popularity  (10206 Views)