Excel: How to kill a scripting dictionary object during a cycling process that uses the dic once per cycle

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

Sub hyperCycle()
    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
        End If
        Call cycleOnce(h)
    Next h
End Sub

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
    Next k
End Sub

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.

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.


if you use method 3 there is no point in resetting an object t to a new dict

why not simply reset to nothing after the call cycleOnce


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
NorieAnalyst Assistant Commented:
I don't think you can do this.
IF dic = Nothing then dic.removeAll

Open in new window

If dic is Nothing then you can't remove anything from it.
Kelvin4Author Commented:
Just to check, do you ask: why not reset to Nothing after dic has been loaded with data and used as a resource?

Some of the dictionaries have quite long lives within the single xl cycle, as the project creates a graphic user interface (via visio in early binding). Thus (before the start of the next xl cycle)  the point at which one resets any one dictionary to Nothing will be deep in code and may need to have its position in the code moved, as the code is extended.  

I only have 3 years experience, so does what I write make sense?

Kelvin4Author Commented:
 I agree! The code has been badly paragraphed, but it does work on test:

 If dic Is Nothing Then
        Else '<=================================
            'Set dic = Nothing '***** 2
            'dic.RemoveAll '***** 3
        End If

and should read:

        If dic Is Nothing Then
            'Set dic = Nothing '***** 2
            'dic.RemoveAll '***** 3
        End If

        If Not dic Is Nothing Then    
            'Set dic = Nothing '***** 2
            'dic.RemoveAll '***** 3
        End If
Kelvin4Author Commented:
Thanks, I found that I had created my own problem. Originally I had created a dic (Dictionary) in a private sub, and then (a year later) tried to Set dic to Nothing in another module. Thanks for your participation.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.