OOP VBA pt.1: Debunking Stuff

Ever seen that one?

It’s not a real language

The thing is, object-oriented code can definitively be written in VBA. This series of posts shows how. Let’s first debunk a few myths and misconceptions.


VBA classes don’t have constructors!

What’s a constructor but a tool for instantiating objects? In fact there are many ways to create objects, and in SOLID OOP code there shouldn’t be much Newing-up going on anyway: you would be injecting a factory or an abstract factory instead, to reduce coupling. VBA is COM, and COM loves factories. No constructors? No problem!


VBA code is inherently coupled with a UI or spreadsheet

In OOP, the ideal code has low coupling and high cohesion. This means code that doesn’t directly depend on MsgBox, or any given specific Worksheet or UserForm. Truth is, OOP code written in VB.NET or C# be it with WinForms or WPF UI frameworks, faces the same problems and can easily be written in the same “Smart UI” way that makes the UI run the show and the actual functionality completely untestable: bad code is on the programmer, not the language. And spaghetti code can be written in any language. The very same principles that make well-written VB.NET, C#, or Java code be good code, are perfectly applicable to VBA code.


Writing Object-Oriented VBA code is painful

Okay, point. The VBE’s Project Explorer does make things painful, by listing all class modules alphabetically under the same folder: it’s as if the IDE itself encouraged you to cram as much functionality as possible in as few modules as possible! This is where Rubberduck’s Code Explorer comes in to save the day though: with a simple comment annotation in each class’ declarations section, you can easily organize your project into virtual folders, nest them as you see fit, and best of all you can have a form, a standard module and a dozen class modules under the same folder if you want. There’s simply no reason to avoid VBA code with many small specialized class modules anymore.


OOP is overkill for VBA

After all, VBA is just “macros”, right? Procedural code was good enough back then, why even bother with OOP when you’re writing code in a language that was made to “get things done”, right? So we go and implement hundreds of lines of code in a worksheet event handler; we go and implement dialogs and thousands of lines of code in a form’s code-behind; we declare dozens upon dozens of global variables because “that’s how it was made to work”. Right? Nope.

It works, and everyone’s happy. Until something needs to change, and something else needs to change the week after, and then another feature needs to be added the next week, then a bug needs to be fixed in that new feature, and then fixing that bug ripples in unexpected places in the code; the beast eventually grows hair and tentacles, and you’re left sitting in front of a spaghetti mess.

And it’s hard to maintain, not because it’s VBA, but because it was written “to get things done”, but not to be maintained. This “ball of mud” code can happen in any language: it’s not the language, it’s the mentality. Most VBA developers are not programmers – code gets written the way it is because doing things in a SOLID way feels like going to the Moon and back to end up next door with the exact same functionality… and lots simply don’t know better, because nobody ever taught them. At least, that’s how it started for me.

Then there’s the IDE. You would like to refactor the code a bit, but there are no refactoring tools and no unit tests, and every change you make risks breaking something somewhere, because knowing what’s used where is terribly painful… and there’s no integrated source control, so if you make a change that the undo button doesn’t remember, you better remember what it looked like. And eventually you start commenting-out a chunk of code, or start having DoSomething_v2 procedures, and then DoSomething3. Soon you don’t know which code calls which version and you have more comments than live code. Without source control, it’s impossible to revert back to any specific version, and short of always working off a copy of the host document, code changes are done at the risk of losing everything.

No safety net. Pretty much no tooling. The VBE makes it pretty hard to work with legacy code – at least, harder than with a more modern, full-featured IDE.

Rubberduck will change that: Rubberduck wants to make writing object-oriented VBA code as enjoyable as in a modern IDE, and maintaining and refactoring legacy procedural code as easy and safe as possible.

Is OOP overkill for VBA? If it’s not overkill for even the tiniest piece of modern-language code, then I fail to see why it would be overkill for any VBA project. After all, SOLID principles are language-agnostic, and the fact that VBA doesn’t support class inheritance does nothing to affect the quality of the code that’s possible to achieve in VBA.


Wait, how would SOLID even apply to VBA?

The Single Responsibility Principle is a golden rule that’s as hard to follow in VBA as it is in any other language: write small procedures and functions that do one thing, prefer many small specialized modules over fewer, large ones.

The Open/Closed Principle, which leaves classes open for extension, closed for modification is even harder to get right, again regardless of the language. However like the others, if the other 4 principles are followed, then this one is just common sense.

Liskov Substitution Principle involves no wizardry, it’s about writing code so that an implementation of an interface guarantees that it does what the interface says it’s doing, so that any given implementation of an interface can be injected into the code, it will still run correctly.

The Interface Segregation Principle goes hand in hand with the other principles, and keeps your code cohesive, focused. Interfaces should not leak any specific implementation; an interface with too many members sounds like breaking SRP anyway.

The Dependency Inversion Principle is possibly the one that raises eyebrows, especially if you don’t know that VBA classes can implement interfaces. Yet it’s perfectly possible to write code against an IMsgBox interface, inject a MsgBoxImpl class in the production code, and inject a MsgBoxStub class in the test code.

See? Nothing VBA can’t handle. So object-oriented VBA code is theoretically possible. In the next couple of weeks we’ll go over what it means in real-world VBA code, in terms of project architecture, design patterns, and code design in general.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s