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

Question

How to determine if an object is a member of a collection in VBA?

In particular, I need to find out if a table definition is a member of the TableDefs collection.

Answer 1

It’s best to go through the members of the collection and see if they match what you are looking for. Believe 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 that the item does not exist.

Answer 2

Isn't that 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 

Answer 3

Not quite elegant, but the best (and fastest) solution I could find was to use OnError. This will be significantly faster than iterating over 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 

Answer 4

This is an old question. I carefully examined all the answers and comments, checked the solutions for performance.

I came up with the fastest option for my environment, which does not fail when the collection contains both objects and 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. Thus, the col As Collection parameter can be replaced with some other variable of type collection, and the function should still work. For example, in my current project I will have it as col As ListColumns .

Answer 5

I created this solution from the above suggestions mixed with Microsoft's solution 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 

Answer 6

You can shorten the suggested code for this, as well as generalize for unforeseen errors. Here you are:

 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 

Answer 7

In your particular case (TableDefs) iterating over the collection and checking the name is a good approach. This is normal 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 (for example, you could use the 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 to access the element and catch the error.

Answer 8

I have some changes that work best 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 

Answer 9

This requires some additional settings if the elements 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 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}

Answer 10

this version works for primitive types and for classes (including a short test method)

 ' 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 ' eg ' 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 

Answer 11

In this case, you might consider using a dictionary instead of a collection.

Answer 12

Turning the question: if an object is of type TableDef , then by definition it should be in the collection of TableDefs , no? Do you want to check if your TableDef Parent matches this TableDef Parent collection?

Answer 13

I am new to VBA, but you can use Excel's match function. Say you want to know if A appears in the range. if isna(match(a,range,0)) is false, then A is a member of this set.

Answer 14

In the case when the key is not used 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 

Answer 15

Not my code, but I think it is pretty well written. It allows you to check both by key and by the Object element itself and processes both the On Error method and iteration over all Collection elements.

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

I will not copy the full explanation, as it is available on the linked page. The solution itself is copied in case the page eventually becomes unavailable in the future.

I doubt the code due to the excessive use of GoTo in the first If block, but this is easy to fix, so I leave the source 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 

Answer 16

I did it this way: a variation of the Vadims code, but for me a little 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 

Answer 17

I wrote this code. I think this might 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