Generic way to check if a key is in a Collection in Excel VBA
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.
I want to point out that if you want to make PaulE's function a little more flexible, you can change the string parameter to a Variant, which means that you can now also use it to check either for an item key or for an item number, which is handy. Variants are a little slower if you're going to be checking a lot of collections, but for most purposes the two functions will act similarly.
Function HasItem(col As Collection, ItemKeyOrNum As Variant) As Boolean
Dim v As Variant
On Error Resume Next
v = IsObject(col.Item(ItemKeyOrNum))
HasItem = Not IsEmpty(v)
End Function
The accepted answer here is wrong (which is the case for quite a few other questions I have noticed as well, so watch out, and read all the answers). Apostle and PaulE collaborated there for the most correct answer to the specific question that was asked. I tried to use the accepted answer, but it didn't work.
The question clearly states, "Is there a way to check if a key is contained in the Collection that works for types as well as objects?"
The accepted answer DOES NOT work for objects. PaulE's answer is the final, and correct answer. I am just adding a little bit of nuance here to make the function more one-size-fits-all.
Apostle is almost correct with their answer. Robin's answer will not work with generic objects, but will work as written because Excel's Range object will return the cell's value. I love Apostle's use of IsObject (mostly because ths is what I had figured out as well). The code is a little over-complicated though.
If the key exists in the collection IsObject will set the variant to True or False, otherwise an error will be ignored leaving the variant empty.
Function HasKey(col As Collection, Key As String) As Boolean
Dim v As Variant
On Error Resume Next
v = IsObject(col.Item(Key))
HasKey = Not IsEmpty(v)
End Function
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.