Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

VB Clear Scripting.Dictionary object

Tags:

excel

vba

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!

like image 821
maephisto Avatar asked Mar 23 '12 09:03

maephisto


People also ask

What is Dictionary object vbscript?

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.

What is scripting Dictionary in VBA?

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.

What is the difference between collection and Dictionary in VBA?

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.


2 Answers

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...

like image 56
T. Fabre Avatar answered Oct 20 '22 10:10

T. Fabre


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.

like image 5
assylias Avatar answered Oct 20 '22 10:10

assylias