Cat On A Spreadsheet

Building a Production-Grade VBA Test Harness (Unit-Testing Concepts in Excel)

As your VBA codebase grows, manual testing and ad-hoc message boxes stop cutting it. Bugs slip through, refactors become risky, and teams lose confidence in making changes. A simple, structured test harness gives you automated verification of behavior, fast feedback during development, and a safer path to refactoring.

A production-ready VBA test harness doesn’t need to be complicated. It should be small, predictable, and easy for any developer on your team to use. The core ingredients are:

A set of lightweight assertion helpers (AssertEqual, AssertTrue, etc.).
A test runner that finds and executes test procedures.
A consistent way to report results (worksheet, Immediate window, or a CSV).
Optional setup/teardown hooks for tests that need controlled state.

Below I walk through a compact, reusable harness pattern you can drop into any workbook.

Design principles

Keep these principles in mind when designing the harness:

Tests should be independent: each test must set up its own prerequisites.
Keep assertions descriptive: when something fails, the message should tell you what and where.
Make test discovery simple: name tests consistently so the runner can auto-detect them.
Prefer deterministic behavior: avoid randomness in tests unless seeded.

These lead to quick, trustworthy feedback and easy adoption by teammates.

The Assert helpers

Start with a small module of assertion routines. They are the primitives tests use to declare expected behavior. The examples below write results to a simple TestResults sheet, but you can adapt output to CSV or the Immediate window.

  Option Explicit
 
 Public Sub AssertEqual(ByVal expected As Variant, ByVal actual As Variant, ByVal message As String)
 
      If CStr(actual) <> CStr(expected) Then
 
          ReportFailure "AssertEqual", message & " | Expected: " & CStr(expected) & " | Actual: " & CStr(actual)
 
      Else
 
          ReportSuccess "AssertEqual", message
 
      End If
 
 End Sub
 
 Public Sub AssertTrue(ByVal condition As Boolean, ByVal message As String)
 
      If Not condition Then
 
          ReportFailure "AssertTrue", message & " | Condition was False"
 
      Else
 
          ReportSuccess "AssertTrue", message
 
      End If
 
 End Sub
 
 Private Sub ReportSuccess(ByVal assertType As String, ByVal message As String)
 
      WriteTestResult "PASS", assertType, message
 
 End Sub
 
 Private Sub ReportFailure(ByVal assertType As String, ByVal message As String)
 
      WriteTestResult "FAIL", assertType, message
 
 End Sub
 
 Private Sub WriteTestResult(ByVal status As String, ByVal assertType As String, ByVal message As String)
 
      Dim ws As Worksheet
 
      On Error Resume Next
 
      Set ws = ThisWorkbook.Sheets("TestResults")
 
      If ws Is Nothing Then
 
          Set ws = ThisWorkbook.Worksheets.Add
 
          ws.Name = "TestResults"
 
      End If
 
      Dim nextRow As Long
 
      nextRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row + 1
 
      ws.Cells(nextRow, 1).Value = Now
 
      ws.Cells(nextRow, 2).Value = status
 
      ws.Cells(nextRow, 3).Value = assertType
 
      ws.Cells(nextRow, 4).Value = message
 
 End Sub

The Test Runner

A simple discovery rule is to name test procedures starting with Test_. The runner reflects over all standard modules and executes public Subs that match that prefix. Keep the runner small so it’s easy to understand and extend.

  Option Explicit
 
 Public Sub RunTests()
 
      Dim comp As Object
 
      Dim vbComp As Object
 
      Dim proc As Object
 
      Dim testName As String
 
      ' Clear prior results
 
      On Error Resume Next
 
      ThisWorkbook.Worksheets("TestResults").Delete
 
      On Error GoTo 0
 
      Set comp = ThisWorkbook.VBProject.VBComponents
 
      For Each vbComp In comp
 
          For Each proc In vbComp.CodeModule.ProcStartLine
 
          ' (Simple discovery below — we ask for test subs starting with Test_ )
 
          ' We will call them by name using Application.Run
 
      Next vbComp
 
      Next vbComp
 
      ' Practical approach: maintain a manual list of tests in a module named TestList
 
      Dim t As Variant
 
      For Each t In TestList_GetTests()
 
          On Error GoTo TestError
 
          Application.Run t
 
          On Error GoTo 0
 
      Next t
 
      MsgBox "Tests completed. See TestResults sheet.", vbInformation
 
      Exit Sub
 
 TestError:
 
      Dim errMsg As String
 
      errMsg = "Error in " & t & " | Err " & Err.Number & ": " & Err.Description
 
      WriteTestError errMsg
 
      Resume Next
 
 End Sub

Note: VBA’s programmatic code inspection (VBProject access) is subject to a Trust Center setting. If you prefer not to require that setting, keep the harness discovery simple: maintain a small TestList function that returns an array of test procedure names — it’s explicit and easy to control.

Example test and test list

Create a module for tests and list them. Tests should be tiny and focused.

  Option Explicit
 
 Public Function TestList_GetTests() As Variant
 
      TestList_GetTests = Array("Tests_TestAdd", "Tests_TestSplit")
 
 End Function

  Option Explicit
 
 Public Sub Tests_TestAdd()
 
      Dim result As Long
 
      result = MyLib_Add(2, 3)
 
      AssertEqual 5, result, "Adding 2 + 3 should be 5"
 
 End Sub
 
 Public Sub Tests_TestSplit()
 
      Dim arr As Variant
 
      arr = MyLib_SplitWords("one two three")
 
      AssertEqual 3, UBound(arr) + 1, "Split should return 3 words"
 
 End Sub

And a tiny library under test:

  Option Explicit
 
 Public Function MyLib_Add(a As Long, b As Long) As Long
 
      MyLib_Add = a + b
 
 End Function
 
 Public Function MyLib_SplitWords(s As String) As Variant
 
      MyLib_SplitWords = Split(s, " ")
 
 End Function

Setup / Teardown and Test Fixtures

For tests that need a controlled environment (files, temporary sheets, mocked ranges), provide optional Setup and Teardown conventions. For example, if a test sub is named Tests_TestX, allow a Tests_TestX_Setup and Tests_TestX_Teardown pair that the runner calls automatically before and after the test. That keeps tests isolated and reproducible.

Reporting and Continuous Integration

The harness above writes the pass/fail rows to a TestResults worksheet. That sheet is easy to parse programmatically or export as CSV for CI systems. For continuous integration you can:

Save the workbook headless (with Application.DisplayAlerts = False) after tests and export TestResults to CSV programmatically.
Have an external scheduler or script open Excel, run RunTests, and then collect the TestResults.csv.
If your team uses a third-party tool (Jenkins, Azure DevOps), the exported CSV becomes the test artifact.

Extra tips for production usage

Keep tests fast: make unit tests run in milliseconds. If a test touches I/O, mark it as integration and run less frequently.
Group tests by feature module for targeted runs.
Use deterministic seeding for random functions.
Make failure output actionable: include input, expected, actual, and any relevant state.
Consider using an external test runner like Rubberduck if you want rich unit test tooling — the harness above is intentionally minimal and dependency-free.

Final thoughts

A small, disciplined test harness transforms VBA projects from fragile spreadsheets into reliable automation. It reduces fear of refactoring, speeds onboarding for new developers, and provides an auditable trail of verification. Start small: write tests for the core functions you change most often, and expand coverage iteratively. Over time, that investment pays back many times over in stability and developer confidence.