How to setup a unit test in VBA Excel Macro?

Question

Hello,

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, the compares the macro's output to the saved, expected output we have.

Then anytime later down the road when we do maintaince, 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 before. 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 avalible 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%5Fmacro%5F5.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.