How to delete a value from a collection without us

问题:

I have a collection object in vba where I am adding a bunch of ID values

Dim newCollection As New Collection 
newCollection.ADD Me.ID

Is there a way for me to delete an ID from the collection without knowing what key it is stored as?

I tried to do:

newCollection.Remove """" & Me.ID & """" 

But I get:

Invalid Procedure Call or Argument

回答1:

You need (want) to ALSO add the "key" to lookup the value (it must be a string).

The following code shows how this works:

Private Sub Command103_Click()

     Dim cValues    As New Collection

     cValues.Add 5, "5"
     cValues.Add 100, "100"
     cValues.Add 6, "6"

     cValues.Add Me.ID.Value, CStr(Me.ID.Value)


     cValues.Add 200, "200"

     GoSub displayList

    ' delete the 2 value based on index
    cValues.Remove (2)
    GoSub displayList

    ' remove a value by key
    cValues.Remove (CStr(Me.ID))

    GoSub displayList

    ' remove the 6 guy by KEY
     cValues.Remove ("6")

    GoSub displayList

    Exit Sub

displayList:

     Dim i       As Integer

     For i = 1 To cValues.Count
        Debug.Print i, "--->", cValues(i)
     Next i

     Return


End Sub

Output:

1            --->           5 
2            --->           100 
3            --->           6 
4            --->           15 
5            --->           200 


1            --->           5 
2            --->           6 
3            --->           15 
4            --->           200 

1            --->           5 
2            --->           6 
3            --->           200 


1            --->           5 
2            --->           200 

回答2:

In this case you are trying to remove the contents of Me.ID as a string. What you need to do is identify what the position of your ID is and then remove it. You can do this as follows:

Dim newCollection As New Collection 
newCollection.ADD Me.ID

For I = 1 To newCollection.Count
    If newCollection.Item(I) = Me.ID Then
        newCollection.Remove I
        Exit For
    End If
Next I