VBA performance with Early Bind vs. Late Bind on called Sub-procedure

Posted on


I was using early bind to reference the dictionary and realised that some users might not be willing to manually add the reference to use it and just consider the macro to be ‘broken’.

I looked at programmatically enabling the reference but that would still require the user to go through several steps to change their macro security level, which again would seem ‘broken’ to an end user.

So my solution is to offer early bind and late bind options to the user, early bind for those comfortable with the VBE, late bind if not.

My question is, would the potential efficiency/performance gain in early binding still be present in going with this method, or is it wiped out simply by having to call to another sub?

If the gain is lost, I can simply use late bind within the main sub-procedure.

Option Explicit

Public Sub MatchArraysEarlyBind()

    Dim arrayMatchDictionary As Dictionary
    Set arrayMatchDictionary = New Dictionary

    Call ContinueMatchArray(arrayMatchDictionary)

End Sub

Public Sub MatchArraysLateBind()

    Dim arrayMatchDictionary As Object
    Set arrayMatchDictionary = CreateObject("Scripting.Dictionary")

    Call ContinueMatchArray(arrayMatchDictionary)

End Sub


Kudos for Option Explicit and explicitly making the procedures Public – I suppose the ContinueMatchArray procedure looks something like this?

Private Sub ContinueMatchArray(ByVal arrayMatchDictionary As Object)
    'do something
End Sub

This arrayMatchDictionary As Object parameter is nice – you have a dependency and you’re putting its creation in its own method, and the dependency can be a Dictionary obtained with New Dictionary or CreateObject, it makes no difference… because as far as VBA is concerned, this arrayMatchDictionary is late-bound, regardless of whether it was MatchArrayMatchEarlyBind that created the object.

Calling members of an Object type means the runtime is going to have to query the IDispatch interface of the object to discover the handle to the member being called.

By declaring the parameter As Object, you’re actually downcasting to the simplest COM interface that is known to VBA – as long as the object is in scope, to the face of the world that procedure its underlying type is unknown.

The VBA type casting mechanism involves Setting a reference to another pointer type:

Dim foo As Object
Set foo = New Scripting.Dictionary

Here foo is a Dictionary, but even if the reference assignment was early bound, every single member call to foo will be late-bound – you’re asking the underlying COM engine to query the object’s interface, and because we’re looking at an Object, that interface is IDispatch; the query locates a function pointer in the object’s vtable, or blows up if it can’t find it.

The performance penalty with late binding isn’t only with the original reference assignment: every single member call incurs a hit.

An early-bound member call is easy: the runtime doesn’t need to query IDispatch, it has the actual object’s interface handy, so the whole “lookup the function pointer” part is skipped, because the function pointer’s location is already known.

ContinueMatchArray works late-bound in both cases.


Your code runs, but doesn’t compile: it relies on the interpreted nature of the language – if no scope in the execution path encounters an early-bound Dictionary, the code can run. But if you go Debug > Compile VBAProject, your project fails to compile:

Compile error: User-defined type not defined (arrayMatchDictionary declaration is highlighted in the editor)

Code that runs but doesn’t compile isn’t… ideal.

Explicit Call Syntax

Visual Basic for Applications (VBA) didn’t just appear; it evolved from older languages: this is a program written in Commodore-64 BASIC 2.0, which didn’t even have a Mod operator:

10 GOSUB 100
20 GOSUB 1000
99 END
110 PRINT CHR$(147)
210 LET MOD% = V%-INT(V%/FB%)*FB%
1010 LET FIZZ$ = "FIZZ"
1011 LET BUZZ$ = "BUZZ"
1020 LET FIZZ% = 3
1021 LET BUZZ% = 5
1030 LET MIN% = 1
1031 LET MAX% = 15
1100 PRINT FIZZ$ + ":" + STR$(FIZZ%)
1101 PRINT BUZZ$ + ":" + STR(BUZZ%)
1102 PRINT FIZZ$ + BUZZ$ + ":" + STR$(FIZZ%*BUZZ%)
1105 PRINT
2010 FOR X = MIN% TO MAX%
2015 LET RESULT$ = STR$(X)
2020 LET FB% = FIZZ%*BUZZ%
2021 LET V% = X
2024 GOSUB 200
2030 LET FB% = FIZZ%
2031 GOSUB 200
2040 LET FB% = BUZZ%
2041 GOSUB 200
2090 NEXT X

