Clearing a Collection

[Guest]

I have created a collection using:

dim collMycollection as new collection

and populated it. Now I want to clear it and reuse it. What is the command
to do so? I've tried:

collMycollection.clear

But that doesn't seem to be supported. Any ideas?

 

[JE McGimpsey]

One way:

First, you should NEVER "Dim as New". See the "Don't Use The New Keyword
In A Dim Statement" section here:

http://cpearson.com/excel/variables.htm

Doing so adds hidden overhead that slows your code

Dim colMyCollection As Collection
Dim i As Long

Set colMyCollection = New Collection
For i = 1 To 10
colMyCollection.Add Cells(i, 1)
Next i
Debug.Print colMyCollection.Count
Set colMyCollection = Nothing
Set colMyCollection = New Collection
Debug.Print colMyCollection.Count

I suspect the Set colMyCollection = Nothing isn't strictly necessary,
but it seems appropriate to me...

 

[Guest]

What kind of collection is it? You may want to add a little more detail to
get an accurate answer.

 

[Jim Cone]

If you declare your collection as...

Dim collMyCollection as Collection
Set collMyCollection = New Collection

Then you can set the collection to nothing to clear it...
Set collMyCollection = Nothing
(providing you have not used another object to also reference the collection)
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"Max"
wrote in message
I have created a collection using:
dim collMycollection as new collection
and populated it. Now I want to clear it and reuse it. What is the command
to do so? I've tried:

collMycollection.clear

But that doesn't seem to be supported. Any ideas?

 

[Guest]

Max,

If you really do need to keep the existing colection object intact (and
there can be good reasons - like if you've made yourself a custom
typed-collection class object with "extras" on it you don't want to loose).

To empty out a collection you basically need to do this:
for i = myCollection.count to 1 step -1 'MUST count DOWN from the top
'you CANNOT use a For Each loop here!
myCollection.remove i
next i

If your collection contains object instances, then you may need to enhance
that to properly destroy/close/terminate those objects (especially if you're
in .Net or using Office/ActiveX Automation classes):

for i = myCollection.count to 1 step -1 'MUST count DOWN from the top
'you CANNOT use a For Each loop here!
set objTemp = mycolelction.item(i)
myCollection.remove i
objtemp.Dispose() ' if the class implements the iDisposable interface or a
Dispose method like most .Net classes should
set objtemp = nothing
next i

The reason for this is that as you remove items from the colelction, the
rest of the items "settle downwards" towards item #1. So loops that count
upwards get about half-way up and error out, having only deleted about half
the collection items.

of course you could also do something like this:
do while mycollection.count > 0
mycollection.remove 1
loop
....and you'd achieve the same thing (unless there are objects involved
needing more careful cleanup, like Office automation objects or .Net objects
again).

 

[Guest]

I think this worked! Thanks for the help!