VBA Dictionary Destroy
It is good practice to destroy a dictionary that is no longer needed.
1 2 3 4 5 6 | Dim D As Dictionary Set D = New Dictionary '... Set D = Nothing |
By Set D = Nothing the object reference that is assigned to the variable is released.
Some prefer this.
1 2 3 4 5 6 | Dim D As Dictionary Set D = New Dictionary '... If (Not (D is Nothing)) Then Set D = Nothing |
After Set D = Nothing the variable of the type dictionary still exists. It can still be used. You can assign a new object reference to it.
1 2 3 4 5 6 7 8 | Dim D As Dictionary Set D = New Dictionary '... Set D = Nothing Set D = New Dictionary '... |
VBA will forgive you when you do not destroy your dictionaries. When the variable goes out of scope, the object reference is released automatically by VBA. The line Set D = Nothing is just an explicit way of doing that yourself.
The VBA editor will not warn you if you are working with an dictionary that has been destroyed. The code compiles. Intellisense still works.
1 2 3 4 5 6 7 8 | Dim D As Dictionary Set D = New Dictionary D.Add "Key", "Item" MsgBox D.Count() 'shows 1 Set D = Nothing MsgBox D.Count() 'gives a run time error |
Be careful when the dictionary has been created by the Dim ... As New ... statement. An object reference will be created automatically if you use the variable again after destroying it.
1 2 3 4 5 6 7 8 9 10 11 12 | Dim D As New Dictionary D.Add "Key", "Item" MsgBox D.Count() 'shows 1 Set D = Nothing If (D Is Nothing) Then MsgBox "D is nothing" Else MsgBox "D is not nothing" 'shows D is not nothing End If MsgBox D.Count() '0 |
The line If (D Is Nothing) Then assigns a new object reference.
We find this code behavior a bit strange and hard to remember. For this reason, we never create dictionaries using the Dim ... As New ... statement.
The method RemoveAll does not destroy the object. It removes all key/item pairs, so that method Count will return 0.
1 2 3 4 5 6 7 8 9 | Dim D As Dictionary Set D = New Dictionary D.Add "Key", "Item" MsgBox D.Count() shows 1 D.RemoveAll MsgBox D.Count() 'shows 0 If (Not (D Is Nothing)) Then MsgBox "Not destroyed." 'shows Not destroyed |
