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
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
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
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
ByVal sName As String) As Boolean
On Error Resume Next
IsInArr2 = Not IsError(Application.Match(sName, StringSetElementsAsArray, False))
End Function
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
– I’ve never seen that before. Very clever.
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.
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.
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!
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 BooleanDim 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
. 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?
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…
On Error Resume Next
IsIn2 = Len(TypeName(oCollection(sKey)))
End Function
Why bother with the Len call?
On Error Resume Next
f = VarType(c(k)) + 1
End Function
@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 “#”
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?”
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
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.
@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.