How to collect parameter data

There are many ways to get user input in VBA, including the use of forms and so on. However that is not going to be very good for our purposes, because there we will need to provide lots of customization capabilities for our roadmaps in the form of standard templates that can be used by specific groups of people, or for particular roadmap types. Trying to collect all that data through a form would be boring for the user, and unlikely to be consistent. The more options there are, the less likely it is that formats and outputs will be reproducible. Using ‘Parameter Sheets’ solves this problem. A parameter sheet is a block of data with headings along the top, labels down the side and values in the body that provide the parameter or option values that need to be communicated to our roadmap program.

Our cDataSet family provides the capability to both read multiple blocks of data within the same sheet, as well allow the addressing of that block by both heading (for the columns) and label (for the rows). So that gives us exactly the capability we need to read in a whole bunch of parameters, split into different sections.

Roadmap parameters

Lets not worry about the content for now, as we’ll develop that as we go along, but a parameter sheet might look something like this


You can see there are 3 sections which I’ve called roadmap colors, containers, and options. Using the cDataSet family, reading this stuff will be easy. Since we are focusing on recursion here, I wont go in to the details which you can find elsewhere, but here is how we will reference the parameters is shown below with updated calling procedures.

Referencing roadmap parameters

The key thing we are doing is creating a collection of cDataSet, which are managed in a class called cDataSets. This collection contains a cDataSet for each of the parameter sections, plus another for the data. A parameter section is just a specialized form of DataSet, with both row labels and column headings enabled ( as opposed to just column headings).

In this case, rather than creating and populating the cDataSet for the data ourselves we are letting cDataSets take care of it for us. In the example below we are going to end up with a collection of 4 cDataSet ; dSets(“data”), dSets(“roadmap colors”), dSets(“containers”) and dSets(“options”). If you followed along on the previous section how to access items in the cDataSet class, accessing parameters is just the same. See the debug.print examples below.

Accessing the value of a parameter then is pretty easy. The code below will return “Shale”, which is the value of the parameter ‘chart style’ in the parameter block ‘options’

dSets.DataSet("options").Value("chart style", "value")

Next steps

Now we have all the inputs we need to build a roadmap – namely input data and parameters. However we need to build a scale for our roadmap. We’ll get back to that in a later section – for now, we are going to go straight to making some shapes.

RoadMapper module version 3

You will find a starter parameter sheet to download in the downloads section. Copy this sheet to your workbook. Replace your roadmapper module with the code below, and check the debug.print output.

Option Explicit
Public Sub RoadMapper()
    Dim dSets As cDataSets, cc As cCell
    Dim rData As Range, rParam As Range
    Set rData = Range("InputData!$a$1:$e$1")
    Set rParam = Range("Parameters!$a:$g")
    ' get the data and the parameters
    Set dSets = New cDataSets
    With dSets
        .create
        .init rData, , "data"
        .init rParam, , , True, "roadmap colors"
        .init rParam, , , True, "containers"
        .init rParam, , , True, "options"
    End With
    '--check reading parameters worked----------------------------------------
    Debug.Print dSets.DataSet("options").Value("chart style", "value")
    Debug.Print dSets.DataSet("roadmap colors").Value("current", "shape")
    Debug.Print dSets.DataSet("containers").Value("frame", "comments")
    '-----------------------------------------------------------------
    With dSets.DataSet("data")
        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(dSets)
            End If
        End If
    End With
End Sub
Private Sub doTheMap(ByRef dSets As cDataSets)
    Dim scRoot As cShapeContainer, sc As cShapeContainer, dr As cDataRow
    ' this will be the root - the frame
    Set scRoot = New cShapeContainer
    scRoot.create scRoot
    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
            Else
                MsgBox sc.ID & " is a duplicate - skipping"
            End If
        Next dr
    End With
    ' sort out the parent/child relationships
    scRoot.SpringClean
    'debug to see what that all looks like
    scRoot.debugReport
End Sub

More topics about Roadmap Generation