Clear a collection with VBA
I use VBA collections with Word extensively. I initiate such a collection (here named “X”) with this command:
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:
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
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. X.Add Key:=Key, Item:=NewData
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Aikimark's solution is beautiful and elegant. I'm most grateful and will use it frequently.
j.r.a.
j.r.a.
aikimark,
Thank you for the link to Patrick's article. Also the demonstration of the collection is new to me.
Cheers,
Thank you for the link to Patrick's article. Also the demonstration of the collection is new to me.
Cheers,
@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.
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.
I use a scripting dictionary to do what you describe.
Dim odict As Object
Set odict = CreateObject("Scripting.Di
<do stuff>
Set odict = Nothing