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