3

I have a Worksheet_BeforeDoubleClick event that checks to see whether a cell that's clicked has data that's in a Dictionary object, like so:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target as Range, Cancel as Boolean)

Dim dict as Dictionary
Dim df as New dictFactory

'returns a dictionary populated with all list items
Set dict=df.create

If dict.Exists(Target.Value) Then
 MsgBox "Exists"
Else
 MsgBox "Doesn't exist"
End If

End Sub

The problem is that this requires creating a new dictionary each time a cell is clicked. I thought it would be good to store the dictionary in a global variable in its own module, like this:

Global valuesDict As New Dictionary

and then populate it upon opening the workbook:

Private Sub workbook_open()

Dim df as New dictFactory
Set valuesDict=df.create

End Sub

But I've encountered a lot of problems with this during testing, because there are many conditions under which a global variable's value can be reset (as discussed here).

How can I store an object so that its value will be available as long as the workbook is open, throughout repeated calls to my BeforeDoubleClick event?

Community
  • 1
  • 1
sigil
  • 9,370
  • 40
  • 119
  • 199
  • make your code stable and this will be the best warranty to keep you `Public variable` values. BTW, why `Global` and not `Public`? – Kazimierz Jawor Oct 18 '13 at 21:02
  • 1
    Store it as a global, but check before using it to see if it's `Nothing`: if it doesn't exist then recreate it using `df.Create` – Tim Williams Oct 18 '13 at 21:02

2 Answers2

5
Global valuesDict As Dictionary 'EDIT - drop the "new"

Private Sub Worksheet_BeforeDoubleClick(ByVal Target as Range, Cancel as Boolean)

'populate global only when needed
if valuesDict is Nothing then CreateDict

If dict.Exists(Target.Value) Then  MsgBox "Exists"
Else
 MsgBox "Doesn't exist"
End If

End Sub
'


Private Sub workbook_open()
    CreateDict
End Sub
'


Sub CreateDict()
    Dim df as New dictFactory
    Set valuesDict=df.create
End sub
Tim Williams
  • 154,628
  • 8
  • 97
  • 125
0

It's true that data of a module level variable (a.k.a. global variable) persists until the workbook is closed but an incomplete execution of code (due to a bug or deliberate interruption) will reset the variable, wiping out that data. It happens to static variables as well which work like module level variables in terms of duration even though static variables are local in scope.

To be safe, you can write code in the worksheet module to check if the global variable (referencing a dictionary) is valid, if not, run a dedicated procedure to recreate a dictionary.

BTW, the dictionary object has no Create method.

Gjedot
  • 1
  • 2
  • Right, I wrote a Create method to gather a data set and then populate the dictionary from that set. – sigil Oct 21 '13 at 17:31