With pretty slight modifications, the BASIC 2.0 code above can be executed by the VBA runtime:

Sub Main()
10     GoSub 100
20     GoSub 1000
99     End
100     Rem CLEAR SCREEN
110     Debug.Print Chr$(147)
120     Return
200     Rem MODULO
210     Let Modulo% = V% - Int(V% / FB%) * FB%
220     Return
1010     Let FIZZ$ = "FIZZ"
1011     Let BUZZ$ = "BUZZ"
1020     Let FZZ% = 3
1021     Let BZZ% = 5
1030     Let Min% = 1
1031     Let Max% = 15
1100     Debug.Print FIZZ$ + ":" + Str$(FZZ%)
1101     Debug.Print BUZZ$ + ":" + Str(BZZ%)
1102     Debug.Print FIZZ$ + BUZZ$ + ":" + Str$(FZZ% * BZZ%)
1105     Debug.Print
2010     For X = Min% To Max%
2015     Let RESULT$ = Str$(X)
2020     Let FB% = FZZ% * BZZ%
2021     Let V% = X
2024     GoSub 200
2025     If Modulo% = 0 Then Let RESULT$ = FIZZ$ + BUZZ$: GoTo 2050
2030     Let FB% = FZZ%
2031     GoSub 200
2035     If Modulo% = 0 Then Let RESULT$ = FIZZ$: GoTo 2050
2040     Let FB% = BZZ%
2041     GoSub 200
2045     If Modulo% = 0 Then Let RESULT$ = BUZZ$: GoTo 2050
2050     Debug.Print RESULT$
2090     Next X
2099     Return
End Sub

So BASIC went from to structured programming, where “structure” started meaning procedures instead of line number ranges, and eventually modules too – and then the Call keyword showed up. BASIC eventually became Visual; comments were denoted by a single quote instead of a REM statement, and the explicit call syntax was abandoned, along with the explicit value assignment syntax: the Call and the Let keywords were deprecated. Oh you can still use them and they still work… just like the above program still works. It’s just that, the modern language has more elegant ways to do things.

Instead of this:

Call ContinueMatchArray(arrayMatchDictionary)

You can simply have this:

ContinueMatchArray arrayMatchDictionary

Don’t write dino-basic, use the implicit call syntax.


If you’re going to be working against an Object, don’t bother with early-binding: remove the reference and delete the early-bound method – at least your code will be compilable and so a compile error becomes meaningful.

On the other hand, why late-bind the Microsoft Scripting Runtime library? It’s on every Windows machine, and hasn’t changed this century, and is somewhat more likely to not ship at all with a new version of Windows than to be issued a new version anytime soon: if you add the project reference, it will work on every Windows user that uses it.

some users might not be willing to manually add the reference to use it and just consider the macro to be ‘broken’.

That makes no sense – if you save the VBA project with a reference to the scripting runtime, the reference isn’t gone when another user opens your macro-enabled workbook: if a user needs to manually add a reference, it’s because they broke it themselves.

If the referenced library is missing or unregistered on the other computer, late-binding isn’t going to make it work any better: there’s nothing to bind to. When you call CreateObject like this:

Set foo = CreateObject("Scripting.Dictionary")

You’re telling the runtime to fetch the CLSID from this registry key:


That CLSID is then used to locate the library:


And then the ProgId “Scripting.Dictionary” is used to locate the type and create the object, which is returned to VBA as an IDispatch – an Object.

If you don’t need late-binding, don’t late-bind – keep it simple:

Private Sub ContinueMatchArray(ByVal arrayMatchDictionary As Scripting.Dictionary)
    'do something
End Sub

Leave a Reply

Your email address will not be published. Required fields are marked *