are there any good unit testing packages for excel
I assume you want to unit test VBA code in modules.
First, even if there was a good unit testing framework available for VBA, I suspect testing would be very complex, because of the dependencies to the Workbook itself. If your code interacts with the workbook and its objects is going to be a total nightmare because you can't really mock any of this: imagine testing a module that reads data in a sheet and creates a chart in another one... In essence, an Excel workbook merges your persistence, domain and presentation all in one - not good for testing.
The other case is code which is mostly computation oriented. If that code gets complex enough that it warrants testing, one thing you might consider is to actually move your code outside of VBA, to make it testable. I often work with clients who have large financial models, with heavy VBA, and when I can I like to extract the VBA code to C# and make it a VSTO add-in. The benefit is that I can test the code, and work in Visual Studio, instead of the VBA IDE.
There are a couple of experiments kicking around the internet: EUnit and VBAUnit. Neither of these projects seem to have active communities, so they are unlikely to be as robust as (say) JUnit or NUnit. But they may be good enough for your purposes.
I use Debug.Assert
. I organize the tests in modules. In each module I maintain a Sub RunAll
that calls all the test methods in that module.
To run all tests in the project, I have a module AllTests with a RunAll that calls RunAll in all the test modules.
Simple and works great without any fuss.
MSDN article on VBA Debug.Assert