VBAUnit

"Necessity is the mother of invention. Laziness is the mother of necessity." -- anon

VBAUnit was born out of necessity and hence laziness. I personally don't like working with VBA on big projects, but sometimes you have to. I wrote VBAUnit for a project I was working on with MS Access. I had recently converted to Extreme Programming, and needed to write automated unit-tests. I like to write automated unit-tests because I'm lazy and don't like to spend hours and hours debugging code. There were already two versions of VBUnit at the time, but neither worked particularly well with VBA. Fortunately, Kent Beck and Erich Gamma, the authors of the very popular JUnit, were good enough to write up their design for JUnit in a document called JUnit: A Cook's Tour, which I used extensively to help me develop the equivalent design in VBA. I highly recommend it. I also borrowed some concepts from the first two versions of VBUnit.

VBAUnit got the job done. Unfortunately, I haven't had much need for it since, so it has kind of stagnated. This version of VBAUnit is supplied as-is. There are a few unresolved problems and the odds are very close to zero that I will ever fix them. Also, I will not be actively maintaining VBAUnit documentation (you may notice a lack of comments in the code).

If there is anyone out there who would like to take over the VBAUnit code and documentation, I encourage you to do so. Contact me by email at rharwood at rapid-programming.com if you have any questions.

The unofficial website for VBAUnit is on WikiWiki (which I also highly recommend) at http://www.c2.com/cgi/wiki?VbaUnit.

VBAUnit code and samples can be downloaded here.

Basics

VBAUnit works with Access, Excel, and to some extent Outlook (see below). Theoretically, it should work in any MS application that uses VBA. For example, I haven't tried it in Word, PowerPoint, or FrontPage, but it would probably work.

Here's what it will take to get up and running:

• Import all the modules. The easiest way is to open the VBA Editor and drag in the modules from a Windows Explorer window.
• VBAUnit requires the use of the VBA Extensibility package. Go to Tools -> References, and then select "Microsoft Visual Basic for Applications Extensibility 5.3". This is the version I have on my computer. I think it will probably work as long as you don't have too early a version.

That should be all that's required to get VBAUnit to run. However, there are some additional considerations.

• If your error trapping settings are not set appropriately, it might be difficult to find any problems that might pop up. Go to Tools -> Options -> General. In Error Trapping, select Break in Class Module.
• Access sometimes requires an "Option Compare Database" line. This won't work in Excel. I stripped out all these lines from my original version, so it should work in Excel, but it's possible you might have to reinsert them if you ever use VBAUnit in Access. In such an event if it causes problems, sprinkle "Option Compare Database" statements wherever appropriate. :-)

Using VBAUnit

Note: Most of this section assumes you are familiar with using JUnit. If you are not, please familiarize yourself at http://junit.sourceforge.net.

The idea behind VBAUnit is to make writing tests easier by minimizing the amount of maintenance required to keep the tests running. It does not have a GUI, just a text interface through the Immediate Window. This makes it slightly less convenient, but it only took a day and a half to write VBAUnit, so I guess that's an alright investment of time. Adding a GUI would probably require writing an Add In, and even then I'm not sure if that would be worth the time.

VBA does not have the capability of introspection that VB can accomplish through ActiveX. This means that following JUnit's example would require adding in a case statement every time you add a test method, and also manually updating the TestCase's suite to include the new test method. What a pain that would be. I almost resorted to that, but laziness prevailed!

Instead, VBAUnit re-builds the case statement and the suite automatically in a separate step, kind of like a preprocessor mixed with a code generator. This separate step is the global 'prep' subroutine. Once the code has been regenerated, the tests can be run by calling the global 'run' subroutine.

VBAUnit uses a naming convention to simplify this:

• Any class module that ends with "Tester" will be targeted by 'prep' for regeneration, and targeted by 'run' for running.
• Within each Tester class, any subroutine that starts with "Public Sub Test" will be run by 'run'. For example, you might declare a test method as:
  

  Public Sub TestPerimeter()
      mAssert.Should mRectangle.Perimeter() = 107, "Perimeter"
  End Sub

As an added bonus, 'prep' also automatically adds Tester classes to the TestClassLister class, so that they will be run automatically by a call to 'run'. No need to update a list of test classes, either.

Example

Open one of the sample projects, or make your own by importing the VBAUnit modules. In the Immediate Window, call the 'prep' routine to regenerate the Tester class modules. Then call 'run' to run the Tester modules. This should run the SimpleTester class, which has two test methods: One that fails, and one that passes. The immediate window should show this:

prep
run
Tests run: 2 Failures: 1
SimpleTester.TestSomethingThatFails: :
expected: 
"1"
but was: 
"2"

Tip: If the test interface has not changed since last run, you can just call 'run' again without needing to call 'prep'.

Try out some test-first programming by changing the name of the RectangleTesterDisabled class to RectangleTester. Then call prep and run. You should get an error when it tries to create a RectangleClass object. Create a new class module called RectangleClass. Re-run the tests. It should give an error again when Init, Perimeter, and Area are not defined. Stub them out. Re-run the tests. This time you've got to actually add the features... Please note that in this example, the tests are themselves intentionally broken to prove they will find bugs. The correct area is 6 and the correct perimeter is 10 (the 7s were added to break the tests). Once you've got a basic RectangleClass, un-break the tests to see how it would work under normal conditions. You should eventually end up with a class that looks something like this:

'' RectangleClass

Private mHeight As Long, mWidth As Long

Public Sub Init(Height As Long, Width As Long)
    mHeight = Height
    mWidth = Width
End Sub

Public Function Area() As Long
    Area = mHeight * mWidth
End Function

Public Function Perimeter() As Long
    Perimeter = 2 * (mHeight + mWidth)
End Function

Known Problems

• Some people wanted to use it in Outlook, so I tried it out. It does work, but the autogeneration code (prep function) doesn't work because Outlook doesn't support the Application.VBE object (probably for security reasons, since it's Outlook).
• 'Prep' is somewhat dangerous because it modifies the code in the modules, and this is not a highly tested feature in Microsoft's VBA. When I used VBAUnit to test itself, this caused it to crash. That is why I have not included VBAUnit's self-test code. It has never crashed when used under normal conditions (i.e. when not using VBAUnit to test itself), but then again I haven't used it that much. I believe the problem is that the VBAUnit Tester classes were calling Prep, which was modifying those same Tester classes which were in the process of running. Be aware that test-code may crash if it calls Prep. The solution is simple. Never call Prep from within code, only manually through the Immediate Window. And since VBAUnit is not even an alpha version, play safe and back up your work at least every hour or so.
  That being said, I haven't had any problems except when using VBAUnit to test itself. Prep seems stable when used under normal conditions.
• When you first open your project (.xls or .mdb), if you immediately go to the VB editor and type 'run' in the Immediate Window it will crash VBE6.DLL. I don't know the cause of this. I've tried it several times and it seems to be very consistent. One way to get around this is to call Prep first before calling Run. For some reason this seems to bypass the problem.

I hope that despite these little quirks it will help you in some way. If you manage to find or fix either of these bugs, let me know. I'd be very interested.

You may use and modify this software in any way you see fit.

Have fun!

Rob Harwood