0

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
keynesiancross
  • 3,441
  • 15
  • 47
  • 87

1 Answers1

1

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.)

PeterT
  • 8,232
  • 1
  • 17
  • 38
  • Thanks a lot for this. The XML data will change periodically, but the user driving it will know (and I'm building a separate function to load new nodes, which would load it to this public dictionary) Do you mind adding a code example though, so I can see the syntax? Super basic is fine. – keynesiancross Apr 18 '16 at 19:35
  • Another question - if I put the public variable into the ThisWorkbook module, do all my other Subs + Functions that reference that variable also need to be in that module? – keynesiancross Apr 18 '16 at 19:37
  • The `xmlDict` object declared in the `ThisWorkbook` example above is available to methods in any other module, userform, or class. So your code would make a straightforward reference to it `xmlDict.Item(n)`. – PeterT Apr 18 '16 at 19:44
  • I've just tried that - but when I have another sub reference the dictionary after the ThisWorkbook_Open() kicks off and finishes, I get an "Object required" error – keynesiancross Apr 18 '16 at 19:48
  • 1
    Excellent information in [this post](http://stackoverflow.com/a/27578082/4717755) – PeterT Apr 19 '16 at 13:58