Recap
We’ve read our data, and set up the parameters. The data structures are working according to our debug reports, and we have dipped our toe into recursion. Now to pull it all together and create some shapes. Since we have parent/child relationships, the idea here is that the parent shape will be big enough to accommodate all its children, grandchildren and so on. This is where recursion will be needed, to predict the height a shape is going to be.
What does it look like
Well- not too good. But notice that all the shapes including the frame are the right height. The starting points and widths we still need to work on – but that will be the subject of the next section. Lets look at how recursion has helped to calculate the heights of each shape, and the processing order ‘ me, then my children’ has ensured that the zOrder (what goes on top), is correct.
There it is. Needs a bit of work, but most of the tricky stuff is done. Lets start by looking at the height recursion
Calculating the height of each shape
To make this simpler, i have defined some properties that are calculated for each shape, using parameters taken from the parameter sheet as a base. These are combined using options from the parameter sheet to decide the height of any given shape.
The code for these properties is as follows – the properties paramXXXX that are referenced are taken from the parameter sheet, the full code will be at the end of this section as usual. The interesting ones from a recursion perspective are
- myExpansion – calls the recursive function biggestBranch(), whose purpose is to work down the layers of children from here to see the largest number of children any of my chidren have. The reason is that we have provided an option to suppress expansion of a parent if there is only one child. But this can only apply if none of the children have more than one child.
- mySpace- calls itself until the chain of children is completed. This is because mySpace for any item is in fact the space needed for all its children and all its childrens’ children and so on. Since the frame is treated as shape, the same way as all the others, mySpace for the frame is calculated to be the space needed for all the shapes, their children and so on.
This is a tricky concept, so read the code over and make sure you get it.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 |
' 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 Else 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 Else If MyExpansion Then ht = ht + MyGapBeforeChildren For Each sc In pChildren ht = ht + sc.MySpace Next sc ht = ht + MyGapAfterMe() Else ht = paramHeight + MyGapAfterMe() End If End If MySpace = ht End Property Public Property Get MyShapeHeight() As Single MyShapeHeight = MySpace - MyGapAfterMe End Property |
Making the shapes
Once we have the ability to figure out the height of any given shape, all thats left now (aside from scaling and formatting) is deciding where to plot them. Again we have to do this recursively because we have to plot all our children, childrens’ children etc, before we can move on to the next shape and know where to start plotting it.
Here is the code below. Notice that we create our own shape of myShapeHeight – which itself calls the recursive mySpace to figure out the space needed for the parent, then we call our selves for each of the children.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 |
' 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 all ok Else ' we have to calculate width and start point using dates relative to scale ' we'll replace these 100 when we figure out how .Width = 100 .Left = 100 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 |
Next steps
The next recursion topic will be sorting. Sorting is a classic instance of recursion. Our roadmapper is going to need the capability to sort the items in various ways to allow the most effective presentation. In the next section, we will cover how to sort a collection, using recursion.
Complete code so far – version 4
The code is getting rather large now. You can replace the Roadmapper module and the cShapeContainer class with the code below, or go to the downloadable items page and pick up version 4.
RoadMapper Module
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 |
Option Explicit Public Sub RoadMapper() Dim dSets As cDataSets Dim rData As Range, rParam As Range, rplot As Range Set rData = Range("InputData!$a$1:$e$1") Set rParam = Range("Parameters!$a:$g") Set rplot = Range("inputdata!a1") ' 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, 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 Else 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 scRoot.SpringClean ' plot the shapes and group them scRoot.makeShape scRoot.groupContainers 'debug to see what that all looks like scRoot.debugReport End Sub |
cShapeContainer Class
1 |
For help and more information join our community, follow the blog or follow me on Twitter.