When Is Late Binding Advantageous for VBA Developers?

What is Late Binding and when would you use it?

Syntax of Late Binding

In syntax terms, late binding is when you declare a variable with As Object instead of giving its Type. So for example scripting against the Excel object ...

Option Explicit

Sub Test()

    '* Early-bound - requires Type Library (Tools References)
    Dim ws As Excel.Worksheet
    
    '* Late-bound - uses IDispatch - slower but more flexible
    Dim objWs As Object
    
    Set ws = ThisWorkbook.Worksheets.Item(1)
    Set objWs = ThisWorkbook.Worksheets.Item(1)
    Debug.Print ws.Name
    Debug.Print objWs.Name
End Sub

In the above example objWs can access all of the methods of Worksheet without reference to a Type Library (Tools References) it does this via the IDispatch interface which is a flexible dynamic type discovery interface that interrogates an object at run-time instead of at compile time.

When would you use Late Binding?

Well the above example is not a good use case. There is no need to use late binding when the library you are scripting against is written and published by a proper software house such as Microsoft. Late-binding was invented for scripting clients like VBScript. However, VBA developers can find advantage from using late-binding when working in a large organisation and co-operation between development teams is not optimal.

At some point in your career, you may find your VBA code depends on a library from another team in your large organisation. The rules of COM interfaces are clear, don't break an interface once published, new behaviour needs to ship in a new interface or also in a new object that goes with the new interface. But sh*t happens, a team changes an interface which breaks your VBA code, how is the break handled?

If you are using early binding and type libraries then you will get a compile error. Imagine that scenario, you get a support call from an important user saying your spreadsheet broke along the lines of "Compile error in hidden module:foo"
You would have to visit (or remote into) their machine, unlock the code (assumes password protection) and go figure out what went wrong. This can be quite embarrassing.

On the other hand if you are using late-binding then your code will only break on a line of code directly affected with the error message

This kind of error is trappable with On Error Goto whilst a compile error is not!

What are downsides to Late-Binding?

Well, you lose Intellisense which can be a boon to writing code but there is nothing to stop you from writing code with early-binding and then changing to late binding when going to test and production.

Also, we have to address speed. There is no doubt that the extra calls into IDispatch carry a performance penalty though for in-process DLL calls I'd say this is minimal. When you have a remote component then I would suggest not using late-binding because network calls are very expensive. For components running locally on the computer but in another process, the call is 50/50.

Summary

Although IDispatch was invented for scripting clients like VBScript it also provides an option for VBA developers to defend against breaking changes in type libraries from other teams.

I'm not religious about this issue but the having the option to late-bind requires the developers of the component you rely on to ship an IDispatch implementation and sometimes this has its own consequences.

I've just rustled up this post because I want to address an ATL design dilemma and needed to sketch out the stakes.