There are, however, limitations to working with VBA. These limitations lead to risks that need to be managed. At the root of the problem is a lack of code centralisation, which leads to duplication. This problem quickly becomes unmanageable if there is no automated test facility. This leads to two common questions:

• Am I working with the correct version of the spreadsheet?
• Is the spreadsheet working correctly?

If these questions can’t be answered quickly, it’s a good indication that you have got a problem The problem might materialise the next time hedge computations are done, resulting in an inappropriate hedge. Do you really want to “hope” the correct spreadsheet is going to be used for the next report? In the remainder of this note it will be explained how centralised, tested code can greatly reduce operational risk while speeding up the development process. This means that we don’t have to hope our sheets are correct. We will know.

Three structures to consider

VBA is a great way of adding new formulas and complex structures to Excel. Complex operations can be moved from the spreadsheet into a more structured code design. This is the first step in making spreadsheets easier to read and therefore more transparent, which should reduce the amount of errors made. The problem with this model is duplication. Consider the diagram below, where we have four spreadsheets all extended with VBA code. Let’s assume that all the spreadsheets have a common basis. For example, they all use the same Economic Scenario Generator (ESG), which is simply copied from one spreadsheet to the next. At certain point in time an error is discovered. Now all 4 spreadsheets need the same fix. Luckily, we only have 4 spreadsheet in this example so we are unlikely to forget one.

This model is not sustainable. As the number of spreadsheets increases, so too does the number of places to look for bugs. However, since there is a steady number of faults, which is dictated by the level of quality, then as the number of spreadsheets grow, so to do the number of faults. Together, these variables form the Square Law of Computing. Assuming a conservative one bug per application, the numbers tell there own story:

Sooner or later you can’t relay on this kind of version management and code fixing. Humans are no good at repeating the same fix 100 times. Or, even worse, fixing the same problem in slightly differing spreadsheets. Before you know it there needs to be fixes for the fixes, if you are lucky enough to catch the defect introduced in the first place. One big step is to centralise the common code. If we would do this, a fix in the ESG would require only one code change instead of 4. Once the change has been made the code could be released under a new version number. The four spreadsheets can now be configured to use the old or the new version of the ESG, making reproduction of previous result straightforward.

Once a centralised resource is available, new tools can be built faster, reusing existing building blocks. Even better than reusing existing functionality is using functionality that is proven to be correct. This can be done by putting a test suite in place. The tests guarantee that the code is working as expected. Tests also provide the users of the code with executable documentation.

The final model presented here considers a central code library which forms the basis for all spreadsheets, VBA code and other software. This code is kept under a version control system like Subversion or GIT. These systems log every change and have functionality to compare various versions of a piece of functionality. This is a great tool that developers can use to track down problems, but it also makes external audits a snap. These systems keep track of who changed what and when. The level of detail is minute by minute.Reports are generated with ease.
To make sure only validated and correct code goes into production a Gatekeeper is put in place. The Gatekeeper compiles the code, packages it, and runs the tests suite to validate it works. Two possible outcomes are possible: success or failure.

Success
If the code passes successfully through the Gatekeeper, the resulting program, VBA add-in or spreadsheet is placed in the Version Repository, ready to use. The is no need for users to re-compile anything. In this manner one can be sure that the VBA building blocks taken from the Version Repository are safe to use. If one is uncertain how to use a piece of functionality, the tests can be consulted to see how it’s used there. In this manner the tests serve as executable documentation, which by definition is always up to date.

Failure
If new functionality is added, or existing modified, and the tests fail, the Gatekeeper will signal a problem has occurred. This will happen within minutes of offering the functionality to the Gatekeeper. The fast feedback loop will give a developer the chance to correct defects while the changes are still fresh in his/her mind. Without the Gatekeeper it could take a few weeks for the defect to be discovered. At this point it will be hard for the developer to remember his changes and rule out the effect of any subsequent changes made. Worse, the defect could have caused serious damage in the production environment.

In some organisations, the Gatekeeper is a person rather than a machine. This is far from ideal, automation is essential here. An automated process is repeatable, can be scaled up and most importantly never “forgets” a step. The process is called continuous integration.

In order to work properly, continuous integration requires the test suite to be of high quality, which is far from simple. We like to think that if you can’t test something properly, you don’t understand it well enough to build it. Testing offers a great opportunity to bring various disciplines together to build exactly what is required.

Conclusion
Excel is a great tool and whatever it doesn’t offer out of the box can be created by extensions. When extending Excel one is offered with the opportunity to greatly reduce operational risks by introducing testing and version control. If implemented vigilantly, the combination of these two will increase productivity of the development team, while reducing operation risk, surpassing both managers and auditors expectations…

References
Collins-Sussman, B., Fitzpatrick, B., Pilato, M. (2004), Version Control with Subversion, O’Reilly Media. Available from http://svnbook.red-bean.com/

Humble J., Farley, D. (2010), Continuous Delivery, Pearson Education

Loeliger, J. (2009), Version Control with Git, O’Reilly Media

Matyas, S., Voit, M., Schneider N. (2007),Continuous Integration,Addison-Wesley Professional

Muller, R. (1951), Simple Square-Law Computer, Analytical Chemistry 23 (10) pp 1494–1495