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

Question

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

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

Labionasal

Answer 1

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.

Uriiah

is this really the only way to do it? – Labionasal

I am almost positive. I scoured all over the API and the internet to find a different way. It really is not that bad. You can hide the details behind a Contains function so at least you won't have to look at the ugliness! :-) – Uriiah

A collection simply defines something that you can iterate over. This is the correct solution. – Bopp

"correct" perhaps, but still very unsatisfactory. Thanks both. – Labionasal

To be honest, I find Access in itself to be unsatisfactory as a programming platform in general. But we must play with the cards we are dealt. :-) – Uriiah

A VB6/VBA collection is not just something you can iterate over. It also provides optional key access. – Veinule

Joe is right. Using TableDef name to access a TableDef implies key access which takes fixed time and uses internal hash table. That's the best wasy to see if an object exists in VB6 collection. – Aerotherapeutics

@Aerotherapeutics If the key is not in the Iterations "Key" list. Then, what will be the return type? – Israelitish

@Israelitish There will be no return type. You'll get an exception, element not found. – Aerotherapeutics

If you can choose, use a Dictionary, which doesn't throw an error when looking for a nonexistant key, just returns an empty variant – Abernethy

Solution provided by Mark Nold below is far superior – Polka

Every time I have to do something in VBA I find something that shocks me, this time, it is that this answer seems to be the canonical way to do what the OP (and I) need to do. – Allaallah

The ON ERROR route is orders of magnitude faster: see low-bandwidth.blogspot.com.au/2013/12/… – Bohannan

Answer 2

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

Impalpable

This seems like the simplest of all solutions presented here. I've used this and it works perfectly. I did however have to change the col argument to be of type Variant. – Oared

Nearly 6 years later, it is still a perfectly viable solution. I'm using it as is with no issues. – Maori

It is a great solution, it is just a bit silly that thousands of people have to reimplement it. VB/VBA are supposed to be higher level than that. – Elute

Worked very well for me. – Aquiculture

This doesn't work if the value for a key is an object not a primitive - if the value is an object you'll get an assignment error (object references need to be assigned with "Set"), thus returning "False" even if the key exists. Replace the line obj = col(key) with IsObject(col(key)) to handle both object and primitive values. – Mirabel

In the answer from Martin Trummer, it can be seen that collections can contain other collections or Nothing as values. In such cases, this code is not working. However, this seems to be very efficient if you are certain that in your collection you have no values like mentioned before. – Idea

Depending on the use case, one can adapt. For me : Function Contains(col As Variant, key As String) As Boolean : my collection is an Excel Workbook.Names, the key is a String. – Fistic

Answer 3

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.

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

Trussing

I don't perceive this as non elegant... it's a try-catch approach, something very normal in C++ and java, e.g. I'd bet it's much more fast that iterating the whole collection, because VB calculated the hash for the provided key, and searched it on the hash table, not in the item's collection. – Camarillo

this implementation is not okay: i.e. it will return True if any other error than #5 occurs – Broad

errNumber is not 5 here, it's 3265 instead :( ... It's not elegant from this aspect - of relying on hard-coded error codes – Neom

Answer 4

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 Collection can 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.

Idea

Excellent solution, and concise. Thank you! – Lymphocyte

Answer 5

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

Schaal

Answer 6

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.

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.

Veinule

Answer 7

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

Allocution

Answer 8

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

Malvasia

Answer 9

For the case when key is unused for collection:

Public Function Contains(col As Collection, thisItem As Variant) As   Boolean

  Dim item As Variant

  Contains = False
  For Each item In col
    If item = thisItem Then
      Contains = True
      Exit Function
    End If
  Next
End Function

Session

Please edit with more information. Code-only and "try this" answers are discouraged, because they contain no searchable content, and don't explain why someone should "try this". – Ats

This is a disastrous solution in terms of speed, the ON ERROR solution is much better: see low-bandwidth.blogspot.com.au/2013/12/… – Bohannan

The solution is the best, when the collection contains no keys only items, since the ON ERROR solution will not work in this case. What explanation is needed for this simple solution? A loop over the members of the collection and check for equality. – Estrus

Answer 10

It works for me

Public Function contains(col As Collection, key As Variant) As Boolean
    For Each element In col
        If (element = key) Then
            contains = True
            Exit Function
        End If
    Next
    contains = False
