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

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

Answer 2

Sure, under the Developer Tab (Excel 2007) there is a Macros button that list all available macros that you can run. You would choose from the list and click Run. Older versions have this same functionality, but I can not remember where they are located.

Here is a link for 2003 version

http://spreadsheets.about.com/od/advancedexcel/ss/excel_macro_5.htm

Answer 3

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.

Answer 4

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.