Storing Dictionary in cell

[PeterBunde]

Fellow passionate colleagues!

My code populates a Dictionary type variable. Later on, an event handler (when selecting a particular cell) needs access to that Dictionary. But how do I store it meanwhile? Is there a clever way to write it into a cell in some formate, then load it back into a dictionary?

BW Peter Bunde Hansen

 

[wideboydixon]

Make the dictionary a public member of the worksheet class that's going to use it by putting it at the top of code, outside of any functions or subs. For example:

Public MyDictionary As Object
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

' Some code here

End Sub

When populating the dictionary, be sure to qualify it using the sheet name, for example:

Public Sub SetUpDictionary()

Set Sheet1.MyDictionary = CreateObject("Scripting.Dictionary")
'...

End Sub

WBD

 

[PeterBunde]

Hello WBD

Thanks for the answer; every time I generate my sheet, the old sheet is deleted and a new is inserted (with the same ). Does that conflict with your statement "Make the dictionary a public member of the worksheet"?

BW Peter Bunde Hansen

 

[wideboydixon]

In which case you'll need to make the dictionary a public member of a module. E.g. in Module1:

Public MyDictionary As Object
Public Sub SetUpDictionary()

Set MyDictionary = CreateObject("Scripting.Dictionary")

End Sub

Then refer to it like this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Module1.MyDictionary.Exists(Target.Value) Then MsgBox "Cool!"

End Sub

WBD

 

[PeterBunde]

Thnk you! It Works.