myCollection.Remove problem [Resolved]

[Kira Roark]

This won't work:

Public Sub ClearCollection(myCollection As Collection)
 
    For i = 1 To myCollection.Count
    
        myCollection.Remove (i)
        Debug.Print i & " - " & myCollection(i).sFileName
 
    Next
    
End Sub

I'm getting a "subscript out of range" error


It is really strange. If i don't commment out the remove command and just print out the values, then it works fine. Does removing the items somehow mess up the index?

I'm really just trying to clear all the values out of the collection.

thanks

[Merri]

For i = myCollection.Count To 1 Step -1

Reason: you are trying to remove an item of too big index: when you remove the first item, the second item becomes first, third the second and so on.

[Kira Roark]

Of Course! Thanks....I feel like a moron....but at least I can move off this stupid problem.

[Merri]

To prevent this mistake, you can use a code like this:

Do While myCollection.Count
    myCollection.Remove 1
Loop

Simpler and you can't fail with it

[Kira Roark]

So, of course both methods work.

Which is "sounder" or would be a better technique.

thanks

[Merri]

There is no better way, and I'm not 100% sure about the performance. It might be faster to remove items starting from the end, or then not. I only suggested the code before because of the simplicity of it, it doesn't require declaring any variables with Dim and you can see what it does with one eye. But I don't think there is any real difference, use what you like to use

[Kira Roark]

I'll use the do loop. thanks again.

[frigginjerk]

wouldn't this be the best/fastest/easiest way:

set MyCollection = New Collection

[Kira Roark]

Well, I'm declaring the collection as a Public in a module and then basically "resetting it" or reloading the data in it a various points in the prog....so I wouldn't think that your method would work.

[Merri]

Actually it can work, but I've never done it like that. Most often I haven't had the need to erase a whole collection or I've added new items the within the removal loop and the code has run until the items run out. I'll do a little test on one thing that worries me with doing Set...

Edit Yup, it seems to work ok memory wise as well (doesn't leave unused stuff in memory). So feel free to use it as well