Unit testing VBA code with the VBA-Unit-Tester addin.

Unit tests are one of the best ways to ensure code works, runs correctly, and continues to work over time. The VBA-Unit-Tester addin is a free, open source module that provides a unit testing framework for VBA without any need to install software. The code can be imported into the Microsoft VBA IDE, saved as an addin, and used in any project.

I’ll demonstrate how to create a series of tests against the Matrix object in the VBA-Math-Objects library. This will require creating a TestCase and the TestRunner configuration.

The VBA-Unit-Tester contains a stub TestCase class that we will use as a starting point.

Implements iTestCase

Dim TestCase As iTestCase

' Constructor: Class_Initialize
' Initialize class members
Private Sub Class_Initialize()
    Set TestCase = CreateTestCase()
End Sub

' Sub: iTestCase_Setup
' Add code here that will be run before every test.
' For example, create objects, populate data.
Sub iTestCase_Setup()

End Sub

' Sub: iTestCase_Setup
' Add code here that will be run after every test.
' For example, remove a worksheet, close a connection.
Sub iTestCase_TearDown()
End Sub

' Sub: iTestCase_RunTest
' Run a specific test.
Public Sub iTestCase_RunTest(Test As String, Optional clsObj = Nothing)
    TestCase.RunTest Test, Me
End Sub

' Sub: iTestCase_RunAllTests
' Run all tests.
Public Sub iTestCase_RunAllTests(Optional ByVal clsObj = Nothing)
    TestCase.RunAllTests Me
End Sub

' Sub: MyFirstTest
' All test subs should end with "Test" and include at least one assertion.
Sub MyFirstTest()
    AssertTrue(2 = 1 + 1, "AssertTrue that one plus one equals two")
    AssertEquals(1 + 1, 2,  "AssertEquals that one plus one equals two")
End Sub

All we need to do is replace the code in MyFirstTest with something more applicable to what we want to test.

We’ll create an easy test. I’ll test that multiplying an identity matrix by zero will produce a matrix of all zeroes. Since the VBA-Math-Objects library has Factory methods to produce different types of matrices, it has a ScalarMultiply() function, and an isEqual() function, we can tie them together in our test.

Sub MyFirstTest()
    Dim M as Matrix
    Set M = Identity(3)

    Dim Test as Matrix
    Set Test = M.ScalarMultiply(0)

    Dim Expected As Matrix
    Set Expected = ScalarMatrix(0, 3, 3)
AssertTrue(Test.isEqual(Expected), "Test that Multiplying a matrix by zero creates a zero matrix.")
End Sub

That is all we need in our test case. You can change the name of the sub, something like ScalarMultiplyZeroTest() might be better. As long as the name of the sub ends in “Test” you will be fine.

Now that we have the test case made, we need to be able to run it. For this we use a TestRunner configuration class. We will again start with the stub class in the examples directory. This configuration will run every test in every test case, and display a summary of what has run in the end and any failing assertions.

Implements iTestableProject

Dim bOutputFailures As Boolean

Dim TestRunner As TestRunner

' Constructor: Class_Initialize
' Configure the test runner
Private Sub Class_Initialize()
    ' Print each failure as it occurs
    bOutputFailures = True
End Sub

Public Property Get iTestableProject_GetOutputFailures()
    iTestableProject_GetOutputFailures = bOutputFailures
End Property

Public Function iTestableProject_GetTestCases()
    ' List all TestCase classes in this array.
    iTestableProject_GetTestCases = Array(New TestCaseStub)
End Function

' Function: Run
' Perform all the actions.
Public Function iTestableProject_Run()
    Configure
    Run
    Report
End Function

Private Sub Configure()
    Set TestRunner = CreateTestRunner()
    
    ' Pass this object to the runner so it has access to the necessary data
    Set TestRunner.TestConfig = New TestConfigStub
End Sub

Private Sub Run()
    ' We want to run all Test Cases
    TestRunner.TestAllCases
End Sub
Private Sub Report()
    ' We want to see the summary report in a message box at the end
    TestReporter.ResultsMsgBox
    
    ' We also want to see a list of all the failures
    TestReporter.PrintFailureLog
End Sub

The only thing we need to do is to change iTestableProject_GetTestCases() to include our test case that we made above, if the name was changed from TestCaseStub.

Lastly, we need to be able to run the tests with the configuration we specified. Again, the examples directory has a Module that contains a simple function that can be run from a blank worksheet.

Public Function RunTests()
    Dim TestConfig As iTestableProject
    Dim UnitTestConfig As New TestConfigStub
    Set TestConfig = UnitTestConfig
    TestConfig.Run
End Function

We are finally done! Go to an Excel sheet and type =RunTests(). The code will execute and if the assertion were to fail, the specified message will display.

In the next chapters we will create multiple tests in the same TestCase, use a data-provider to run the same test multiple times with different inputs, create a custom assertion, and test for expected errors.