检查密钥是否在 Excel VBA 中的集合中的通用方法

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

提问by user2606240

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

我的代码中有不同的集合。一些持有对象（各种），其他人在其中拥有类型（如 Long）。

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.)

这两个函数的原因是，如果我传递一个包含 Longs 对的集合，ContainsObject 似乎不起作用（该函数总是返回 False。）

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

PS：第一个功能是测试第三个答案的副本或检查表是否存在

回答by Robin Mackenzie

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

您应该Variant在第一个函数中使用 a 。您可以将 an 分配Object给 a Variant，例如这不会出错：

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 Mismatchcompile error i.e. trying to assign a Longto an Object:

但这会Type Mismatch导致编译错误，即尝试将 a 分配Long给 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 Collectionkey is valid, you can use:

因此，对于用于检查Collection密钥是否有效的通用函数（沿着代码行），您可以使用：

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 MSDNregarding this. The context is VB6 but relates to VBA.

MSDN上有一篇关于此的有用文章。上下文是 VB6，但与 VBA 相关。

回答by skkakkar

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

*但同时在集合中使用key主要存在三个问题

• 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:-

根据 Pearsons 的文章，集合的键是只写的——无法获取集合的现有键的列表。进一步阅读引用的段落：-

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.

这里，Col 是一个 Collection 对象，我们将在其中存储多个 CFile 对象。CollKeys 集合用于存储存储在 Coll 集合中的 CFile 对象的键。我们需要第二个集合，因为集合的键是只写的——无法获得集合的现有键的列表。CFiles 提供的增强功能之一是能够检索集合的键列表。

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 collectionerror 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.

一种方法是迭代集合的成员，看看是否有你正在寻找的匹配项，另一种方法是捕捉Item not in collection错误，然后设置一个标志来说明该项目不存在。对这些方法的意见不同，有些人认为这不是捕获错误的好方法，而其他部分则认为对于任何中到大型集合，它都会比迭代快得多。
因此，如果我们使用一种方法来捕获错误，那么我们得到的错误编号取决于导致错误的确切原因。我们需要一个代码例程来检查错误。以最简单的方式，它可能是。

'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 5Invalid 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 457This 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 91Object 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 450Wrong 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,

• Error 5过程调用或参数无效。如果尝试调用在当前平台上无效的过程，也会发生此错误。例如，某些过程可能仅对 Microsoft Windows 或 Macintosh 等有效。
• error 438"对象不支持此属性或方法对象是类实例。类实例支持该类类型定义中定义的某些属性，但不支持此属性。
• Error 457此键已与此集合的元素相关联。您为已标识该集合的另一个成员的集合成员指定了一个键。为该成员选择不同的密钥。
• Error 91对象变量或未设置块变量。创建对象变量有两个步骤。首先，您必须声明对象变量。然后您必须使用 Set 语句为对象变量分配一个有效的引用。您试图使用尚未引用有效对象的对象变量。
• Error 450参数数量错误或属性分配无效。调用过程中的参数数量与过程预期的所需参数数量不同。如果您尝试为只读属性赋值，

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 VBAwith due credit to him.

在上述错误中，错误号 438 被认为是重要的，另一个是 5。我在我的示例测试程序中加入了一个函数例程，该程序由 Mark Nold 7 年前在 2008 年发布，视频 SO question Determining a object is a member VBA 中的一个集合，应归功于他。

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.

程序试运行时不允许出现错误 457 等错误。我试图填充重复的键数据，它在程序测试时给出了错误，如快照所示。

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.

如果不将键值存储在独立数组中，则可能无法使用普通集合获取集合的键列表。最简单的替代方法是添加对 Microsoft Scripting Runtime 的引用并改用功能更强大的字典。我已经包含了这种方法来获取我的程序中的键列表。
在填充 Collection 时，要确保键是第二个参数，并且必须是唯一的字符串。

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.

即时窗口中显示的每个程序的最终输出已显示在快照中。

回答by PaulE

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.

使徒的回答几乎是正确的。Robin 的答案不适用于通用对象，但会按书面方式工作，因为 Excel 的 Range 对象将返回单元格的值。我喜欢 Apostle 对 IsObject 的使用（主要是因为这也是我想出来的）。不过代码有点过于复杂了。

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.

如果集合中存在键，IsObject 会将变体设置为 True 或 False，否则将忽略错误并将变体留空。

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

回答by sdanse

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.

我想指出的是，如果你想让 PaulE 的函数更灵活一点，你可以将 string 参数更改为 Variant，这意味着你现在也可以使用它来检查项目键或项目编号，这很方便。如果您要检查大量集合，变体会稍微慢一点，但在大多数情况下，这两个函数的作用相似。

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.

此处接受的答案是错误的（我也注意到了很多其他问题，因此请注意并阅读所有答案）。Apostle 和 PaulE 在那里合作，为所提出的特定问题提供最正确的答案。我尝试使用接受的答案，但没有用。

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.

接受的答案不适用于对象。PaulE 的答案是最终的、正确的答案。我只是在这里添加了一点细微差别，以使该功能更加一刀切。

回答by Apostle

The method from Robin will fail if the Collection contains objects rather than primitive types because they need to be assigned using Set and otherwise generate an error that will result in the method returning False. Here is a small adjustment:

如果 Collection 包含对象而不是原始类型，则 Robin 的方法将失败，因为它们需要使用 Set 进行分配，否则会生成错误，导致方法返回 False。这是一个小调整：

'Test if a key is available in a collection
Public Function HasKey(coll As Collection, strKey As String) As Boolean
    On Error GoTo IsMissingError
        Dim val As Variant
'        val = coll(strKey)
        HasKey = IsObject(coll(strKey))
        HasKey = True
        On Error GoTo 0
        Exit Function
IsMissingError:
        HasKey = False
        On Error GoTo 0
End Function