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

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


Isn't it good enough?

Public Function Contains(col As Collection, key As Variant) As BooleanDim obj As VariantOn Error GoTo err    Contains = True    obj = col(key)    Exit Functionerr:    Contains = FalseEnd Function


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 IfEnd Function


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.