Programming Excel with VBA and .NET

Implement and Test

Finally, you get to write some code! But since this code is going out to a wide audience, you need to do some extra work to make sure it functions correctly. In short, you need to test as you implement your code.

Each procedure that you write should have a unit test written to test it. Unit tests are procedures that call the functional procedures to make sure they work correctly. I use unit tests in all of the sample workbooks; for example, the TestMathFunctions unit test from ch05.xls tests the Inverse and CubeRoot procedures:

' Unit test
Sub TestMathFunctions(  )
    Dim result As Double, Value As Double, str As String
    Value = 42
    result = Inverse(Value)
    str = "The inverse of " & Value & " is " & result
    result = CubeRoot(Value)
    str = str & " and the cube root is " & result
    MsgBox str, , "Test Math Functions"
End Sub
 
' Functional code
Public Function Inverse(x As Double) As Double
    If x = 0 Then Inverse = 0 Else Inverse = 1 / x
End Function
 
' Functional code
Public Function CubeRoot(x As Double) As Double
    If x < 0 Then CubeRoot = 0 Else CubeRoot = x ^ (1 / 3)
End Function

I prefix the names of unit tests and the modules that contain them with Test to make their purpose clear. You’ll see that again and again throughout this book.

Unit testing is not the same as stepping through the functional procedures manually. Manual testing, sometimes called ad hoc testing , is an important way to find errors during development but it does not create a reproducible result. Unit ...