确定一个对象是否是 VBA 中集合的成员

Determining whether an object is a member of a collection in VBA

提问by inglesp

How do I determine whether an object is a member of a collection in VBA?

如何确定对象是否是 VBA 中集合的成员？

Specifically, I need to find out whether a table definition is a member of the TableDefscollection.

具体来说，我需要找出表定义是否是TableDefs集合的成员。

采纳答案by Gilligan

Your best bet is to iterate over the members of the collection and see if any match what you are looking for. Trust me I have had to do this many times.

最好的办法是迭代集合的成员，看看是否有任何匹配你正在寻找的。相信我，我不得不多次这样做。

The second solution (which is much worse) is to catch the "Item not in collection" error and then set a flag to say the item does not exist.

第二种解决方案（更糟糕）是捕获“Item not in collection”错误，然后设置一个标志来表示该项目不存在。

回答by Vadim

Isn't it good enough?

还不够好吗？

Public Function Contains(col As Collection, key As Variant) As Boolean
Dim obj As Variant
On Error GoTo err
    Contains = True
    obj = col(key)
    Exit Function
err:

    Contains = False
End Function

回答by Mark Nold

Not exactly elegant, but the best (and quickest) solution i could find was using OnError. This will be significantly faster than iteration for any medium to large collection.

不是很优雅，但我能找到的最好（也是最快）的解决方案是使用 OnError。这将比任何大中型集合的迭代快得多。

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

回答by ZygD

This is an old question. I have carefully reviewed all the answers and comments, tested the solutions for performance.

这是一个老问题。我仔细查看了所有答案和评论，测试了解决方案的性能。

I came up with the fastest option for my environment which does not fail when a collection has objects as well as primitives.

我为我的环境想出了最快的选项，当集合具有对象和基元时不会失败。

Public Function ExistsInCollection(col As Collection, key As Variant) As Boolean
    On Error GoTo err
    ExistsInCollection = True
    IsObject(col.item(key))
    Exit Function
err:
    ExistsInCollection = False
End Function

In addition, this solution does not depend on hard-coded error values. So the parameter col As Collectioncan be substituted by some other collection type variable, and the function must still work. E.g., on my current project, I will have it as col As ListColumns.

此外，该解决方案不依赖于硬编码的错误值。所以参数col As Collection可以被一些其他的集合类型变量代替，并且该函数必须仍然有效。例如，在我当前的项目中，我会将它作为col As ListColumns.

回答by Gov_Programmer

I created this solution from the above suggestions mixed with microsofts solution of for iterating through a collection.

我根据上述建议与微软的解决方案混合创建了这个解决方案，用于迭代集合。

Public Function InCollection(col As Collection, Optional vItem, Optional vKey) As Boolean
On Error Resume Next

Dim vColItem As Variant

InCollection = False

If Not IsMissing(vKey) Then
    col.item vKey

    '5 if not in collection, it is 91 if no collection exists
    If Err.Number <> 5 And Err.Number <> 91 Then
        InCollection = True
    End If
ElseIf Not IsMissing(vItem) Then
    For Each vColItem In col
        If vColItem = vItem Then
            InCollection = True
            GoTo Exit_Proc
        End If
    Next vColItem
End If

Exit_Proc:
Exit Function
Err_Handle:
Resume Exit_Proc
End Function

回答by KthProg

You can shorten the suggested code for this as well as generalize for unexpected errors. Here you go:

您可以为此缩短建议的代码并概括意外错误。干得好：

Public Function InCollection(col As Collection, key As String) As Boolean

  On Error GoTo incol
  col.Item key

incol:
  InCollection = (Err.Number = 0)

End Function

回答by Levent

I have some edit, best working for collections:

我有一些编辑，最适合收藏：

Public Function Contains(col As collection, key As Variant) As Boolean
    Dim obj As Object
    On Error GoTo err
    Contains = True
    Set obj = col.Item(key)
    Exit Function
    
