How to setup a unit test in VBA Excel Macro?

If it were me, I would create a second spreadsheet that references the sheet to be tested. (Go to tools>references>browse) This new workbook can contain all the tests you want without having to dirty up your main workbook. This also frees you up to build an interface for yourself if you want one.

As a side note, if you want to hide procedures from the Excel Macro menu but still have access to them, at the top of your module put:

Option Private Module

Then make all the procedures you want to be able to use "Public". You can then call them from the immediate window (ctrl-g in the VBE) but they won't be listed in the macro list.

But if you keep your unit tests separate (as you probably should), then you don't really have to worry about Public/Private modules.

Rubberduck is a free and open source VBE add-in that includes an IDE integrated Unit Testing framework and works on most of the major Office Products.

1. Download and install the latest release.
2. Open the Excel workbook your VBA project resides in.
3. Go to Tools >> Refrences and add a reference to Rubberduck.
4. Add a new standard module.

5. Add the '@TestModule attribute and create an instance of the Rubberduck.AssertClass

   `@TestModulePrivate Assert As Rubberduck.Assert

6. Start writing tests. Any Public Sub marked with the '@TestMethod attribute will be discovered and run by the Test Explorer.

   '@TestMethodPublic Sub OnePlusOneIsTwo()    Assert.AreEqual 2, Add(1,1)End Sub

For more information, please see the Unit Testing page of the project's wiki.

Disclaimer: I am one of the project's developers.

Haven't looked at it in years, but vbaUnit used to work well...