Questions And Answers

More Tutorials

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


In this page (written and validated by ) you learned about VBA Determining if a Key or Item Exists in a Collection . What's Next? If you are interested in completing VBA tutorial, your next topic will be learning about: VBA Operators.

Incorrect info or code snippet? We take very seriously the accuracy of the information provided on our website. We also make sure to test all snippets and examples provided for each section. If you find any incorrect information, please send us an email about the issue:

Share On:

Mockstacks was launched to help beginners learn programming languages; the site is optimized with no Ads as, Ads might slow down the performance. We also don't track any personal information; we also don't collect any kind of data unless the user provided us a corrected information. Almost all examples have been tested. Tutorials, references, and examples are constantly reviewed to avoid errors, but we cannot warrant full correctness of all content. By using, you agree to have read and accepted our terms of use, cookies and privacy policy.