I have a project that uses several dictionaries, which I now want to adapt for cyclic use, without the excel file being closed between cycles.
In the following snippet, I checkout ways of ensuring that repeated use of dic does not lead to the error report: "This key is already associated with an element in this collection"
The snippet is a 2-cycle process, in which controlling Sub hyperCycle() drives Sub cycleOnce(cycleNo As Integer);
Sub cycleOnce loads scripting dictionary dic.
[code lines '***** 1, '***** 2, '***** 3 are alternative solutions]
Public dic As Dictionary
Dim h As Integer
For h = 1 To 2
set dic = Nothing '***** 1
If dic Is Nothing Then
'Set dic = Nothing '***** 2
'dic.RemoveAll '***** 3
Sub cycleOnce(cycleNo As Integer)
Dim v As Variant
Dim k As Integer
Dim str As String
Set dic = New Dictionary ''''''''''''''create dic
For k = 1 To 5
str = cycleNo & "_" & k
dic.Add k, str
Via the three lines of code, marked '*****, I check out use of dic.removeAll (which does not destroy the object, or Set dic = Nothing, which does destroy it.
-- dic.removeAll needs to be conditional: IF dic = Nothing then dic.removeAll
-- Set dic = Nothing does not need to be under that condition
Is there any reason in good practice why I should not use the line: set dic = Nothing '***** 1 ?
The three optional lines of code appear at the START of each cycle, not the end. In the main Project there are several potential end points in the code for each cycle, so I decided that the dics, and 10s of other public variables, would be 'killed' at the start of each cycle, where I can see them together.
If there are pros and cons- I'd like to learn more.