What’s Wrong With VBA?

The Most Dreaded Language

The annual Stack Overflow Developer Survey has always ranked VBA pretty high on the “most dreaded” languages. For some reason this year VB6 and VB.NET aren’t making the list, but VBA is sitting at the very top of it, with 75.2% of respondents “dreading” VBA.

VBA is a gateway language – it was for me, anyway. It gets things done, and abstracts away boilerplate that you don’t really need to worry about in order to, well, get things done. For some, that’s good enough. As long as it works. Code is written to be executed, right? What if we wrote code for it to be read instead? Code that’s easy to understand, is easier to maintain and to extend without breaking things. Code that’s well organized, that uses small specialized and reusable components that can be tested independently, …is just objectively better code. And nothing in VBA says it can’t be exactly that.

Nothing is wrong with VBA. Obscure code with weird variable names, twisted code that’s hard to read and ever harder to follow, can be written in every single programming language yet invented or not.

VBA is a version of “classic” Visual Basic (VB5, VB6) that is hosted in another application. For a number of years Microsoft was selling a VBA Software Development Kit (SDK), and with it you could embed VBA in your own product to enable scripting against your own COM API / object model library: you could write an ERP (Enterprise Resource Planning) system, CAD software, vector graphics drawing software, anything really – and enable user extensibility through the VBA SDK. These were the golden years of Visual Basic: everyone knew VB. I happened to be in college around these years, and Programming I involved VB6. It was a gateway language back then too: “real programmers” wrote C++.

Visual Basic happened a few years after QBasic, which succeeded to BASIC. Edsger W. Dijkstra famously had this to say about BASIC:

It is practically impossible to teach good programming to students that have had a prior exposure to BASIC: as potential programmers they are mentally mutilated beyond hope of regeneration.

And yet after all these years, BASIC is still alive, through VBA and VB.NET. Bad code is on the programmer, not the language. And if you want to learn, you will learn: don’t let anyone tell you otherwise. Every single programmer alive was once a beginner, and whether you’re writing VBA, Java, C++, C#, heck even PHP, or the latest flavor-of-the-week of Javascript, remove the curly braces and semicolons and rule of thumb they could all pass one for the other if the same person wrote them all: ignore the brogrammers that think whatever language they code in is better than yours. Your mind isn’t any more mutilated than any other’s for it.

VBA is a full-fledged, mature programming language that has proven itself multiple times over the past 20 years (and more). It’s not just procedural code either: Visual Basic projects can define custom classes and spawn real COM objects; objects that can present multiple interfaces, expose and handle events, and these capabilities open doors no toy language can even begin to dream opening. “But it doesn’t do class inheritance! It’s not real object-oriented programming!” – sure there are limitations; but while class inheritance is cool, it’s also often and easily abused. Composition is preferred over inheritance for many reasons, and VBA lets you compose objects as much as you need. What makes inheritance so nice is in no small part that you get to treat all derived classes as their common base class, which gives you polymorphism: Car, Plane, and Boat can all be treated as a Vehicle, and each object might have different means to implement a Move method. VBA code can do this too, using interfaces. For the most part, VBA is only as limiting as you make it.

Among the useful things other languages do that VBA doesn’t, we notably find reflection: the ability to write code that can inspect itself – for example being able to query a VBA project’s type library to locate a particular Enum type, iterate its members, and store the member names and their underlying values in a dictionary. Reflection is made possible in .NET with a very detailed type system that VBA doesn’t have: writing some kind of reflection API for VBA isn’t impossible, but demands very intimate knowledge of how VBA user code and types work internally, and a way to access the internal pointers to these COM structures. Reflection is extremely powerful, but comes at a cost: it is generally avoided in places where performance matters.

VBA doesn’t support delegates, and doesn’t treat functions as first-class citizens: you can’t pass a function to another procedure in VBA; you pass the result of that function instead. This makes it hard to implement, for example, feature-rich data structures that can be queried and/or filtered: the querying and filtering needs to happen in-place using an explicit loop, and this makes the code more verbose than the equivalent in, say, C# or VB.NET, where such deeds would be accomplished using LINQ and other modern technology. But lambdas only appeared in Java rather recently, and their decades-long absence didn’t undermine Java’s claim to fame for all these years – .NET delegates are an incredibly useful tool to have at your disposal, but we can very well do without, albeit with a little bit more verbose code. And guess what? Fancypants LINQ code might be very elegant (if used well… it can also be a mind-wrecking nightmare), but .NET programmers tend to avoid using it in places where performance matters.

Error handling in VBA works with global runtime error state, and On Error statements that essentially set up conditional GoTo jumps. Other languages have exceptions and try/catch blocks… which essentially set up conditional GoTo jumps. Sure exceptions are great, and they can simplify error handling. But they are no silver bullet, and judging by the sheer amount of “real programmers” using them for flow control, or just plain swallowing them and moving on… bad exception handling in any language is just as bad as bad error handling in VBA.

The stigma around VBA and VB6 as a language, is also and perhaps even mostly due to the Visual Basic Editor (VBE) itself. As an IDE the VBE simply didn’t keep up, it was… pretty much abandoned. There’s a (now closed) question on Stack Overflow asking whether there are any refactoring tools for VBA. The top-voted answer was then a funny jab at the legacy editor, saying the only refactoring they know about, is search/replace (Ctrl+H). The editor itself feels like it’s actively working against writing full-blown object-oriented VBA code, or just plain old clean-reading code: all your classes are shoved under a single “Class Modules” folder, sorted alphabetically… so you resort to funky naming schemes just to visually regroup things by functionality. You might have toyed with interfaces before, but coding against them (i.e. to abstractions, not concrete types; c.f. the Dependency Inversion Principle) makes it impossible to navigate to the actual code that implements these interfaces. There’s no built-in support for unit testing, no mocking framework, no refactorings, no static code analysis, no code metrics, …and the list goes on and on.

The language does have its little annoying quirks (every language does), and some massively used type libraries (like Excel’s) do have their own little annoying quirks as well – but VBA as a language isn’t to blame for the quirkiness of some type libraries, even first-party ones developed by Microsoft.

VBA isn’t what’s wrong with VBA. The Visual Basic Editor is. If only there was a VBIDE add-in that made working with VBA more pleasant…