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
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
Stephen Bullen:
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
8 June 2005, 5:28 amDick Kusleika:
8 June 2005, 1:28 pmLen(Typename())- I've never seen that before. Very clever.RobertV:
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.
12 June 2005, 1:54 amLon Ingram:
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.
13 June 2005, 3:01 amLon Ingram:
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!
13 June 2005, 3:26 am