Problem
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
Solution
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:
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:
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