How to unit test Excel VBA code

Question

Does anyone have any experience with unit testing Excel VBA code? I want to introduce unit tests into some legacy Excel VBA code as painlessly as possible. One idea I had would be to use VSTO to call code from inside the Excel workbook. I would like to know if others have tried this for the purpose of unit testing the Excel code, as well as any other methods they may have used for unit testing Excel VBA.

I would appreciate some pointers as to any available frameworks and/or tips on unit testing Excel VBA code as well.

Answer 1

Disclaimer: I own Rubberduck's GitHub repository, and I'm one of the devs involved in the project.

Rubberduck is under active development. It's much more than a unit testing tool for VBA though, but it works pretty well and lets you write VBA unit tests pretty much without any boilerplate:

'@TestModule
Private Assert As New Rubberduck.AssertClass

'@TestMethod
Public Sub TestMethod1()
    Assert.Inconclusive "Test method is not written yet."
End Sub

'@TestMethod
Public Sub AnotherTestMethod()
    Assert.IsTrue False, "Something's wrong?"
End Sub

And then you get to navigate and run your test methods in a docked toolwindow that also gives you menus for quickly adding arrange-act-assert method stubs, and the AssertClass can be late-bound, too, so you don't have to worry about deploying Rubberduck anywhere else than on your development environment just to keep the code compilable.

---

The unit testing wiki page on Rubberduck's GitHub repository explains pretty much everything there is to explain about using it.

---

The latest 2.1 pre-release versions includes the beginnings of a "fakes" framework that can be used to hijack a number of standard library calls that would normally interfere with unit tests, by literally turning the standard library into "test fakes" that can be setup to behave as specified when executed in the context of a Rubberduck unit test, for example MsgBox calls:

'@TestMethod
Public Sub TestMethod1()
    On Error GoTo TestFail

    Fakes.MsgBox.Returns 42 ' MsgBox function will return 42

    'here you'd invoke the procedure you want to test
    Debug.Print MsgBox("This MsgBox isn't going to pop up!", vbOkOnly, "Rubberduck") 'prints 42

    With Fakes.MsgBox.Verify ' Test will pass if MsgBox was invoked as specified
        .Parameter "prompt", "This MsgBox isn't going to pop up!"
        .Parameter "buttons", vbOkOnly
        .Parameter "title", "Rubberduck"
    End With
TestExit: 
    Exit Sub
TestFail: 
    Assert.Fail "Test raised an error: #" & Err.Number & " - " & Err.Description
End Sub

Contributions to expand that Fakes API to cover more functions are more than welcome. Covering FileSystemObject invocations would be particularly useful.