Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 17281
  • Last Modified:

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
0
JohnRobinAllen
Asked:
JohnRobinAllen
  • 2
  • 2
1 Solution
 
DrTribosCommented:
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
0
 
aikimarkCommented:
@John

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:
http:A_3391-Using-the-Dictionary-Class-in-VBA.html

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
    Loop
End Sub

Open in new window

You pass it the collection you want to clear.
Example:
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

0
 
JohnRobinAllenAuthor Commented:
Aikimark's solution is beautiful and elegant. I'm most grateful and will use it frequently.
              j.r.a.
0
 
DrTribosCommented:
aikimark,

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

Cheers,
0
 
aikimarkCommented:
@John

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

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now