Testing VBA Code – Part 4

In Part 3, we looked at testing a worksheet as an Excel object plus associated code (in the sheet module). This idea is similar to that in object-oriented testing practice, where the unit of testing is a single object (instance of a class), or a group of closely related objects.

In a previous set of postings on Classes, we defined a couple of Class modules, Job and Resource, instances of which acted as adapters on to a worksheet (rather than having operational code in the sheet module itself). This example also used a List class as a ‘helper’ – basically a linked data structure with Add and Remove operations. So, how do we test such Classes?

For simplicity, let’s stick with the List class (since there’s no persistence to the worksheet, as there was with Job and Resource classes).

The test ‘script’ basically consists of:

  • create an instance of the class
  • perform operations on the object
  • check the class invariant after creation and after each operation
  • check the post-condition of each operation
  • use the ‘state query’ functions or properties (e.g. First, Count) to inspect the object.

For collection-like objects, there are often sequences of operations that can be checked for ‘circularity’ – e.g. li.Add(val).Last = val.

Here’s the ‘short form’ for the class – that is, just the member headers plus assertions:

'Warning: not complete VBA syntax!

Dim liHead As ListItem
Dim liTail As ListItem
Dim licount As Integer
Const separator As String = "/"

Public Function Invariant() As Boolean
    Invariant = _
        (licount = 0 And liHead Is Nothing And _
			liTail Is Nothing) Or _
        (licount = 1 And Not liHead Is Nothing And _
			liHead Is liTail) Or _
        (licount > 1 And Not liHead Is Nothing And _
            Not liTail Is Nothing And Not liHead Is liTail)
End Function

Public Property Get Count() As Integer

Function IsEmpty() As Boolean

Function First() As Variant
    If pre Then AssertPre "List.First", (Not IsEmpty)

Function Last() As Variant
    If pre Then AssertPre "List.Last", (Not IsEmpty)

Sub SetLast(val)
    If pre Then AssertPre "List.SetLast", (Not IsEmpty)
    'post: GetNth(Count) = val

Private Function NthItem(n As Integer) As ListItem

Function GetNth(n As Integer) As Variant
    If pre Then AssertPre "List.GetNth", _
		     (Count > 0 And n > 0 And n <= Count) 

Sub SetNth(n As Integer, val)
    If pre Then AssertPre "List.SetNth", _
		     (Count > 0 And n > 0 And n <= Count)
    'post: GetNth(n) = val 

Sub Add(val)
       'post: count = old count + 1 

Sub Remove()
'post: (old count > 0 and count = old count - 1) Or
'        (old count = 0 and count = 0)

Function ToString() As String
'post: Len(ToString) >= count * Len(separator)

Notes:

  • ListItem is just a Value + NextItem pair.
  • The Invariant is given as a function.
  • The post-conditions are just comments: they will be evaluated in the test code. ‘old’ refers to the value of an expression before execution of the operation.
  • In line with the suggestion in Part 3, the preconditions are given as executable code (‘pre’ is a global switch for evaluation).

Following our general approach, the test code goes in an ordinary module ‘TestList’, corresponding to the Class module. The test module contains a single Run procedure (obviously you could break this down into sub-tests). TestSetup and TestWrapup are calls to general facilities in a ‘Test’ module.

The code is verbose of necessity, but pretty simple and repetitive:

Public Sub Run()
    TestSetup "List"

    Dim li As List    'the object under test
    Dim oldcount As Integer
    Dim val As Integer, val2 As Integer   'some values

    Set li = New List

    AssertInv li
    AssertEqual li.Count, 0
    oldcount = li.Count
    '--------------------------------------------
    val = 3
    li.Add val

    AssertInv li
    AssertEqual li.Count, oldcount + 1      'post Add
    AssertEqual li.First, val
    AssertEqual li.Last, val
    AssertEqual li.GetNth(li.Count), val
    oldcount = li.Count
    '--------------------------------------------
    val = 4
    li.SetNth 1, val

    AssertInv li
    AssertEqual li.GetNth(1), val           'post SetNth
    AssertEqual li.ToString, "/" & val
    '--------------------------------------------
    val = 5
    li.SetLast val

    AssertInv li
    AssertEqual li.GetNth(li.Count), val    'post SetLast
    '--------------------------------------------
    val2 = 6
    li.Add (val2)

    AssertInv li
    AssertEqual li.Count, oldcount + 1      'post Add
    AssertEqual li.First, val
    AssertEqual li.Last, val2
    AssertEqual li.GetNth(1), val
    AssertEqual li.GetNth(li.Count), val2
    AssertEqual li.ToString, "/" & val & "/" & val2
    oldcount = li.Count
    '--------------------------------------------
    li.Remove

    AssertInv li
    AssertEqual li.Count, oldcount - 1     'post Remove
    AssertEqual li.First, val
    AssertEqual li.Last, val
    AssertEqual li.GetNth(1), val
    AssertEqual li.ToString, "/" & val
    oldcount = li.Count
    '--------------------------------------------
    li.Remove

    AssertInv li
    AssertEqual li.Count, oldcount - 1      'post Remove
    AssertEqual li.Count, 0
    AssertEqual li.ToString, ""
    oldcount = li.Count
    '--------------------------------------------
    li.Remove

    AssertInv li
    AssertEqual li.Count, 0
    AssertEqual li.ToString, ""

    TestWrapup
End Sub

AssertInv is just another general procedure in module Test. If Invariant is a standard name, then we just need to pass in the object:

Public Sub AssertInv(obj As Object)
    Dim msg As String

    If Not obj.Invariant Then
        msg = "*** Inv " & TypeName(obj) & " failed"
        Debug.Print (msg)
        errcount = errcount + 1
    End If
End Sub

Now, we haven’t investigated the question of how much testing is enough testing. There’s no simple answer, of course, but having a standard approach and framework into which to put testing code makes it easy to do testing alongside development, and to do both in small increments. Any change or extension to the List class is matched by changes or extensions to TestList.Run. If we find a bug that’s not been picked up by the tests, then we add some tests so that it is, then we fix the bug. We come back to the project after a year, say, and we can immediately discover whether it’s working or not (bad case of Software Rot…). It really can be most productive.

Finally, a doff of the hat towards the Eiffel language, the only one that fully integrates and supports assertions: preconditions, postconditions and invariants. These are part of the primary code, and can (optionally) be evaluated at run-time. This almost removes the need for separate unit testing code, allowing you to concentrate on system-level or architectural testing. Meanwhile, back with VBA …

Advertisements

2 Responses to “Testing VBA Code – Part 4”


  1. 1 Peder Schmedling April 30, 2009 at 5:36 am

    Thank you for helping a self-thought VBA programmer how to test code in a structured and professional way.


  1. 1 Roll-over Periodic Workbook – Part 2 « Roy MacLean’s VBA Blog Trackback on May 21, 2009 at 5:40 pm

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s




April 2009
M T W T F S S
« Mar   May »
 12345
6789101112
13141516171819
20212223242526
27282930  

%d bloggers like this: