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!
Rubberduck News 