Unit Tests for Microsoft Excel

Many (business) users use Excel to create full blown applications. However, unlike applications developed by professional software developers, Excel tools fall short of most software development practices that are considered minimal standards. This introduces risks that can lead to reputational damages and substantial financial losses (remember the «London Whale»).

This blog post introduces the concept of test-driven development before it shows you how to write an automated unit test for a VBA function. All we need is Python, a free and open-source programming language and xlwings, our Python package that connects to Excel. The concepts shown work on Windows and Mac.

If you already know what I am speaking of and don’t want to go through all the step by step instructions below: Simply head over to GitHub where you can clone or download a fully working example. For a broader but less technical view of this topic, download our white paper.

Overview

1. The directory of horror: «John_handover»
2. Test-driven development
3. The missing piece: Python with xlwings
4. Before we can start: Install Python
5. A simple unit test
6. No guarantee for success
7. Where to go from here

The directory of horror: «John_handover»

We’ve all been there: On day one of our new job we are assigned the responsibility over a folder called «John_handover». John left the company a while ago, but not without leaving behind a spreadsheet tool of about 20 MB of size, containing VBA and complex formulas that involve multiple sheets (some of them hidden) and countless VLOOKUPs. Your boss is kind enough to tell you the secret of how to manage it: «Just don’t touch anything and you will be fine.»

After getting over your first shock and thinking about how to get out of the mess, you come up with the solution of having IT transform this Excel tool into a proper application. But it turns out that the IT budget has already been spent for this year and for next year there are already a few other projects queuing up.

Bottom line is that you’re left with a tool that causes you a headache and that you don’t really understand. You would like to simplify it but are afraid of breaking something.

Guess what: In software development, this situation is the rule rather than the exception. But there’s ways to get confidence over such legacy tools and make sure that they still work as they are supposed to after a change. Say hello to test-driven development.

Test-driven development

A core component of modern software development is writing automated tests. Most commonly, these tests make sure that a small independent unit of the program’s overall logic works correctly given a few test cases that are therefore called «unit tests». The paradigm that wants you to write the tests first and only then adopt the logic of the program until all tests pass, is called «test driven development» (TDD). In a nutshell, the thinking behind TDD is: «If it’s not tested, it’s broken» (Bruce Eckel in his book «Thinking in Java», 3rd edition).

Unit tests are a very effective way to automatically test a large number of use cases (including edge cases) to make sure that a program still works correctly under all tested circumstances even after adding or changing pieces of the program.

Practically all common programming languages come with either a built- in framework for writing unit tests or integrate easily with an existing framework. Excel, however, doesn’t offer any means to effectively write unit tests. That is, until now.

The missing piece: Python with xlwings

Python is a widely used programming language that excels at interacting with all kinds of different systems which is why it is sometimes referred to as a «glue language». To interact smoothly between Excel and Python, we created xlwings, a Python package that is free and open-source, like Python itself.

xlwings allows you to automate and program Excel with Python and therefore gives you access to Python’s built-in unittest module. Writing unit tests for Excel with Python and xlwings is simple and intuitive:

• No Excel add-ins required
• No changes to the original Excel file necessary
• Create tests covering spreadsheet logic or VBA code
• You only need a Python installation with xlwings

Before we can start: Install Python

Simply download Anaconda, a free scientific Python distribution that comes in the form of a one-click installer that already contains everything we need. After downloading, install Anaconda with the defaults.

If it worked out correctly, you should see something like the following when typing python at a command prompt (on Windows) or a Terminal (on Mac):

C:\Users\felix> python
Python 3.5.2 |Anaconda 4.2.0 (64-bit)| (default, Jul  5 2016, 11:41:13) [MSC v.1900 64 bit (AMD64)] on win32
Type "help", "copyright", "credits" or "license" for more information.
>>>

A simple unit test

Create a new macro-enabled workbook with the name mybook.xlsm and save it in the folder where you started the command prompt: in my example that would be C:\Users\Felix. Open the VBA editor by clicking Alt-F11, Insert a new VBA module (Insert > Module) and paste the following simple VBA function:

Function mysum(x, y) As Double
    mysum = x + y
End Function

To access this function in Python, you can easily map it with xlwings: In the Python session we started above, execute the following lines to import the xlwings package, then map the workbook and finally the VBA function. If you have saved your workbook somewhere else than in the current working directory of the command prompt you would need to provide its full path.

>>> import xlwings as xw
>>> wb = xw.Book('mybook.xlsm')
>>> mysum = wb.macro('mysum')

Now you can use mysum as if it was a native Python function but it’s actually running the VBA code:

>>> mysum(1, 2)
3

Let’s now create a Python file called test_mybook.py. In your favorite text editor, copy/paste the following into that file:

import unittest
import xlwings as xw

class TestMyBook(unittest.TestCase):
    def setUp(self):
        # setUp will be called before the execution of each unit test
        self.wb = xw.Book('mybook.xlsm')  # map workbook
        self.mysum = self.wb.macro('Module1.mysum')  # map function

    def test_mysum(self):
        result = self.mysum(1, 2)  # get result from VBA
        self.assertAlmostEqual(3, result)  # test if result corresponds to the expected value

if __name__ == '__main__':
    # This allows us to easily run the tests from the command prompt
    unittest.main()

This file contains quite a bit of boilerplate code but the actual unit test is defined in test_mysum where we assert that the result as delivered by the VBA function corresponds to the expected result. We use assertAlmostEqual so that the test doesn’t fail because of floating point issues.

To run all unit tests in that file (ok, it’s just one for now), simply call the following from a command prompt (if you still have a Python session running, then make sure to quit first by hitting Ctrl-D or entering quit()):

C:\Users\felix> python test_mybook.py 
.
----------------------------------------------------------------------
Ran 1 test in 0.083s

OK

The report gives you a dot for all tests that ran successfully. If we would introduce an error in the VBA code by changing it like this:

Function mysum(x, y) As Double
    mysum = x * y
End Function

Then the output would accordingly tell us about the failed test like this:

C:\Users\felix> python test_mybook.py 
F
======================================================================
FAIL: test_mysum (__main__.TestMyBook)
----------------------------------------------------------------------
Traceback (most recent call last):
  File "test_mybook.py", line 17, in test_mysum
    self.assertAlmostEqual(3, result)
AssertionError: 3 != 2.0 within 7 places

----------------------------------------------------------------------
Ran 1 test in 0.093s

FAILED (failures=1)

No guarantee for success

Unit tests are tremendously helpful in regaining trust over a complex workbook and feel comfortable before doing changes. However, even if all tests pass, it’s in no way a guarantee for bug free code.

First, only situations that you can think of are tested and second, the tests might pass if you pick the wrong edge cases. In the sample above, if I would have tested 2 + 2 = 4, instead of 1 + 2 = 3, then the unit test would have still passed when using the multiplication by mistake. Hence it usually makes sense to test a few scenarios including various edge cases.

Where to go from here

The unit test that we’ve built in this blog post is just a simple example of writing a test that checks the logic of a VBA function.

However, you can use the same unit testing infrastructure to validate a model by comparing the output of a VBA function with the output of an alternative implementation, e.g. in one of Python’s heavily tested 3rd party packages.

Also, you can write tests that control the correct formulas and relationships in cells as this is where you often introduce errors by copy/pasting or by dragging the wrong cells and consequently filling cells with the wrong formula.

To get an overview over these techniques, too, download our white paper.