How to setup a unit test in VBA Excel Macro?

Question

I am working on an Excel VBA macro that will take some excel file, scrub / clean it, and produce a workbook with 2 sheets - sheet 1 being the original 'dirty' file, sheet 2 being the cleanly scrubbed file.

Since we have 10 different formats right now, and down the road 40+.. I would like to save this output for a given set of files, then write a unit test which takes the original input, runs our macro, then compares the macro's output to the saved, expected output we have.

Then anytime later down the road when we do maintenance, we can quickly and easily run our unit-tests to make sure we didn't break anything that already worked.

This is my first time working with VBA. I googled around for frameworks or plug ins and can't find much help. I did find a function that will compare 2 whole excel spreadsheets - so I have that part down. But now I need to figure out how to actually write and execute this unit test. Writing the unit test should be pretty straight forward - but how do I execute it? I don't want to put a button on the spreadsheet that says 'run unit tests'... Is there a way in VBA/Excel to just run an arbitrary function, so I can just say right click on my UnitTest function and do 'run'?

Any additional feedback on what my plans for unit testing would also be appreciate. Thanks again.

Answer 1

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

   `@TestModule
   
   Private 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.

   '@TestMethod
   Public 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.