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.