Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.1k views
in Technique[技术] by (71.8m points)

vba - Remove currently looped item from collection?

How do I remove the currently looped item from a collection? I get run-time error 13: Type mismatch on the line wls.Remove vl

Sub FocusOnH(ByRef vls As Collection)
    Dim vl As CVegetableLine
    For Each vl In vls
        If vl.hValue <> 0 Then
            vl.volume = vl.hValue
        Else
            vls.Remove vl
        End If
    Next vl
End Sub
See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

You must delete item while looping through the collection in backward order, otherwise it will cause error.

Sub TestRemoveItemInCollection()
    Dim col As Collection, i As Integer
    Set col = New Collection
    col.Add "item1"
    col.Add "item2"
    col.Add "item3"
    col.Add "item4"

    ' Never use: For i=1 to col.Count
    For i = col.Count To 1 Step -1
        col.Remove i
    Next i

    Set col = Nothing
End Sub

Why? Because Visual Basic collections are re-indexed automatically. If you try to delete in forward order, it will conflict with the outer loop and hence get the tricky error.

Another example, to remove all items in the collection can be done like this:

For i = 1 to col.Count
    col.Remove 1 'Always remove the first item.
Next i

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...