In the previous post, we were looking at how to export a hierarchical structure as XML, from a set of Excel tables, where the rows are hierarchically numbered (1, 1.2, 1.2.3, etc). The approach in our VBA is to build a hierarchical object model from our worksheet tables, and then ask the root of the model for its XML string. Both the building and the string generation proceed recursively down the structure. Note that this approach works for any hierarchical data – in our case it is software test definitions.
Having built a hierarchical object structure from our worksheet data, there are a number of other things we might want to do:
- check the test definitions for structural validity
- generate output in other formats, such as plain text (really plain, not XML)
I’ll talk a bit more about the first of these, here.
The basic idea is that of a class invariant – a property (which can be a conjunction of simpler properties) that always applies to any instance of the class. This idea is, of course, part of Bertrand Meyer’s Design By Contract approach.
Writing good invariants is hard, particularly for objects that are essentially reactive in their behavior. However, in our case here, we have a static hierarchical object structure, and there are some fairly obvious relationships between a node in our hierarchy and its child nodes:
- there is a least one child node
- the ID of the current node is a prefix of each child node ID (for example, 2.3 –> 2.3.1)
- the number of the table rows representing the current node = the sum of the rows for the child nodes + 1 row for the current node
These properties help to ensure that the test data worksheets are ‘well formed’ – with no extra or missing rows, and with proper hierarchical nesting. You can see an example of the last property in this screen shot:
- test condition 101 is represented by rows 3:9 = 7 rows
- test scenario 101.1 is represented by rows 4:6 = 3 rows
- test scenario 101.2 is represented by rows 7:9 = 3 rows
- so 7 = 3 + 3 + 1 row for the test condition itself
The Eiffel language has full support for invariants and other assertions, and it would be the job of the class constructor to establish the invariant. With VBA, we have to take a less rigorous approach, and have the invariant as a normal Boolean-returning function, which should return True any time after the Build procedure has been called. In Eiffel, failure of an invariant or other assertion raises an exception; in VBA, we’ll just put up an error MsgBox.
Here’s the invariant function for a test condition (level #2 in the hierarchy):
Function Invariant() As Boolean Invariant = _ tcond_tid.IsCondition And _ tcond_scenarios.Count > 0 And _ tcond_scenarios(1).ScenNum = "1" And _ ChildrenPartitionRows If Not Invariant Then MsgBox "Problem with Condition " & Me.Tid Dim ts As TScen For Each ts In tcond_scenarios Invariant = Invariant And IsChild(ts.Tid, Me.Tid) If Not Invariant Then MsgBox ts.Tid & " is not a child number of " & Me.Tid Invariant = Invariant And ts.Invariant Next End Function
The first part checks:
- the Condition’s ID is at the right level for a Condition
- there is at least one child Scenario, and that the numbering starts from 1
- that the child Scenarios cover all of the Condition’s rows (minus 1)
The second part iterates over the child Scenarios:
- checking that the child IDs extend the Condition’s ID (there’s an IsChild relationship between the two IDs)
- recursively evaluating the invariants for the child Scenarios (and thence recursively down the whole structure)
The ChildrenPartitionRows function is a simple iteration over the child Scenarios:
Function ChildrenPartitionRows() As Boolean 'Do the rows of my children + 1 = my rows? Dim childrenrows As Integer Dim ts As TScen For Each ts In tcond_scens childrenrows = childrenrows + ts.Rowcount Next ChildrenPartitionRows = (childrenrows = tcond_tblrowcount - 1) End Function
It might seem that we’re making extra work in writing these invariant and helper functions, but it’s a useful exercise in understanding how our worksheet tables represent a hierarchical structure, and what rules this implies for the worksheet/table layout. Further, we can make the checking directly accessible to a user via a Command Button: the handler simply builds the structure and calls the top-level invariant (on the Test Definition):
Private Sub CheckCommand_Click() 'Build the TDefn, then evaluate its Invariant (and recursively down the structure) Dim td As TDefn Set td = New TDefn td.Build If td.Invariant Then MsgBox "Check Complete" Else MsgBox "Some problems were found" End If End Sub
Note that we’re not being very informative about the precise location of an invariant failure. However, it would be simple enough to record an invariant failure on the object concerned, and then interrogate the structure subsequently to find out the ID of that object.
Finally, as an example of ‘plain text’ output (as opposed to XML), we can have objects provide a simple text representation of themselves – say just ID and Description, with vbTab and vbCrLf. This can be displayed in a TextBox (with property MultiLine = True), to give us a simple Table of Contents. Done within the handler above, this would be:
Worksheets("Front").TocText.value = td.Contents
For a simple one-level ToC, the Test Definition would simply iterate over the child Conditions, concatenating their individual Contents strings.