Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Clearing a Collection
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? -- Thanks! Max |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Clearing a Collection
One way:
First, you should NEVER "Dim as New". See the "Don't Use The New Keyword In A Dim Statement" section he 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... In article , Max wrote: 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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Clearing a Collection
What kind of collection is it? You may want to add a little more detail to
get an accurate answer. -- If this posting was helpful, please click on the Yes button. Regards, Michael Arch. "Max" wrote: 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? -- Thanks! Max |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Clearing a Collection
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? -- Thanks! Max |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Clearing a Collection
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). "Max" wrote: 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? -- Thanks! Max |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Clearing a Collection
I think this worked! Thanks for the help!
-- Thanks! Max "Jim Cone" wrote: 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? -- Thanks! Max |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Clearing cells without clearing formulas | Excel Discussion (Misc queries) | |||
Clearing RAM | Excel Programming | |||
Clearing #N/A's in one go? | Excel Worksheet Functions | |||
Clearing all items in a collection | Excel Programming |