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
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.
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.
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
Like what you see?
Get the full story about version control and automated testing for Excel by downloading our white paper "Make Excel Audit Proof!
- How to Effectively Manage Spreadsheet Risk".
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
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):
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:
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.
Now you can use mysum as if it was a native Python function but it’s actually running the VBA code:
Let’s now create a Python file called test_mybook.py. In your favorite text editor, copy/paste the following into that
importunittestimportxlwingsasxwclassTestMyBook(unittest.TestCase):defsetUp(self):# setUp will be called before the execution of each unit testself.wb=xw.Book('mybook.xlsm')# map workbookself.mysum=self.wb.macro('Module1.mysum')# map functiondeftest_mysum(self):result=self.mysum(1,2)# get result from VBAself.assertAlmostEqual(3,result)# test if result corresponds to the expected valueif__name__=='__main__':# This allows us to easily run the tests from the command promptunittest.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
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
Then the output would accordingly tell us about the failed test like this:
C:\Users\felix> python test_mybook.py
FAIL: test_mysum (__main__.TestMyBook)
Traceback (most recent call last):
File "test_mybook.py", line 17, in test_mysum
AssertionError: 3 != 2.0 within 7 places
Ran 1 test in 0.093s
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.