CODEWITHSUNDEEP
vbaexcel
keynesiancross
keynesiancross

Reputation: 3529

Excel 2013 VBA - Global Dictionary Variable Declaration

I'm trying to declare a public dictionary variable that will survive as long as the workbook is open. At the moment I'm using at the top of one of my modules:

Public dict As Scripting.Dictionary

I can access it from multiple subs within the same module, but as soon as all the code finishes running it goes out of scope. I've tried declaring it in the Workbook_Open() sub, but that doesn't seem to survive either.

The reason I'm doing this: I'm loading in a lot of external XML information that I will need to dynamically access while the sheet is open quickly (so I don't want to reload the xml each time). Instead I'd rather load the XML at the beginning, sort the relevant information into instances of custom classes that do the work I need.

Any suggestions?

Thanks

EDIT:

I've added the code as per the suggestion below, but the dictionary still seems to go out of scope as soon as the code finishes running. When other subs try to access it, I get an "Object Required" error

    Option Explicit
Public dict As Scripting.Dictionary

Private Sub Workbook_Open()
    Set dict = New Scripting.Dictionary
    dict.Add "ID", New cItemClass
End Sub

Upvotes: 0

Views: 3254

Answers (1)

PeterT
PeterT

Reputation: 8557

Add the public Dictionary declaration to the ThisWorkbook module. It's also a good place to put your Workbook_Open() event handler to populate it.

This can work as long as the data from the XML file doesn't change while your workbook is open. You can program other triggers to cause a refresh of the data if necessary. Depending on the format of the XML, you could also store the XML data on a hidden worksheet to use as global storage.

EDIT: added code sample

Option Explicit

Public xmlDict As Scripting.Dictionary

Private Sub Workbook_Open()
    Set xmlDict = New Scripting.Dictionary
    '--- read your XML file here and initialize
    '    the dictionary
End Sub

As an alternative (and depending on how you are storing and accessing the data in the dictionary) you could create your own Class to access the stored data, using Get properties and disassociate the structure of the data from the calling functions. (It could still be a Dictionary, or any other data storage/structure, and always keep the same interface.)

Upvotes: 1

Related Questions