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.
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.
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'
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.
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.
Services > Desktop Liberation - the definitive resource for Google Apps Script and Microsoft Office automation > Recursion > Roadmap Generation >