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

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.

Answer 2

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.