Solved

Clear a collection with VBA

Posted on 2014-03-21
5
13,404 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 45

Accepted Solution

by:
aikimark earned 500 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 45

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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…
Nice table. Huge mess. Maybe this was something you created way back before you figured out tabs or a document you received from someone else. Either way, using the spacebar to separate the columns resulted in a mess. Trying to convert text to t…
This video shows where to find templates, what they are used for, and how to create and save a custom template using Microsoft Word.
Office 365 is currently available in five editions. Three of them are for business use: Office 365 Business Essentials, Office 365 Business, and Office 365 Business Premium. Two of them are for home/personal use: Office 365 Home and Office 365 Perso…

688 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