IsIn Collection or Array

Created by Bill Manville:
Checks whether a name exists in a collection.
For example, If IsIn(ActiveWorkbook.Names, “ThisOne”) Then …

Function IsIn(oCollection As Object, stName As String) As Boolean
    Dim O As Object
    On Error GoTo NotIn
    Set O = oCollection(stName)
    IsIn = True   ‘succeeded in creating a pointer to the object so
                 ‘must be there
NotIn:
 
End Function

Editor’s Note:

Another way:

Function IsInCol(oCollection As Object, stName As String) As Boolean
 
    On Error Resume Next
    IsInCol = Not oCollection(stName) Is Nothing
   
End Function

Tushar posted a similar function earlier on the newsgroups, but for arrays instead of collections. Nice use of the Join function.

Function IsInArr(ByVal StringSetElementsAsArray As Variant, _
    ByVal sName As String) As Boolean
 
    On Error Resume Next
    IsInArr = InStr(1, _
        Chr$(0) & Join(StringSetElementsAsArray, Chr$(0)) & Chr$(0), _
        Chr$(0) & sName & Chr$(0), _
        vbTextCompare) > 0
 
End Function

Another way:

Function IsInArr2(ByVal StringSetElementsAsArray As Variant, _
    ByVal sName As String) As Boolean
   
    On Error Resume Next
    IsInArr2 = Not IsError(Application.Match(sName, StringSetElementsAsArray, False))
   
End Function
Posted in Uncategorized

