Late Binding

Ever wondered why sometimes the VBE tells you what the members of an object are, how to parameterize these member calls, what these members return… and other times it doesn’t? Late binding is why.

Rubberduck’s static code analysis is currently powerful enough to issue an inspection result for code that would fail to compile with VB.NET’s Option Strict enabled. It’s not yet implemented. But in the meantime, you can still benefit from writing modern VBA code that passes the Option Strict rules (at least as far as late binding is concerned)… and essentially eliminate the possibility for error 438 to ever be raised in your code.

If you’re coding against a dynamic API, then this isn’t really applicable. But for literally everything else, it’s a must.

What is Late Binding?

A quick Twitter survey revealed that a majority (67%) of VBA developers (well, at least those following the @rubberduckvba account) associate the term “late binding” with the CreateObject function. While it’s true that CreateObject is the only way to create an instance of an object for which you don’t have a compile-time reference, it’s mostly a mechanism for creating an object through a registry lookup: the function accepts a ProgID or a GUID that corresponds to a specific class that must exist in the Windows Registry on the machine that executes the code. If the ProgID does not exist in the registry, an error is raised and no object gets created. While this is useful for providing an alternative implementation (handle the error and return another, compatible object), it is rarely used that way – and then there’s this common misconception that CreateObject can somehow magically create an object out of thin air, even if the library doesn’t exist on the target machine. If you’re reading a blog that says or insinuates something to that effect (I’ve seen a few), close that browser tab immediately – you’re being grossly mislead and there’s no telling what other lies can be on that page.

If you’re still skeptical, consider these two simple lines of code:

Dim app As Excel.Application
Set app = CreateObject("Excel.Application")

Assuming this code compiles, no late binding happening here: all CreateObject is doing, is take something very simple (Set app = New Excel.Application) and make it very complicated (locate the ProgID in the registry, lookup the parent library, load the library, find the type, create an instance, return that object).

Late binding occurs whenever a member call is made against the Object interface.

Dim app As Object
Set app = CreateObject("Excel.Application")

If we’re not in Excel and need some Excel automation, referencing the Excel type library gives us the ability to bind the Excel.Application type at compile-time, however early binding is version-specific… which means if you code against the Excel 2016 type library and one of your users is running Excel 2010, there’s a chance that this user can’t compile or run your code (even if you’re careful to not use any of the newer APIs that didn’t exist in Excel 2010) – and this is where late binding is useful: now the code works against whatever version of the library that exists on that user’s machine (still won’t magically make a Worksheet.ListObjects call succeed in, say, Excel 2003). The downside is, obviously, that you can’t declare any Worksheet or Workbook object: since the library isn’t referenced, the compiler doesn’t know about these classes, or any of the xlXYZ global constants defined in that library.

Things get hairy when you start using late binding for libraries that are essentially guaranteed to exist on every Windows machine built this century. Like Scripting, or several others – if your code can’t work without these libraries present, late-binding them isn’t going to solve any problem. Rather, it will likely cause more of them… because late-bound code will happily compile with typos and glaring misuses of a type library; you don’t get IntelliSense or parameter QuickInfo as you type, and that is basically the best way to run into run-time error 438 (member not found):

Dim d As Object
Set d = CreateObject("Scripting.Dictionary")
d.Add "value", "key" 'or is it "key", "value"?
If d.ContainsKey("key") Then 'or is it d.Exists("key")?
End If

It’s not about project references

Late binding isn’t about what libraries are referenced and what types need to be created with CreateObject though: not referencing a library forces you to late-bind everything, but late binding can (and does!) also occur, even if you don’t use anything other than the host application’s object model and the VBA standard library: every time anything returns an Object and you make a member call against that object without first casting it to a compile-time known interface, you are making a late-bound member call that will only be resolved at run-time.

Try typing the below examples, and feel the difference:

Dim lateBound As Object
Set lateBound = Application.Worksheets("Sheet1")
latebound.Range("A1").Value = 42

Dim earlyBound As Worksheet
Set earlyBound = Application.Worksheets("Sheet1")
earlyBound.Range("A1").Value = 42

Worksheets yields an Object that might be a Worksheet reference, or a Sheets collection (depending if you’ve parameterized it with a string/sheet name or with an array of sheet names). There are dozens of other methods in the Excel object model that return an Object. If you’re automating Excel from VB.NET with Option Strict turned on, late-bound member calls are outright forbidden.

VBA is more permissive, but it is our duty as VBA developers, to understand what’s happening, why it’s happening, and what we can do to make things more robust, and fail at compile-time whenever it’s possible to do so. By systematically declaring explicit types and avoiding member calls against Object, we not only accomplish exactly that – we also…

  • Learn to work with a less permissive compiler, by treating late-bound calls as if they were errors: hopping into the .NET world will be much less of a steep learning curve!
  • Learn to work better with the object model, better understand what types are returned by what methods – and what to look for and what to research when things go wrong.
  • Write code that better adheres to modern programming standards.

Late binding isn’t inherently evil: it’s a formidable and powerful tool in your arsenal. But using it when an early-bound alternative is available, is abusing the language feature.

Whenever you type a member call and the VBE isn’t telling you what the available members are, consider introducing a local variable declared with an explicit type, and keeping things compile-time validated – as a bonus, Rubberduck will be able to “see” more of your code, and inspections will yield fewer false positives and fewer false negatives!

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…