VBA Determining if a Key or Item Exists in a Collection



Unlike a Scripting.Dictionary, a Collection does not have a method for determining if a given key exists or a way to retrieve keys that are present in the Collection. The only method to determine if a key is present is to use the error handler:

Public Function KeyExistsInCollection(ByVal key As String, _
                                      ByRef container As Collection) As Boolean
    With Err
        If container Is Nothing Then .Raise 91
        On Error Resume Next
        Dim temp As Variant
        temp = container.Item(key)
        On Error GoTo 0

        If .Number = 0 Then
            KeyExistsInCollection = True 
        ElseIf .Number <> 5 Then
            .Raise .Number
        End If
    End With
End Function


The only way to determine if an item is contained in a Collection is to iterate over the Collection until the item is located. Note that because a Collection can contain either primitives or objects, some extra handling is needed to avoid run-time errors during the comparisons:

Public Function ItemExistsInCollection(ByRef target As Variant, _
                                       ByRef container As Collection) As Boolean
    Dim candidate As Variant
    Dim found As Boolean
    For Each candidate In container
        Select Case True
            Case IsObject(candidate) And IsObject(target)
                found = candidate Is target
            Case IsObject(candidate), IsObject(target)
                found = False
            Case Else
                found = (candidate = target)
        End Select
        If found Then
            ItemExistsInCollection = True
            Exit Function
        End If
End Function