Generic way to check if a key is in a Collection in Excel VBA

Question

I have different Collections in my code. Some hold Objects (of various kinds), others have types (like Long) within them.

Is there a way to check if a key is contained in the Collection that works for types as well as objects?

So far I have two functions.

First function:

Private Function ContainsObject(objCollection As Object, strName As String) As Boolean
    Dim o As Object
    On Error Resume Next
    Set o = objCollection(strName)
    ContainsObject = (Err.Number = 0)
    Err.Clear
End Function

Second function:

Private Function ContainsLong(AllItems As Collection, TheKey As String) As Boolean
    Dim TheValue As Long
    On Error Resume Next
    TheValue = AllItems.Item(TheKey)
    ContainsLong = (Err.Number = 0)
    Err.Clear
End Function

The reason for the two functions is that ContainsObject does not seem to work if I pass a Collection that has Longs pairs (the function always returns False.)

P.S.: The first function is a copy of the third answer from Test or check if sheet exists

Answer 1

You should use a Variant in the first function. You can assign an Object to a Variant, e.g. this won't error:

Sub Test()
    Dim var As Variant
    Dim obj As Object
    Set obj = Application
    var = Application
    Debug.Print var
End Sub

But this will give a Type Mismatch compile error i.e. trying to assign a Long to an Object:

Sub Test()
    Dim obj As Object
    Dim lng As Long
    lng = 3
    Set obj = lng
End Sub

So, for a generic function (along the lines of your code) to check if a Collection key is valid, you can use:

Function HasKey(coll As Collection, strKey As String) As Boolean
    Dim var As Variant
    On Error Resume Next
    var = coll(strKey)
    HasKey = (Err.Number = 0)
    Err.Clear
End Function

Test code:

Sub Test()
    Dim coll1 As New Collection
    coll1.Add Item:=Sheet1.Range("A1"), Key:="1"
    coll1.Add Item:=Sheet1.Range("A2"), Key:="2"
    Debug.Print HasKey(coll1, "1")

    Dim coll2 As New Collection
    coll2.Add Item:=1, Key:="1"
    coll2.Add Item:=2, Key:="2"
    Debug.Print HasKey(coll2, "1")
End Sub

There is a useful article on MSDN regarding this. The context is VB6 but relates to VBA.