We help IT Professionals succeed at work.

How do i clear a collection in vba

JoseJalapeno
JoseJalapeno asked
on
5,919 Views
Last Modified: 2012-05-06
In Visual Basic there seems to be a collection.clear() command but not in Visual Basic for Applications.
Anyone know how to do it in vba?
Comment
Watch Question

CERTIFIED EXPERT

Commented:
You'll need to use the Remove method to remove each item in the collection. The below function shows an example.

Wayne
Public Function ClearCollection(ByRef coll As Collection)
     Do
          coll.Remove (1)
     Loop While coll.Count > 0
End Function

Open in new window

Author

Commented:
Thanks for that.
One more question though:
If all you do is empty the items surely the collection still exists. If you set a new collectio you end up with 2
CERTIFIED EXPERT

Commented:
>>If all you do is empty the items surely the collection still exists.

Yeah, sure, but you didn't want to dispose of the collection - simply clear the items. To dispose of a collection, use this....

    Set coll = Nothing

>>If you set a new collectio you end up with 2

Do you mean if you do this to an already instantiated collection?

    Set coll = New Collection

If so, then no. The old collection is disposed of.

Wayne
ExcelGuideConsultant

Commented:
sorry for the offtopic, but webtubbs I love that mask!!! :)
CERTIFIED EXPERT

Commented:
Yeah, the folks in the office are into the April Fool's spirit :)

Author

Commented:
OK sorry to appear to have a single brain cell but...
...i understood that = New Collection meant a new instance of the collection, same as a new instance of an object, am i wrong?
CERTIFIED EXPERT

Commented:
You are not wrong. Using "Set coll = New Collection" sets the collection *variable* to a new collection. The previous instance of the collection is no longer "connected" to a variable and is released from memory.

Author

Commented:
How come then if you open multiple instances of a form you use frm = new Form_X which produces a new copy of the form. The previous one does not dissapear.
Is it a different set of rules for collections?
Rory ArchibaldGrand Poobah
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011

Commented:
Userforms are a little bit odd. You have to actually close them, either manually or in code.
Rory ArchibaldGrand Poobah
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011

Commented:
This is basically because they are added to the Userforms collection which maintains a reference to them until they are unloaded.
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Rory ArchibaldGrand Poobah
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011

Commented:
I agree. Can you reopen it, or do we need a VB ZAPE?

Author

Commented:
Sorry but when i clicked on "Accept as Solution" against webtubbs it did not offer me the opportunity to assign any points.
If i am doing something wrong please educate me.

Author

Commented:
I didn't want to assign points to more than one person.
I accepted webtubbs solution, or at least i thought i had.
How then do points get awarded to one person?

Author

Commented:
Also can you tell me where the help is for assigning points as i must have missed it.

Author

Commented:
Apologies webtubbs, genuine mistake.
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.