检查键是否在Excel VBA中的集合中的通用方法 [英] Generic way to check if a key is in a Collection in Excel VBA

问题描述

我的代码中有不同的集合.有些保存对象(各种类型)，另一些对象中包含类型(如Long).

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

有没有一种方法可以检查键是否包含在适用于类型和对象的Collection中?

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.

第一个功能:

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

第二功能:

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

这两个函数的原因是，如果我传递具有Longs对的Collection(该函数始终返回False)，则ContainsObject似乎不起作用.

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

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

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

推荐答案

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

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

但这会产生Type Mismatch编译错误，即试图将Long分配给Object:

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

因此，对于泛型函数(沿代码行)检查Collection键是否有效，可以使用:

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

测试代码:

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

在 MSDN 上有一篇有用的文章

这篇关于检查键是否在Excel VBA中的集合中的通用方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！