VBA and and the Power of the IDE

Warning: Illegal string offset 'filter' in /var/sites/t/theproactiveprogrammer.com/public_html/wp-includes/taxonomy.php on line 1409

Over the past few weeks I have been working on my first significant project in VBA, building a complex MS Excel application which makes use of a .NET dll for some complex maths. It has been a very interesting but challenging experience so far. I always welcome the opportunity to program in different languages, as it tends to remind me of the truly language-agnostic fundamental principles of programming, such as good naming, small functions and modularisation. Having said that, the experience has highlighted the importance of a good IDE and powerful development environment for the modern day programmer. The standard VBA editor built into Excel is basic to say the least. What follows are the key features of my usual development environment which I now realise I have been taking for granted.


One phrase from Steve McConnell’s Code Complete that has stuck with me for many years is that “design is a sloppy process”. In other words, when designing something we should expect to scribble, throw ideas away, experiment and change our mind before ultimately settling on something we are happy with. If I recall correctly, McConnell was primarily referring to the traditional software design process of planning how you divide your code into classes, functions, modules and so on. However, for the modern day developer with powerful refactoring tools at his disposal, programming is more than ever a process of continual “sloppy” design. We take for granted the ability to easily change our minds, to redesign and redesign again. Our IDE might do the entire refactoring task for us, or at least tell us what we’ve done wrong. If we have unit tests then these can reassure us that we haven’t broken anything. Planning ahead of time is important of course, but once we start writing code we often realise a better way of doing things, and our refactoring tools allow us to make these ideas a reality.

As you will know if you have worked with VBA, its editor was not built with refactoring in mind. With regards to refactoring, probably the most frustrating missing piece of functionality is that there is no way to quickly rename an item, whether that be a variable, a subroutine, a function or a class. I am a great believer in the value of naming things well, and therefore frequently rename things in Visual Studio as I think up new and improved names. Without the ability to automatically rename all references you are left with no option but to use the good old Find function to do it manually.


The VBA editor has no “Go to Definition” shortcut, nor a “Find All References” function. As a result I found myself again using Find as an inferior alternative. With both refactoring and navigation, the problem experienced is largely psychological – your flow is interrupted and therefore so is your creativity. Or at least that’s how it feels. Its a not dissimilar feeling to the anxiety induced by applications “not responding”. You want to get on with your work but your rate of progress is dictated by your toolset, not by you the developer.

Unit Testing

There is virtually no unit testing support in the world of VBA. I downloaded a plugin called Rubber Duck which does offer some limited unit testing functionality (i.e. Assert statements and the ability to run tests). This does what it does well, however there appears to be no good way to mock dependencies, and therefore unit tests can only be written for “pure” functions. Having divided my project into cohesive modules which depended upon each other, I found it impossible to write meaningful unit tests for the majority of my code.

Final Thoughts

I’m actually a big fan of Microsoft Excel and the idea of spreadsheets in general. It is such a powerful tool for the educated user, and expresses brilliantly the power of having data and the ability to analyse it, which is after all the essence of software, is it not? The fact that the VBA editor is limited in terms of tools for the developer may be an indicator that it is intended for thin layers of code only, with the more complex code being written into .NET dlls, for example. Nevertheless, there is no escaping the fact that a complex Excel application will require a significant amount of VBA code, for reading, writing and manipulating the data contained in its worksheets at the very least. Working with VBA has been an eye opener for me, it has provided me with a sense of nostalgia for how developers used to write code before the days of powerful refactoring, navigation and unit testing tools. Furthermore it has encouraged me to look at my code in a different way, to plan more carefully and even develop a little more patience. If you have never written in VBA, I would encourage you to give it a try.


Share Button
  • Rubberduck VBA

    Next release of Rubberduck will introduce a “fakes” API that hooks into the VBA runtime to let the user configure how certain built-in functions such as MsgBox, InputBox, CurDir, Kill, etc. behave when invoked by a Rubberduck unit test. It’s not complete (these hooks need to be implemented individually… but hey we’re open-source, fork us and help!), but it’s a great start. Actual mocking is another story though.. we need to figure out a way to make a .net class implement the COM interface presented by a VBA class module, on the fly, at runtime, and pass it back to the VBA calling code. Probably not impossible, but quite the Holy Grail we’re after. Stay tuned!