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

Question

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

• Mathieu Guindon: Users don't need to manually add any references... a project's references are part of a project's definition and stored in the host document along with the source code - that whole solution is based on a wrong premise ...for a problem that doesn't exist.
• Mathieu Guindon: Also... the code wouldn't compile without a reference to the scripting runtime.
• Iain Saunders: The late bind option seems to work correctly after I removed the reference to the scripting runtime in the VBE? The idea behind this being that I have several different members on my team at varying levels of Excel knowledge, I want all of them to be able to use the code without relying on my being there and without relying on a magic workbook.
• Mathieu Guindon: Just saying, having code that says Dim something As Dictionary without a reference to the scripting runtime is asking for compile errors - I don't know what a magic workbook is, but every Windows box is going to have the scripting runtime available, so early-binding is sure to work for everyone, and nobody needs to tweak any project references. Early bound code will not compile without a reference, and late binding with a reference is a waste. Hence I'm not sure what problem this tries to solve.

Answer 1

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.

---

Compilation

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 fizzbuzz program written in Commodore-64 BASIC 2.0, which didn't even have a Mod operator:

10 GOSUB 100
20 GOSUB 1000
99 END
100 REM CLEAR SCREEN
110 PRINT CHR$(147)
120 RETURN
200 REM MODULO
210 LET MOD% = V%-INT(V%/FB%)*FB%
220 RETURN
1000 REM INIT VARIABLES
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
2000 REM ACTUAL FIZZBUZZ LOOP
2010 FOR X = MIN% TO MAX%
2015 LET RESULT$ = STR$(X)
2020 LET FB% = FIZZ%*BUZZ%
2021 LET V% = X
2024 GOSUB 200
2025 IF MOD%=0 THEN LET RESULT$=FIZZ$+BUZZ$ : GOTO 2050
2030 LET FB% = FIZZ%
2031 GOSUB 200
2035 IF MOD%=0 THEN LET RESULT$=FIZZ$ : GOTO 2050
2040 LET FB% = BUZZ%
2041 GOSUB 200
2045 IF MOD%=0 THEN LET RESULT$=BUZZ$ : GOTO 2050
2050 PRINT RESULT$
2090 NEXT X
2099 RETURN

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
1000 Rem INIT VARIABLES
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
2000 Rem ACTUAL FIZZBUZZ LOOP
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 fizzbuzz 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.

---

Early-bind?

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:

HKEY_CLASSES_ROOT\Scripting.Dictionary\CLSID

That CLSID is then used to locate the library:

HKEY_CLASSES_ROOT\Wow6432Node\CLSID{EE09B103-97E0-11CF-978F-00A02463E06F}

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

• Iain Saunders: That all makes complete sense, my parameter was actually (ByRef arrayMatchDictionary As Variant) which I am now willing to bet is worse? So the only way I would actually see the difference in performance is if every called sub where the dictionary is a parameter had it set As Dictionary?
• Mathieu Guindon: That's correct. I'm not willing to bet my shirt that the difference is observable to the naked eye, but depending on how the object is used it can make a significant difference.