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!
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.
d("b") for example, where "b" is a key that has not been added to the dictionary before.d("b") is a shortcut for d.Item("b") which does create a new key if it did not already exist. It is just weird that the debugger watch window has side effects on the actual variables but I can see why now.