Tutorial :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.


Solution: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.


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


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


Solution:4

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  


Solution:5

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      Dim f As Boolean      On Error GoTo err      ExistsInCollection = True      f = 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.


Solution: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.


Solution:7

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  


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


Solution:9

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  


Solution:10

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


Solution:11

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  


Solution:12

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  


Solution:13

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  


Solution:14

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  

Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »