Solved

Clear a collection with VBA

Posted on 2014-03-21
5
12,737 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

Enroll in May's Course of the Month

May’s Course of the Month is now available! Experts Exchange’s Premium Members and Team Accounts have access to a complimentary course each month as part of their membership—an extra way to increase training and boost professional development.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Get Started with Digital Signatures 4 53
Paragraph formating? 2 40
Calculate age in Microsoft Word 10 69
How to create a Fillable Form in Adobe Acrobat Pro. 7 16
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…
Preface: When I started this series, I used the term CommandBars because that is the Office Object class that it discusses. Unfortunately, when Microsoft introduced Office 2007, they replaced the standard Commandbar menus with "The Ribbon" and rem…
This video shows where to find the word count, how to display it, and what it breaks down to in Microsoft Word.
In a previous video Micro Tutorial here at Experts Exchange (http://www.experts-exchange.com/videos/1358/How-to-get-a-free-trial-of-Office-365-with-the-Office-2016-desktop-applications.html), I explained how to get a free, one-month trial of Office …

740 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