Clear/empty a collection object? 4

[VBAjedi]

I can't remember a fast way to clear/empty a Collection object. I have a Collection called myItems with thousands of items in it that I want to clear so I can reuse it, but I don't want to iterate through it. . .

VBAjedi

 

[fumei]

Collections unfortunately do not have RemoveAll - unlike a Dictionary object. This is a flaw IMHO. I don't think you can do it except by iterating Remove. Bummer. I am going to flag this one, just in case someone DOES know....but if you are asking, I'm kidda doubting.

Gerry

 

[VBAjedi]

Well, the good news is that to loop through a collection of 64,000 objects to clear it takes less than half a second. That definitely suprised me! The code I used is:

For x = 1 To myList.Count
   myList.Remove 1
Next x

So I guess it isn't a big deal. . .

VBAjedi

 

[TonyJollans]

Hi Jedi,

Can you not just Set the collection to Nothing?

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at [url=http://www.vbaexpress.

 

[VBAjedi]

Doh! Att'll do. . .



Sometimes the answer is too obvious to see! Have a star.

VBAjedi

 

[fumei]

Does that not simply set the instance of collection object to nothing? Does it really empty the collection of items? Learn something every day. Doh, indeed.

Gerry

 

[TonyJollans]

Hi Gerry,

I'm not quite 100% sure but my understanding is that if there are no references to an object, it is destroyed. I just ran a few tests and execution of setting a collection object to nothing took about 30 seconds for a collection of 3million+ elements suggesting it does something more than just zeroising the pointer.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at [url=http://www.vbaexpress.

 

[fumei]

Ah, thanks Tony! Something to add to the collection (pun intended) of knowledge.

Gerry

 

[VBAjedi]

Didn't know you could put 3 million items in a collection! Which brings up a related question - how many objects CAN you put in a collection? Is it limited only by system resources, or is there a built-in "cap" in Excel?

VBAjedi

 

[TonyJollans]

AFAIK it's limited only by resources available.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at [url=http://www.vbaexpress.