End Function

Tatary

Answer 11

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}

Bernhardt

Answer 12

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

Broad

Answer 13

Not my code, but I think it's pretty nicely written. It allows to check by the key as well as by the Object element itself and handles both the On Error method and iterating through all Collection elements.

https://danwagner.co/how-to-check-if-a-collection-contains-an-object/

I'll not copy the full explanation since it is available on the linked page. Solution itself copied in case the page eventually becomes unavailable in the future.

The doubt I have about the code is the overusage of GoTo in the first If block but that's easy to fix for anyone so I'm leaving the original code as it is.

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'INPUT       : Kollection, the collection we would like to examine
'            : (Optional) Key, the Key we want to find in the collection
'            : (Optional) Item, the Item we want to find in the collection
'OUTPUT      : True if Key or Item is found, False if not
'SPECIAL CASE: If both Key and Item are missing, return False
Option Explicit
Public Function CollectionContains(Kollection As Collection, Optional Key As Variant, Optional Item As Variant) As Boolean
    Dim strKey As String
    Dim var As Variant

    'First, investigate assuming a Key was provided
    If Not IsMissing(Key) Then

        strKey = CStr(Key)

        'Handling errors is the strategy here
        On Error Resume Next
            CollectionContains = True
            var = Kollection(strKey) '<~ this is where our (potential) error will occur
            If Err.Number = 91 Then GoTo CheckForObject
            If Err.Number = 5 Then GoTo NotFound
        On Error GoTo 0
        Exit Function

CheckForObject:
        If IsObject(Kollection(strKey)) Then
            CollectionContains = True
            On Error GoTo 0
            Exit Function
        End If

NotFound:
        CollectionContains = False
        On Error GoTo 0
        Exit Function

    'If the Item was provided but the Key was not, then...
    ElseIf Not IsMissing(Item) Then

        CollectionContains = False '<~ assume that we will not find the item

        'We have to loop through the collection and check each item against the passed-in Item
        For Each var In Kollection
            If var = Item Then
                CollectionContains = True
                Exit Function
            End If
        Next var

    'Otherwise, no Key OR Item was provided, so we default to False
    Else
        CollectionContains = False
    End If

End Function

Chartist

Answer 14

i used this code to convert array to collection and back to array to remove duplicates, assembled from various posts here (sorry for not giving properly credit).

Function ArrayRemoveDups(MyArray As Variant) As Variant
Dim nFirst As Long, nLast As Long, i As Long
Dim item As Variant, outputArray() As Variant
Dim Coll As New Collection

'Get First and Last Array Positions
nFirst = LBound(MyArray)
nLast = UBound(MyArray)
ReDim arrTemp(nFirst To nLast)
i = nFirst
'convert to collection
For Each item In MyArray
    skipitem = False
    For Each key In Coll
        If key = item Then skipitem = True
    Next
    If skipitem = False Then Coll.Add (item)
Next item
'convert back to array
ReDim outputArray(0 To Coll.Count - 1)
For i = 1 To Coll.Count
    outputArray(i - 1) = Coll.item(i)
Next
ArrayRemoveDups = outputArray
End Function

Katabatic

Answer 15

I did it like this, a variation on Vadims code but to me a bit more readable:

' Returns TRUE if item is already contained in collection, otherwise FALSE

Public Function Contains(col As Collection, item As String) As Boolean

    Dim i As Integer

    For i = 1 To col.Count

    If col.item(i) = item Then
        Contains = True
        Exit Function
    End If

    Next i

    Contains = False

End Function

Moriahmoriarty

Answer 16

I wrote this code. I guess it can help someone...

Public Function VerifyCollection()
    For i = 1 To 10 Step 1
       MyKey = "A"
       On Error GoTo KillError:
       Dispersao.Add 1, MyKey
       GoTo KeepInForLoop
KillError: 'If My collection already has the key A Then...
        count = Dispersao(MyKey)
        Dispersao.Remove (MyKey)
        Dispersao.Add count + 1, MyKey 'Increase the amount in relationship with my Key
        count = Dispersao(MyKey) 'count = new amount
        On Error GoTo -1
KeepInForLoop:
    Next
End Function

Coble