Webxcel, the first webserver written in plain Visual Basic macros in Microsoft Excel, is getting more and more mature. Last time I blogged about getting PHP support going, this time we'll look into increasing webxcel's stability - through unit testing.

This is what it looks like:

Figure 1.1: Running VBA unit tests from PowerShell

Why are you still doing this?

You're right, I didn't invent unit testing in VBA and why should anyone even bother? Rubberduck already built tooling for people to improve their VBA workflow, however it requires you to install their add-in, and I think we can do testing in a semi-automated way without even interacting with a GUI.

Also, I decided to take a course called "Peer-To-Peer Systems and Security" at my university, which allowed students to build a predefined project in any language on any platform.

Obviously, I chose Microsoft Word, since enterprise environments need P2P systems and I take course work very seriously (I passed the course with an A+, but from what I heard, the instructors now consider limiting it to more "conservative" platforms). One of the project requirements however was (unit) testing, so I had to come up with something.

How it works

Similar to the build script introduced in the first post about automating version control for VBA projects, unit testing can be achieved by utilising Microsoft Office interop functionality.

Testing can be broken down to:

1) Identify test suites and tests
2) Setup tests (i.e. run beforeAll and beforeEach methods before all or individual tests)
3) Run tests
4) Tear down tests (i.e. run afterAll and afterEach)

Let's look into these steps in detail.

Identifying test suites and tests

.NET supports accessing the VBA document model, so we can list all modules and modify their code (i.e. importing and exporting). For simplicity and also developer experience, let's assume our test suites all have to start with "Test", since we can't annotate modules or declare them "testable" without parsing VBA ourselves. For instance, inheritance like JUnit's TestCase class would require us to understand VBA's inheritance from our unit testing tooling; checking whether a module starts with "Test" is a lot easier:

Figure 1.2: A list of modules (green background) and their corresponding test suites (blue background).

After identifying our test suites, we need to find our tests. Again we can take the naming shortcut, to only test functions starting with "Test" rather than parsing docstrings. With our test cases in place, we can start setting up and running our tests.

Fun fact: the interop code to access the individual lines of a VBA module looks a lot like Visual Basic. To access an individual module, one has to call modules.Item(index), and index starts at 1, which is typical to VB. Similarly, modules expose a module.CodeModule.Lines(fromIndex, toIndex) function, whose indices start at 1 once again. It might be a coincidence, however it looks a lot like that code has not been touched in a long time.

Setup and tear down tests

It might be helpful for tests to share some setup, e.g. when a module needs an instance of a class in all tests, it might be a good idea to create it once and share it among all tests.

To do so, we'll also search for BeforeAll, BeforeEach, AfterEach and AfterAll functions in our test suites and call them before our actual tests.

Running tests

Running a test is rather easy: interop provides an app.Run(macroName, arg1, arg2, ...) method, which we can use to call macros. We can even pass arguments or get its return value if it's an instance of a class. Note that VBA has something similar to structs, i.e. custom types, which can only be used inside VBA. Accessing a custom type returned via app.Run will not work.

To find out whether a test was run successfully, we'll need to signal our result to the test script somehow. Since we can return arbitrary objects, we can create an Assert class, which has two properties:

• AssertSuccessful: indicates whether the test assertion was ok
• AssertMessage: a helpful message to indicate test failure or success

By also providing a factory method to create such Assert objects like AreEqual, special messages like "expected 'foo', got 'bar'" can be crafted in case of a failure:

Figure 1.3: A failed assertion, indicating the expected and actual value

Conclusion

VBA projects can now be unit tested without running tests from a GUI.

I'm still looking for a way to run tests on a CI server for a "Build: Passing" badge on GitHub, so if you happen to know a CI provider offering preinstalled Microsoft Office for FOSS projects, let me know.