I'm writing an Excel macro, and I'm having trouble clearing a Scripting.Dictionary object:
Dim test As Integer
test = CompListDict.Count
CompListDict.RemoveAll
Set CompListDict = Nothing
Set CompListDict = CreateObject("Scripting.Dictionary")
Dim test1 As Integer
test1 = CompListDict.Count
Before I do this, I add items to the dictionary, and then i try to clear it, but test1 will equal test and equal the nr of objects I've added.
What am I doing wrong ? Thank you!
A Dictionary object is the equivalent of a PERL associative array. Items, which can be any form of data, are stored in the array. Each item is associated with a unique key. The key is used to retrieve an individual item and is usually an integer or a string, but can be anything except an array.
The scripting dictionary is a way to store unique items via a key and item (Keys and Items are terms in the dictionary. It is a fantastic tool to store data based on a unique key. It is powerful in that it the keys can be used to store and consolidate data.
You can assign a Key to an Item when you add the Item to the Collection, but you cannot retrieve the Key associated with an Item nor can you determine (directly) whether a key exists in a Collection. Dictionaries are much friendly and open with their keys. Dictionaries are also considerably faster than Collections.
If I run the following macro on my workstation, it works :
Set compListDict = CreateObject("Scripting.Dictionary")
compListDict.Add 1, "Test"
Dim test As Integer
test = compListDict.Count
compListDict.RemoveAll
Set compListDict = Nothing
Set compListDict = CreateObject("Scripting.Dictionary")
Dim test1 As Integer
test1 = compListDict.Count
After running it, test1 equals 0, and test equals 1.
Make sure you have Option Explicit enabled, and that you don't have any typos in your variable names.
Also, make sure you don't have an "On Error Resume Next" statement, as it will hide away errors in your code. Try placing "On Error Goto 0" before your code snippet, so that Excel will display any error message.
Since you're setting the variable value to Nothing, and assigning it a new dictionnary object, it should be impossible that it retains the previsouly stored values.
I also tried running the following code, and it also gives the same results:
Set compListDict = CreateObject("Scripting.Dictionary")
compListDict.Add 1, "Test"
Dim test As Integer
test = compListDict.Count
compListDict.RemoveAll
Dim test1 As Integer
test1 = compListDict.Count
Hope that helps...
Your code looks ok although the 2 lines where you set your dict to nothing and recreate it are not necessary.
Not sure if it's related but there is a bug in some versions of VBA IDE: if you have added a watch on dict(aKeyThatDoesNotExist)
it can lead to tgat key being added to the dict and not being removable. the only solution I know: restart Excel to clear the memory.
EDIT
For Siddharth: Tested with Excel 2003 & 2010.
Create a new book.
Open VBA IDE.
Type this in the Sheet1 module:
Option Explicit
Sub test()
Dim d As Variant
Set d = CreateObject("Scripting.Dictionary")
d.Add "a", "a"
Debug.Print d.Count
'Add watch here on d and d("b")
Debug.Print d.Count
End Sub
Run it in step by step mode and when on the commented line, add a watch on d
and d("b")
.
The second Debug.Print
will print 2. So far, you could think that the watch created an entry, which is weird because you would not expect watch to have side effects.
Run the macro again: the first Debug.Print
will print 2 and you will notice that the dictionary already has a "b" key.
Actually, contrary to what I said above, removing the watch on d("b")
and rerunning the macro will reset the dictionary properly.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With