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?

This is a duplicate question; see: http://stackoverflow.com/questions/137845/determining-whether-an-object-is-a-member-of-a-collection-in-vba — Todd Schiller, Aug 27 '10 at 18:59

score 8 · Answer 1 · answered Aug 27 '10 at 18:58

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

score 6 · Accepted Answer · answered Aug 27 '10 at 18:47

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.

score 5 · Answer 3 · edited May 11 '18 at 03:25

5

I use a simple tool function which iterates through a collection. It's without directly accessing indexes and it uses VBA language features like they should be used (Comparison of variants and each-Loop).

Public Function ExistsIn(item As Variant, lots As Collection) As Boolean
    Dim e As Variant
    ExistsIn = False
    For Each e In lots
        If item = e Then
            ExistsIn = True
            Exit For
        End If
    Next
End Function

edited May 11 '18 at 03:25

Marcucciboy2

3,053
3
15
33

answered Oct 09 '13 at 14:21

schmijos

6,629
3
43
50

1

probably because the question was phrased *search through*, like iterate, which you answered as asked. however strings are hashed native lookup, no loop needed. yours is not invalid, and is a way without having to hack the error condition, and I don't think should have been voted down and certainly not into negative. There's the use error and native, use index loop, and yours of use built in iterator. – Celess Nov 05 '14 at 22:12

Daniel Dušek · Answer 4 · 2014-01-15T07:40:15.443

@Josua Schmid:

I think the code in your answer could be correct but could be not correct as well. Your function has paremeter of type Variant and it is then compared to each menber of the collection. But what is compared actually? In this case the default member is compared. So firts problem could arise if the collection will contain members of some custom class which does not have default member specified. In such case runtime error 438 object doesn't support this property or method will be raised. Well you could add default member but even then it will work in a way you maybe not like I am afraid.

Example with ranges (for Range-Class Value is the default member so Values will be compared). Maybe it is exactly what you wanted but maybe not. So from my point of view better is to use 'Key' for each Item added to collection and then try to get this Item by its Key.

Debug.Print col.item(r1.Address) ' A1 Value

Or by index if no keys were used:

Debug.Print col.item(1) ' A1 Value

Sub test()
    Dim col As New VBA.Collection

    Dim r1 As Range
    Dim r2 As Range
    Dim r3 As Range

    Set r1 = Range("a1")
    Set r2 = Range("b1")
    Set r3 = Range("c1")

    r1 = "A1 Value"
    r2 = "B1 Value"
    r3 = "C1 Value"

    col.Add r1, r1.Address
    col.Add r2, r2.Address
    col.Add r3, r3.Address

    Debug.Print ExistsIn(r1, col)
    Debug.Print ExistsIn(r2, col)
    Debug.Print ExistsIn(r3, col)

    Dim r4 As Range
    Set r4 = Range("d1")
    r4 = "A1 Value"

    Debug.Print ExistsIn(r4, col)
End Sub

Output:

True
True
True
True

score 0 · Answer 5 · answered Apr 18 '18 at 08:42

It is possible to determine the collection item with a variant. In the example below, I am determining if an object with a specific index exists in the FXItems collection or not. If it doesnt exist, system will create it otherwise, do some other action

            On Error Resume Next
            Dim myFXItem as FXItem
            Set myFXItem = FXItems.item("USDEUR")
            On Error GoTo 0

            If myFXItem Is Nothing Then
                Set myFXItem = New FXItem
                myFXItem.sCurr = "USDEUR"
                FXItems.Add item:=myFXItem, Key:="USDEUR"
            Else
                myFXItem.dRate = 0.834
                myFXItem.dtValueDate = #12-03-2018#
            End If
            Set myFXItem = Nothing

vba: how do i search through a collection?

5 Answers5

Linked