CODEWITHSUNDEEP
vbacollections
Alex Gordon
Alex Gordon

Reputation: 60711

vba: how do i search through a collection?

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

Upvotes: 4

Views: 57934

Answers (5)

schmijos
schmijos

Reputation: 8695

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

Upvotes: 6

Ankur G
Ankur G

Reputation: 1

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

Upvotes: 0

gembird
gembird

Reputation: 14053

@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

Upvotes: 2

shahkalpesh
shahkalpesh

Reputation: 33476

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

Upvotes: 8

GSerg
GSerg

Reputation: 78155

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.

Upvotes: 7

Related Questions