12 thoughts on “IsIn Collection or Array

  1. Both versions of checking if an element is in a collection assume it’s a collection of objects, but any data type can be put in a VBA Collection object. If we have a collection of strings, say, they both error out on the Set / Is Nothing check, rather than the collection indexing. For that reason, I use the following (which I hope comes out OK):

    Function IsIn2(oCollection As Object, sKey As String) As Boolean
    On Error Resume Next
    IsIn2 = Len(TypeName(oCollection(sKey))) > 0
    End Function

    Regards

    Stephen Bullen

  2. 1. For completeness sake, shouldn’t a created object always be annihilated when no longer in use?

    Function IsIn(oCollection As Object, stName As String) As Boolean
    Dim O As Object
    On Error GoTo NotIn
    Set O = oCollection(stName)
    IsIn = True
    Set O = Nothing

    NotIn:
    End Function

    2. Dictionary Objects do have a builtin method Exists to check whether or not a member exist. This bypasses the ugly ON Error statement. I understand that the use of some Collections (e.g WorkSheets, Charts, …) is imposed by the Excel Object Model but I always wonder why Dictionary Objects (which are much faster and more natural) are not more widely used. Perhaps because you have to include them through a reference to vbscript? (vbscripting runtime?). Any opinions are wellcomed.

  3. RobertV:
    1. Explicitly destroying objects is deprecated. Your O will be destroyed when it goes out of scope. I used to do this too, because the MS Press books do it. Then I read about it on Eric Lippert’s blog.

    The functions listed all assume that the passed collection is not nothing. They also hide any other error that might rear its head. This would not be a good idea in production code. Why do the On Error Resume Next nonsense when you know what the error will be?

    I would do something more along the lines of:

    Private Const ERR_BAD_ARGUMENT As Long = 5
    Private Const ERR_BAD_SUBSCRIPT As Long = 9

    Public Function IsInCol(oCol As Object, sKey As String)
    Dim v As Variant

    On Error GoTo ErrWrangler

    v = oCol.Item(sKey)

    IsInCol = True

    ExitHere:
    Exit Function

    ErrWrangler:
    Select Case Err.Number
    Case ERR_BAD_ARGUMENT, ERR_BAD_SUBSCRIPT
    IsInCol = False
    Err.Clear
    Resume ExitHere

    Case Else
    Err.Raise Err.Number ‘yada yada
    End Select
    End Function

    I didn’t do much testing on this, so use at your own risk. Should be safer than the other versions posted here. Yes, variants are slow, but so are collections. Code a custom type-specific version if you really need it.

  4. Whoops, above should be:

    Private Const ERR_BAD_ARGUMENT As Long = 5
    Private Const ERR_BAD_SUBSCRIPT As Long = 9

    Public Function IsInCol(oCol As Object, sKey As String)
    Dim f As Boolean

    On Error GoTo ErrWrangler

    f = IsObject(oCol.Item(sKey))
    IsInCol = True

    ExitHere:
    Exit Function

    ErrWrangler:
    Select Case Err.Number
    Case ERR_BAD_ARGUMENT, ERR_BAD_SUBSCRIPT
    IsInCol = False
    Err.Clear
    Resume ExitHere

    Case Else
    Err.Raise Err.Number ‘yada yada
    End Select
    End Function

    No variant, too. Woot!

  5. This discussion is very useful. I’d been using Chip Pearson’s KeyExistsInCollection, from http://www.cpearson.com/excel/CollectionsAndDictionaries.htm :
    Public Function KeyExistsInCollection(Coll As Collection, KeyName As String) As Boolean
        Dim V As Variant
        On Error Resume Next
        Err.Clear
        V = Coll(KeyName)
        If Err.Number = 0 Then
            KeyExistsInCollection = True
        Else
            KeyExistsInCollection = False
        End If
    End Function

    This worked when my collections contained strings. But I've just spent half an hour tracking down a bug caused by it not working for a collection that contains collections. Presumably because then one needs a

    Set V = Coll(KeyName)

    . I note Lon Ingram's warning about hiding other errors that might raise their heads. But I also like Stephen Bullen's code, because it's short and very easy to understand. Lon, or anyone else, what other errors could arise other than the two you make constants for?

  6. Just to note that since IsIn2 is declared as a Boolean, then Stephan Bullen’s code should be able to be simplified (ever so slightly) to this…

    Function IsIn2(oCollection As Object, sKey As String) As Boolean
      On Error Resume Next
      IsIn2 = Len(TypeName(oCollection(sKey)))
    End Function
  7. Why bother with the Len call?

    Function f(c As Collection, k As String) As Boolean
      On Error Resume Next
      f = VarType(c(k)) + 1
    End Function
  8. @fzz,

    Good point! I guess we can extend your idea to one of these assignments as well…

    f = VarType(c(k)) >= 0

    or…

    f = IsNumeric(VarType(c(k))

    or…

    f = VarType(C(k)) Like “#”

  9. Rick and fzz, I’m still worried about what Lon says about errors: “They [the functions above his postings] also hide any other error that might rear its head. This would not be a good idea in production code. Why do the On Error Resume Next nonsense when you know what the error will be?”

  10. I guess there are differences of opinion about passing objects as generic Object types or a specific object types since VBA doesn’t syntax check different object types. So it’s a question of where and how to throw the error. If errors must be thrown, then I’d declare procedure arguments as specific object types and let VBA throw the runtime error. If wrong object type errors should always be caught, then better to do so at the source rather than in a library procedure.

    So maybe something like

    Function f(c As Object, k As String) As Boolean
      If c Is Nothing Then
        ‘nothing to do, will return False
     ElseIf TypeOf c Is Collection Then
        On Error Resume Next
        f = VarType(c(k)) + 1
        Err.Clear
      Else
        ‘1st arg invalid, take whatever action you want
     End If
    End Function

    If c(k) doesn’t throw an error, VarType will ALWAYS return an integer, and the integers it returns will NEVER throw overflow/underflow errors when incremented by 1. So only 2 errors could occur: k isn’t a key in c; c is neither a Collection object or Nothing. [Note: Nothing is treated as a Collection with no items/keys.] Trapping all other errors is madness. The only other runtime errors which might occur are environmental ones like no more stack space, out of memory, CPU overheating, etc. Nothing gained trying to trap those.

  11. @Jocelyn,

    I cannot think of any other errors of consequence that this code could possibly raise, so On Error Resume Next should be fine to use as shown. Perhaps to be safe, though, we might add one more line to protect any other error handlers that might exist “up the line”…

    Err.Clear

    Make this the last line of code in the function. Without it, the Err object would hold onto any generated error which, if one were not careful, could trip up error trapping code in the calling routine (not very likely, but possible, so we might as well protect against it). We do **not** need to execute an On Error GoTo 0 statement to close the error handler as it will die automatically when the function ends.


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.