Skip to content

Instantly share code, notes, and snippets.

@danwagnerco
Last active July 1, 2016 10:51
Show Gist options
  • Save danwagnerco/5dc72c8e46adf0e761bdb899e39d39f6 to your computer and use it in GitHub Desktop.
Save danwagnerco/5dc72c8e46adf0e761bdb899e39d39f6 to your computer and use it in GitHub Desktop.
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
CheckForObject:
If IsObject(Kollection(strKey)) Then
Contains = True
On Error GoTo 0
Exit Function
End If
NotFound:
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
Else
Contains = False
End If
End Function
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment