Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Clear a collection with VBA

Posted on 2014-03-21
5
Medium Priority
?
15,631 Views
Last Modified: 2014-03-22
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
Comment
Question by:JohnRobinAllen
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 15

Expert Comment

by:DrTribos
ID: 39946594
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
 
LVL 46

Accepted Solution

by:
aikimark earned 2000 total points
ID: 39947434
@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
 

Author Closing Comment

by:JohnRobinAllen
ID: 39947577
Aikimark's solution is beautiful and elegant. I'm most grateful and will use it frequently.
              j.r.a.
0
 
LVL 15

Expert Comment

by:DrTribos
ID: 39947960
aikimark,

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

Cheers,
0
 
LVL 46

Expert Comment

by:aikimark
ID: 39948260
@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

Tech or Treat! - Giveaway

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Do you ever need to create a 20 page Word document for some testing purpose? Are you tired of copying & pasting old boring "lorem ipsum" text over and over again, increasing font size and line space in order to make the document 20+ pages long? Look…
Microsoft Word is a program we have all encountered at some point, but very few of us have dug deep into its full scope of features, let alone customized it to suit our needs. Luckily making the ribbon (aka toolbar, first introduced in Word 2007) wo…
This video shows the viewer how to set up and create Footnotes in their document. Click on the References tab: Select "Insert Footnote": Type in desired text:
This video walks the viewer through the process of creating envelopes and labels, with multiple names and addresses. Navigate to the “Start Mail Merge” button in the Mailings tab: Follow the step-by-step process until asked to find the address doc…

609 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question