How do i clear a collection in vba

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?
JoseJalapenoAsked:
Who is Participating?
 
Wayne Taylor (webtubbs)Connect With a Mentor Commented:
I don't mean to be rude, but I answered your question, "How do i clear a collection in vba" in my first comment.
0
 
Wayne Taylor (webtubbs)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

0
 
JoseJalapenoAuthor 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
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
Wayne Taylor (webtubbs)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
0
 
ExcelGuideConsultantCommented:
sorry for the offtopic, but webtubbs I love that mask!!! :)
0
 
Wayne Taylor (webtubbs)Commented:
Yeah, the folks in the office are into the April Fool's spirit :)
0
 
JoseJalapenoAuthor 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?
0
 
Wayne Taylor (webtubbs)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.
0
 
JoseJalapenoAuthor 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?
0
 
Rory ArchibaldCommented:
Userforms are a little bit odd. You have to actually close them, either manually or in code.
0
 
Rory ArchibaldCommented:
This is basically because they are added to the Userforms collection which maintains a reference to them until they are unloaded.
0
 
Rory ArchibaldCommented:
I agree. Can you reopen it, or do we need a VB ZAPE?
0
 
JoseJalapenoAuthor 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.
0
 
JoseJalapenoAuthor 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?
0
 
JoseJalapenoAuthor Commented:
Also can you tell me where the help is for assigning points as i must have missed it.
0
 
JoseJalapenoAuthor Commented:
Apologies webtubbs, genuine mistake.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.