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.

2.0 Beta is here!

A little while ago, we issued an alpha release of Rubberduck 2.0, just because, well, v1.4.3 had been around since July 2015, and we wanted to say “look, this is what we’ve been working on; it’s not nearly stable yet, but we still want to show you what’s coming”.

Time flies. 6 whole weeks, 353 commits (plus a few last-minute ones), 142* pull requests from 8 contributors, 143* closed issues, 60* new ones, 129,835 additions and 113,388 deletions in 788* files later, Rubberduck still has a number of known issues, some involving COM interop, most involving COM reflection and difficulties in coming up with a host-agnostic way of identifying the exact types we’re dealing with.

It might seem obvious, but knowing that ThisWorkbook is a Workbook object is anything but trivial – at this point we know that Workbook implements a WorkbookEvents interface; we also know what events are exposed: we’re this close to connect all the dots and have a resolver that works the way we need it to.

So what does this mean?

It means a number of false positives for a number of inspections. It means false negatives for a number of others.

Other than that, if the last version you used was 1.4.3, you’re going to be blown away. If the last version you used was 2.0.1a, you’ll appreciate all the work that just went into this beta build.

There are a number of little minor issues here and there, but the major issues we’re having pretty much all revolve around resolving identifier references, but I have to admit I don’t like unit test discovery working off the parser – it just doesn’t feel right and we’re going to fix that soon.

Speaking of unit testing… thanks to @ThunderFrame’s hard work, Rubberduck 2.0 unit tests now work in Outlook, Project, Publisher and Visio.

@Hosch250 If you get unit testing to work in outlook I’ll eat my hat.

– @RubberDuck 2016-05-13

So Chris, how’s the hat?

Stay tuned, things are going to snowball from this point on – we’ll be releasing much more often than we have been.

*From the GitHub “Pulse” page between May 7 and June 7, 2016.