Late Binding Global Variables?

问题:

I'm using VBA for Excel. From my understanding, global variables need to be declared outside of any subs. That's the only way they can be accessed by all subs.

At the meantime, I want to do late binding to reference the "Microsoft Scripting Runtime" library(in order to use the dictionary object type) so that an end user doesn't have to do it himself.

My code is as below:

On Error Resume Next
strGUID = "{420B2830-E718-11CF-893D-00A0C9054228}"
ThisWorkbook.VBProject.References.AddFromGuid GUID:=strGUID, Major:=1, Minor:=0

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

What if I want to declare global dictionary object with late binding? It looks like VBA won't allow me to put any code outside of the subs (other than the declarations).

How may I declare a global dictionary object without needing the end user configure library reference himself? Shall I don the following?

Dim Dic1 As Object
Dim Dic2 As Object

Sub Prog1()
    On Error Resume Next
    strGUID = "{420B2830-E718-11CF-893D-00A0C9054228}"
    ThisWorkbook.VBProject.References.AddFromGuid GUID:=strGUID, Major:=1, Minor:=0

    Set Dic1 = CreateObject("Scripting.Dictionary")
    Set Dic2 = CreateObject("Scripting.Dictionary")
End Sub

回答1:

Like the VBA code itself, project references don't magically disappear when your user opens your host workbook. They're saved along with the code in the host document.

So, the premise of your question is wrong: users never need to tweak project references.

Also the Scripting Runtime type library is standard issue and has been shipped the exact same version on every single Windows machine built this century (even before that), which means unless your code needs to run on a Mac, there's no need to ever late-bind the Scripting Runtime library.

And if your code needs to run on a Mac, the library won't late-bind anyway because it won't be found on the host machine, so late-binding the Scripting Runtime only serves to make silly typos and introduce other easily avoidable bugs that IntelliSense helps preventing.

---

ThisWorkbook.VBProject.References.AddFromGuid GUID:=strGUID, Major:=1, Minor:=0

This defeats the entire purpose of late-binding: it's using the VBIDE extensibility library (which requires lowered macro security settings) to programmatically add a reference that you can easily add at design-time through the VBE's Tools menu.

Late-bound code doesn't need the reference at all. Not at compile-time, not at run-time.

Add the reference, save, then declare your objects As Scripting.Dictionary and enjoy the benefits of early-bound code.

Set Dic1 = New Scripting.Dictionary

That's all you need.

---

What if I want to declare global dictionary object with late binding? It looks like VBA won't allow me to put any code outside of the subs (other than the declarations).

Late binding isn't any different than early binding in that aspect. The only difference between late and early bound code is the As clause of the declaration:

Private foo As Object ' no compile-time type knowledge: late-bound
Private bar As Dictionary ' compile-time type knowledge: early-bound

How you're initializing that object reference makes no difference to the late/early binding nature of the declaration.

This looks up a ProgID in the registry to find the library and the type:

Set foo = CreateObject("Scripting.Dictionary")

This uses the project references:

Set foo = New Scripting.Dictionary

Both are correct, and both will work against either early or late-bound declarations. Except, if you already have a reference to the type library, there's not really a need to go hit the registry to locate that library - just New it up!

回答2:

Global variables are really not needed and should be avoided. However, if you have decided to use them for a your own reasons, you can put them in the Workbook_Open event:

Option Explicit

Dim Dic1 As Object
Dim Dic2 As Object

Private Sub Workbook_Open()

    Set Dic1 = CreateObject("Scripting.Dictionary")
    Set Dic2 = CreateObject("Scripting.Dictionary")

End Sub

Thus, it would create the object every time the workbook is opened.