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

User · Answer

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

User · Answer

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

User · Answer

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  amp       amp  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

User · Answer

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    lt   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   lt   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

User · Answer

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

User · Answer

I have some edit  best working for collections    x000D   x000D  Public Function Contains col As collection  key As Variant  As Boolean x000D      Dim obj As Object x000D      On Error GoTo err x000D      Contains   True x000D      Set obj   col Item key  x000D      Exit Function x000D       x000D  err  x000D      Contains   False x000D  End Function x000D   x000D   x000D

User · Answer

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  lt  gt  5         just to clear the error     If Err  lt  gt  0 Then Call Err Clear     Set cObj   Nothing End Function   Source  http   coderstalk blogspot com 2007 09 visual-basic-programming-how-to-check html

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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  lt  gt  5 And Err Number  lt  gt  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

[vba] Determining whether an object is a member of a collection in VBA

Examples related to vba

Examples related to object

Examples related to ms-access

Examples related to collections