err:
    Contains = False
End Function

回答by Joe

In your specific case (TableDefs) iterating over the collection and checking the Name is a good approach. This is OK because the key for the collection (Name) is a property of the class in the collection.

在您的特定情况下（TableDefs），迭代集合并检查 Name 是一个好方法。这是可以的，因为集合 (Name) 的键是集合中类的属性。

But in the general case of VBA collections, the key will not necessarily be part of the object in the collection (e.g. you could be using a Collection as a dictionary, with a key that has nothing to do with the object in the collection). In this case, you have no choice but to try accessing the item and catching the error.

但是在 VBA 集合的一般情况下，键不一定是集合中对象的一部分（例如，您可以将集合用作字典，键与集合中的对象无关）。在这种情况下，您别无选择，只能尝试访问该项目并捕获错误。

回答by muscailie

It requires some additional adjustments in case the items in the collection are not Objects, but Arrays. Other than that it worked fine for me.

如果集合中的项目不是对象而是数组，则需要进行一些额外的调整。除此之外，它对我来说效果很好。

Public Function CheckExists(vntIndexKey As Variant) As Boolean
    On Error Resume Next
    Dim cObj As Object

    ' just get the object
    Set cObj = mCol(vntIndexKey)

    ' here's the key! Trap the Error Code
    ' when the error code is 5 then the Object is Not Exists
    CheckExists = (Err <> 5)

    ' just to clear the error
    If Err <> 0 Then Call Err.Clear
    Set cObj = Nothing
End Function

_{Source: http://coderstalk.blogspot.com/2007/09/visual-basic-programming-how-to-check.html}

_{来源：http: //coderstalk.blogspot.com/2007/09/visual-basic-programming-how-to-check.html}

回答by TmTron

this version works for primitive types and for classes (short test-method included)

此版本适用于原始类型和类（包括简短的测试方法）

' TODO: change this to the name of your module
Private Const sMODULE As String = "MVbaUtils"

Public Function ExistsInCollection(oCollection As Collection, sKey As String) As Boolean
    Const scSOURCE As String = "ExistsInCollection"

    Dim lErrNumber As Long
    Dim sErrDescription As String

    lErrNumber = 0
    sErrDescription = "unknown error occurred"
    Err.Clear
    On Error Resume Next
        ' note: just access the item - no need to assign it to a dummy value
        ' and this would not be so easy, because we would need different
        ' code depending on the type of object
        ' e.g.
        '   Dim vItem as Variant
        '   If VarType(oCollection.Item(sKey)) = vbObject Then
        '       Set vItem = oCollection.Item(sKey)
        '   Else
        '       vItem = oCollection.Item(sKey)
        '   End If
        oCollection.Item sKey
        lErrNumber = CLng(Err.Number)
        sErrDescription = Err.Description
    On Error GoTo 0

    If lErrNumber = 5 Then ' 5 = not in collection
        ExistsInCollection = False
    ElseIf (lErrNumber = 0) Then
        ExistsInCollection = True
    Else
        ' Re-raise error
        Err.Raise lErrNumber, mscMODULE & ":" & scSOURCE, sErrDescription
    End If
End Function

Private Sub Test_ExistsInCollection()
    Dim asTest As New Collection

    Debug.Assert Not ExistsInCollection(asTest, "")
    Debug.Assert Not ExistsInCollection(asTest, "xx")

    asTest.Add "item1", "key1"
    asTest.Add "item2", "key2"
    asTest.Add New Collection, "key3"
    asTest.Add Nothing, "key4"
    Debug.Assert ExistsInCollection(asTest, "key1")
    Debug.Assert ExistsInCollection(asTest, "key2")
    Debug.Assert ExistsInCollection(asTest, "key3")
    Debug.Assert ExistsInCollection(asTest, "key4")
    Debug.Assert Not ExistsInCollection(asTest, "abcx")

    Debug.Print "ExistsInCollection is okay"
End Sub