I am using a scripting dictionary in VBA to hold 1,840,000 key and value pairs in Excel 2010. I want to declare and populate them once, then use them in my modules and functions.
For starters, I declared the dictioanry as public, using Public dict As Scripting.Dictionary, then I populated it on worksheet_activate(). How do I save it and use it in other mods?
Thank you
As a good programming practice, wrap such a variable in a getter function, and hide the global variable as a static local inside it. Populate it on first use.
To do that, write this function inside a standard module:
Public Function getMyDictionary() as Scripting.Dictionary
Static dict as Dictionary ' static: will keep state across different calls
If dict Is Nothing Then
Set dict = new Scripting.Dictionary
''''''''''''''''''''
dict.Add "foo", "bar"
' etc...
' Code to populate dictionary
'
''''''''''''''''''''
End If
Set getMyDictionary = dict
End Function
Now whenever you need to reference the dictionary, simply type something like this:
If getMyDictionary.Exists("foo") Then doStuff
This idiom has many advantages:
the dictionary is populated on first use. If during an Excel session it is not needed, it will not be populated.
you no longer need to worry about "when do I populate my dictionary." It will be available and populated whenever and wherever it is needed
if the project unloads due to some runtime error, all global variables are reset. In particular, objects are reset to Nothing. The wrapper function handles correctly the situation and re-populates the dictionary in a transparent way.
TLDR... the global dictionary is hidden, access to it is (to some extent) controlled. "To some extent" because other code can still manipulate it (do insert, remove...). If one needs more control over it, for example allow other code to only read it, then make it a private member of some Class Module that exposes only the allowed functionality...
Set dict = new Scripting.Dictionary, I just added it :)getMyDictionary. Of course you can use another name for the function, but consider its name as the name of your dictionary in all other code.
End) will result in any global/static variables being lost.