Clear a collection with VBA

I use VBA collections with Word extensively. I initiate such a collection (here named “X”) with this command:

Dim X As New Collection.

I can then manipulate data in the collection with the commands Add, Item, or Remove. In the following, Key is a unique string, and Data or NewData are strings of information I want to store or retrieve from the collection X under the Key.

To add something I use:

X.Add Key:=Key, Item:=Data
To read the data stored into strDataStored I can use

strDataStored = X.Item(Key)
To change the data stored I first remove the key with the old data, and then I add new data under the same Key:

X.Remove (Key)
X.Add Key:=Key, Item:=NewData
Is there any way that when I no longer need a collection I could use VBA to clear all the data from a collection? Visual Studio has the command “.Clear” that could do the trick, but “Clear” will not work with VBA.

A possible solution that I would dread to use would be to store in a different array all the Keys used with the collection, and then go through each item in that array to Remove it from the collection. I think I would prefer each time to start a new collection under a different name and hope I do not run out of memory with all the collections, most of which would no longer be needed.

Any suggestions are most welcome. Each time I post a query with EE, I learn a bit more.

John Robin (Allen) in Priddis, Alberta, Canada
Who is Participating?
aikimarkConnect With a Mentor Commented:

As DrTribos alludes, there is a different key/value object you can use in the VBA environment -- the scripting dictionary.  It is an ActiveX object that you can use either with a program reference or, as shown above, with the createobject() function.  The dictionary object has a .RemoveAll method.  Here is a good article on the dictionary object:

If you just need a way to empty your collection, add this routine
Public Sub ClearCollection(parmCol As Collection)
    Do Until parmCol.Count = 0
        parmCol.Remove 1
End Sub

Open in new window

You pass it the collection you want to clear.
Public Sub testit()
    Dim x As New Collection
    x.Add "M", "mark"
    x.Add "F", "Fred"
    Debug.Print x.Count
    ClearCollection x
    Debug.Print x.Count
End Sub

Open in new window

Hi there,

I use a scripting dictionary to do what you describe.

Dim odict As Object
  Set odict = CreateObject("Scripting.Dictionary")
<do stuff>
 Set odict = Nothing
JohnRobinAllenAuthor Commented:
Aikimark's solution is beautiful and elegant. I'm most grateful and will use it frequently.

Thank you for the link to Patrick's article.  Also the demonstration of the collection is new to me.


You're welcome.  You can treat items in a dictionary object just like variables.  You can update the value without having to delete the existing item and add the new value.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.