VBA - Global Variable dropping out of scope

1
votes

I'm having an issue with a global variable dropping out of scope. I have defined a public variable in "ThisWorkbook":

Public dict As Scripting.Dictionary

This gets initalized in "Workbook_Open()" 

Set dict = New Scripting.Dictionary

After initialization I run a Sub (code located in "ThisWorkbook" still) that fills this dictionary with IDs and instances of a custom class.

I'm having trouble working with this dict variable in other modules though. The goal is to build a number of public functions that the worksheet will be able to call. These functions manipulate/retrieve/etc data in the custom classes in the dictionary.

For example, this test sub (code in ModuleXYZ) throws "Object variable or With block variable not set"

Private Sub TestSub()   
    Dim x As Integer
    x = ThisWorkbook.dict.Count
End Sub

This is the same error I would get when I first started this coding project, when the dict fell out of scope in the "ThisWorkbook" module, and I'd have to redo the "Set dict = New Scripting.Dictionary"

My hope was that by setting the dict as a Public variable in "ThisWorkbook", that it would stay in scope the entire time this workbook was open.

Thanks - KC

2
vbaexcel
It should remain as long as you don't do something to reset the project (same is true of all public variables). – Rory

2 Answers

3
votes

Try declaring the public variable in a module rather than the workbook. Also check that you haven't declared it twice. Convention is to declare all globals in one module which is often called globals.bas.

1
votes

In cases like these, I sometimes like to use a Singleton pattern of sorts (at least, this is my understanding of the singleton pattern). I create a publicly available function that either returns the object if it exists, or creates then returns the object. This way, you don't have to worry too much about it falling out of scope.

Here's an example: 

Public oDict As Object

Public Function GetDictionary() As Object
    If oDict Is Nothing Then
        Set oDict = CreateObject("Scripting.Dictionary")
        For Each cel In Range("A1:A10")
            oDict.Add cel.Value, cel.Offset(, 1).Value
        Next cel
    End If

    Set GetDictionary = oDict
End Function

To reference it, it's like:

Sub GetDictCount()
    MsgBox GetDictionary().Count
End Sub

Sub OtherDictTest()
    MsgBox GetDictionary()(1)
End Sub