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.

Answer 2

Few typos as per comments have already been corrected during edit of your post. In response to your question I would like to cover related aspects.
While Using keys in collections has mainly three advantages
- If the order changes your code will still access the correct item - You can directly access the item without reading through the entire collection
- It can make you code more readable.

*But at the same time there are mainly three issues with using keys in collections

• You cannot check if the key exists

• You cannot change the key

• You cannot retrieve the key

As per Pearsons article the Keys of a Collection are write-only -- there is no way to get a list of existing Keys of a Collection. Further going through quoted paragraph:-

Here, Coll is a Collection object in which we will store multiple CFile objects. The CollKeys Collection is used to store the keys of the CFile objects stored in the Coll Collection. We need this second Collection because the Keys of a Collection are write-only -- there is no way to get a list of existing Keys of a Collection. One of the enhancements provided by CFiles is the ability to retrieve a list of Keys for the Collection.

Custom Collection Classes

One way is to iterate over the members of the collection and see if there is match for what you are looking for and the other way is to catch the Item not in collection error and then set a flag to say the item does not exist. Opinions differ on these approaches whereas some people feel it is not a good method to catch error while other section feels that it will be significantly faster than iteration for any medium to large collection.
So if we go for a method to catch error then error number we get depends on exactly what caused the error. We need a code routine to check the error. In a simplest way it could be.

'c1 is the collection
 For i = 1 To c1.Count
     Debug.Print Err.Number, Err.Description
     If Err.Number <> 0 Then Err.Clear
 Next i

Error catching routines proposed by various professionals differ in the error number they consider important and include in their routine.Various commonly occurring error numbers associated with collection object are:-

• Error 5 Invalid procedure call or argument.This error can also occur if an attempt is made to call a procedure that isn't valid on the current platform. For example, some procedures may only be valid for Microsoft Windows, or for the Macintosh, and so on.
• error 438 "object doesn't support this property or method An object is a class instance. A class instance supports some properties defined in that class type definition and does not support this one.
• Error 457 This key is already associated with an element of this collection.You specified a key for a collection member that already identifies another member of the collection. Choose a different key for this member.
• Error 91 Object variable or With block variable not set.There are two steps to creating an object variable. First you must declare the object variable. Then you must assign a valid reference to the object variable using the Set statement. You attempted to use an object variable that isn't yet referencing a valid object.
• Error 450 Wrong number of arguments or invalid property assignment.The number of arguments in the call to the procedure wasn't the same as the number of required arguments expected by the procedure.If you tried to assign a value to a read-only property,

Among the above errors error number 438 has been considered important and the other one is 5. I am incorporating a Function routine in my sample testing program which was posted by Mark Nold 7 years back in 2008 vide SO question Determining whether an object is a member of a collection in VBA with due credit to him.

Some errors like error 457 won't be allowed at the time of program test run. I tried to populated with duplicate keys data, it gave the error at the time of program testing itself as shown in the snapshot.

After removing it is showing correct output as shown in the snap shot.

It may not be possible to get the list of keys of a collection with a vanilla collection without storing the key values in an independent array. The easiest alternative to do this is to add a reference to the Microsoft Scripting Runtime & use a more capable Dictionary instead. I have included this approach to get the list of keys in my program.
While populating Collection it is to be ensured that the key is the second parameter and must be a unique string.

Full code of my program is.

Sub Generic_key_check()
    Dim arr As Variant
    Dim c1 As New Collection
    Dim dic As Object
    With Application
    .ScreenUpdating = False
    End With


    Set dic = CreateObject("Scripting.Dictionary")
    dic.CompareMode = vbTextCompare

    'Populate the collection
    c1.Add "sheet1", "sheet1"
    c1.Add "sheet2", "sheet2"
    c1.Add "sheet3", "sheet3"
    c1.Add "sheet4", "sheet4"
    c1.Add "sheet5", "sheet5"
    c1.Add 2014001, "Long1"
    c1.Add 2015001, "Long2"
    c1.Add 2016001, "Long3"
    c1.Add 2015002, "Long4"
    c1.Add 2016002, "Long5"

    'Populate the dictionary
    dic.Add "sheet1", "sheet1"
    dic.Add "sheet2", "sheet2"
    dic.Add "sheet3", "sheet3"
    dic.Add "sheet4", "sheet4"
    dic.Add "sheet5", "sheet5"
    dic.Add "Long1", 2014001
    dic.Add "Long2", 2015001
    dic.Add "Long3", 2016001
    dic.Add "Long4", 2015002
    dic.Add "Long5", 2016002
    ' Get a list of key items by Dictionary Method
    Dim N As Variant
    For Each N In dic.Keys
    Debug.Print "Key: " & N, "Value: " & dic.item(N)
    Next
    'Test for two types of data whether key exists or not.
    If InCollection(c1, "Long1") Then
    'If Exists("Long1", c1) Then
    Debug.Print "Good"

    Else
    ' If there is error then print out the error number and its description.
    Debug.Print Err.Number, Err.Description
    Debug.Print "Not Good"
    End If
    If InCollection(c1, "sheet2") Then
    Debug.Print "Good"

    Else
    Debug.Print Err.Number, Err.Description
    Debug.Print "Not Good"
    End If

    'Checking whether desired key has populated correctly
    Debug.Print c1("Sheet1")
    Debug.Print c1("Long3")



    'Listing out collection items to check theyexist in the collection.
    For i = 1 To c1.Count
    Debug.Print c1.item(i)
    Next i
    With Application
    .ScreenUpdating = True
    End With
    Set c1 = Nothing
End Sub
Public Function InCollection(col As Collection, key As String) As Boolean
    Dim var As Variant
    Dim errNumber As Long

    InCollection = False
    Set var = Nothing

    Err.Clear
    On Error Resume Next
    var = col.item(key)
    errNumber = CLng(Err.Number)
    On Error GoTo 0

    '5 is not in, 0 and 438 represent incollection
    If errNumber = 5 Then ' it is 5 if not in collection
    InCollection = False
    Else
    InCollection = True
    End If

End Function

Final output as per program as shown in the Immediate window has been shown in the Snapshot.