Solved

How do i clear a collection in vba

Posted on 2009-04-01
19
5,749 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?
0
Comment
Question by:JoseJalapeno
[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
  • 7
  • 5
  • 3
  • +1
19 Comments
 
LVL 47

Expert Comment

by:Wayne Taylor (webtubbs)
ID: 24036698
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
 

Author Comment

by:JoseJalapeno
ID: 24036735
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
 
LVL 47

Expert Comment

by:Wayne Taylor (webtubbs)
ID: 24036751
>>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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 17

Expert Comment

by:ExcelGuide
ID: 24036754
sorry for the offtopic, but webtubbs I love that mask!!! :)
0
 
LVL 47

Expert Comment

by:Wayne Taylor (webtubbs)
ID: 24036760
Yeah, the folks in the office are into the April Fool's spirit :)
0
 

Author Comment

by:JoseJalapeno
ID: 24036785
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
 
LVL 47

Expert Comment

by:Wayne Taylor (webtubbs)
ID: 24036804
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
 

Author Comment

by:JoseJalapeno
ID: 24036885
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
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 24037088
Userforms are a little bit odd. You have to actually close them, either manually or in code.
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 24037109
This is basically because they are added to the Userforms collection which maintains a reference to them until they are unloaded.
0
 
LVL 47

Accepted Solution

by:
Wayne Taylor (webtubbs) earned 500 total points
ID: 24037486
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
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 24037543
I agree. Can you reopen it, or do we need a VB ZAPE?
0
 

Author Comment

by:JoseJalapeno
ID: 24037743
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
 

Author Comment

by:JoseJalapeno
ID: 24038208
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
 

Author Comment

by:JoseJalapeno
ID: 24038222
Also can you tell me where the help is for assigning points as i must have missed it.
0
 

Author Closing Comment

by:JoseJalapeno
ID: 31565206
Apologies webtubbs, genuine mistake.
0

Featured Post

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

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

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

732 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