vba: how do i search through a collection?

Question

i am looking for a particular element in a collection. how do i know if it exists in the collection?

Answer 1

If you used a key when you added the item to the collection, see if referring to this key gives an error:

on error goto no_item
col.Item "key"
msgbox "Item exists"

exit sub

no_item:    
msgbox "Item does not exist"

Otherwise you have to loop through all items to see if there's the one you need.

Answer 2

Collection are index based. Hence, you will have to loop through the collection to search for an item.

Sub test()
Dim iCtr As Integer
Dim itemCount As Integer

Dim myData As Collection
Set myData = New Collection

Dim searchFor As String

myData.Add "MS", "11"
myData.Add "Oracle", "22"
myData.Add "Google", "33"

'** Searching based on value
searchFor = "Google"

itemCount = myData.Count
For iCtr = 1 To itemCount
    If myData(iCtr) = searchFor Then
        MsgBox myData(iCtr)
        Exit For
    End If
Next

'** Searching by key
MsgBox myData.Item("22")
End Sub

Answer 3

This is a duplicate question; see: http://stackoverflow.com/questions/137845/determining-whether-an-object-is-a-member-of-a-collection-in-vba