Skip to content

Instantly share code, notes, and snippets.

Last active July 1, 2016 10:51
What would you like to do?
A bulletproof VBA function that examines a Collection for a Key or Item
'INPUT : Kollection, the collection we would like to examine
' : (Optional) Key, the Key we want to find in the collection
' : (Optional) Item, the Item we want to find in the collection
'OUTPUT : True if Key or Item is found, False if not
'SPECIAL CASE: If both Key and Item are missing, return False
Option Explicit
Public Function Contains(Kollection As Collection, Optional Key As Variant, Optional Item As Variant) As Boolean
Dim strKey As String
Dim var As Variant
'First, investigate assuming a Key was provided
If Not IsMissing(Key) Then
strKey = CStr(Key)
'Handling errors is the strategy here
On Error Resume Next
Contains = True
var = Kollection(strKey) '<~ this is where our (potential) error will occur
If Err.Number = 91 Then GoTo CheckForObject
If Err.Number = 5 Then GoTo NotFound
On Error GoTo 0
Exit Function
If IsObject(Kollection(strKey)) Then
Contains = True
On Error GoTo 0
Exit Function
End If
Contains = False
On Error GoTo 0
Exit Function
'If the Item was provided but the Key was not, then...
ElseIf Not IsMissing(Item) Then
Contains = False '<~ assume that we will not find the item
'We have to loop through the collection and check each item against the passed-in Item
For Each var In Kollection
If var = Item Then
Contains = True
Exit Function
End If
Next var
'Otherwise, no Key OR Item was provided, so we default to False
Contains = False
End If
End Function
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment