Determining whether an object is a member of a collection in VBA

Question

How do I determine whether an object is a member of a collection in VBA? Specifically, I need to find out whether a table definition is a member of the TableDefs collection.

Answer 1

Your best bet is to iterate over the members of the collection and see if any match what you are looking for. Trust me I have had to do this many times.

The second solution (which is much worse) is to catch the "Item not in collection" error and then set a flag to say the item does not exist.

Answer 2

Can you iterate over the TableDefs collection using For..Each and check some property like Name to find a match?

Answer 3

In your specific case (TableDefs) iterating over the collection and checking the Name is a good approach. This is OK because the key for the collection (Name) is a property of the class in the collection.

But in the general case of VBA collections, the key will not necessarily be part of the object in the collection (e.g. you could be using a Collection as a dictionary, with a key that has nothing to do with the object in the collection). In this case, you have no choice but to try accessing the item and catching the error.

Answer 4

Turning the question around: if the object is of type TableDef then it should by definition be in the TableDefs collection, no? Do you want to test whether your TableDef's Parent is the same as a given TableDefs collection's Parent?

Answer 5

Not exactly elegant, but the best (and quickest) solution i could find was using OnError. This will be significantly faster than iteration for any medium to large collection.

Public Function InCollection(col As Collection, key As String) As Boolean
  Dim var As Variant
  Dim errNumber As Long

  InCollection = False
  Set var = Nothing

  Err.Clear
  On Error Resume Next
    var = col.Item(key)
    errNumber = CLng(Err.Number)
  On Error GoTo 0

  '5 is not in, 0 and 438 represent incollection
  If errNumber = 5 Then ' it is 5 if not in collection
    InCollection = False
  Else
    InCollection = True
  End If

End Function

Answer 6

Hi there! Isn't it good enough?

Public Function Contains(col As Collection, key As Variant) As Boolean
Dim obj As Variant
On Error GoTo err
    Contains = True
    obj = col(key)
    Exit Function
err:

    Contains = False
End Function

Regards.

Answer 7

You can use a simple LINQ query. Here's some code with an example test.

Module Module1

Public Function find(ByVal col As Collection, ByVal item As Object) As Object
    Dim query = From x In col Where x = item Select x
    If query.Count >= 1 Then
        Return query.first()
    Else
        Return Nothing
    End If
End Function

Public Sub check(ByVal col As Collection, ByVal item As Object)
    If find(col, item) Is Nothing Then
        Console.WriteLine(item.ToString & " not found")
    Else
        Console.WriteLine(item.ToString & " found")
    End If
End Sub

Public Sub test1()
    Dim collection As New Collection
    collection.Add("apple")
    collection.Add("orange")
    collection.Add("banana")
    collection.Add("mango")

    check(collection, "mango")
    check(collection, "pineapple")
    check(collection, "orange")
    check(collection, "coconut")

End Sub

Sub Main()
    test1()
    Console.ReadLine()
End Sub

End Module

Answer 8

The output from the above example is, as expected:

mango found pineapple not found orange found coconut not found