VBA Determining if a Key or Item Exists in a Collection
Keys
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 FunctionItems
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
Next
End Function