How to unit test Excel VBA code

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:

'@TestModulePrivate Assert As New Rubberduck.AssertClass'@TestMethodPublic Sub TestMethod1()    Assert.Inconclusive "Test method is not written yet."End Sub'@TestMethodPublic 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:

'@TestMethodPublic 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 WithTestExit:     Exit SubTestFail:     Assert.Fail "Test raised an error: #" & Err.Number & " - " & Err.DescriptionEnd Sub

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

I was just looking for the same thing and found: http://accunit.access-codelib.net/ which integrates into the VBA IDE quite nicely.

Note: Viewing the page in German and using the browser translation tools seems to yield more info than the English pages.

The xlwings guys just demoed how to unit tests VBA functions in Python. Essentially, you do not need to add anything to your workbook but you rater write "stand alone" Python code that call your VBA functions so that you can compare expected vs actual results of your VBA functions.