Here's the function I wrote to mimic @IsMember. It works with both arrays and lists (we use lists A LOT). I've also included my test fixture code to exercise it. We have a master library that feeds into all our other templates, so this function is included there.
I've commented out our error handling so you can insert your own.
John
Public Function AtIsMember( _
varNeedle As Variant, _ ' text, list, or array we seek
varHaystack As Variant _ ' list or array in which we seek
) As Boolean
' this function mimics @IsMember() formula language
' it is case sensitive
' if needle is text (or can be converted to text) then
' see if it's in the haystack
' if needle is a collection of strings, see if they're ALL in the
' haystack
' return True if ALL needles are found in haystack, False otherwise
' throws custom error 1109 if parameters fail
On Error Goto errHandler
Dim varH As Variant ' temporary needle
Dim varN As Variant ' temporary haystack
Dim boolNeedlesFound As Boolean
Dim boolOneNeedle As Boolean
' make sure the needle is text, list, or array
If Isarray( varNeedle ) Then
varN = varNeedle
Elseif Islist( varNeedle ) Then
varN = varNeedle
Elseif Datatype( varNeedle ) < 9 Then
Redim varN( 0 )
varN( 0 ) = Trim$( Cstr( varNeedle ) )
Else
Error 1109, "First parameter must be text, array or list."
End If
' make sure haystack is list or array
If Isarray( varHaystack ) Then
varH = varHaystack
Elseif Islist( varHaystack ) Then
varH = varHaystack
Else
Error 1109, "Second parameter must be array or list."
End If
' we got this far, so we can look for all the needles in the haystack
boolNeedlesFound = True ' default
Forall vN In varN
boolOneNeedle = False
Forall vH In varH
If Trim$( vN ) = Trim$( vH ) Then
boolOneNeedle = True
Exit Forall
End If
End Forall
boolNeedlesFound = boolNeedlesFound And boolOneNeedle
If Not boolNeedlesFound Then
Exit Forall
End If
End Forall
AtIsMember = boolNeedlesFound
Goto Done
errHandler:
' Call ErrorMessage( Err, Erl, Error$, Lsi_info( 2 ), False )
Resume Done
Done:
End Function
Here's the test function I wrote to exercise it.
Private Function TestAtIsMember()
On Error Goto errHandler
On Error 1109 Goto errHandler1109
' needles
Dim varN1 As Variant
Dim strN2 As String
Dim strN3List List As String
Dim docN4 As NotesDocument
' haystacks
Dim varH1 As Variant
Dim varH2 As Variant
Dim strH3List List As String
Dim varH4 As Variant
Const COMMA = ","
Dim boolReturn As Boolean
Print "------------------------------ " & Now() & " -----------------------"
' populate needles
varN1 = Split( "black,brown,blue", COMMA )
strN2 = "blue"
strN3List( 1 ) = "black"
strN3List( "george" ) = "green"
strN3List( "today's date" ) = "black"
' populate haystacks
varH1 = Split( "black,brown,blue", COMMA )
varH2 = Split( "black,brown,green", COMMA )
strH3List( 198 ) = "blue"
strH3List( "tom" ) = "black"
strH3List( "to do" ) = "brown"
strH3List( "a" ) = "lavender"
varH4 = Split( "black,purple,lavender,brown,green", COMMA )
' perform tests
boolReturn = AtIsMember( varN1, varH1 )
Print "11 Expected: True, returned: " boolReturn
boolReturn = AtIsMember( varN1, varH2 )
Print "12 Expected: False, returned: " boolReturn
boolReturn = AtIsMember( varN1, strH3List )
Print "13 Expected: True, returned: " boolReturn
boolReturn = AtIsMember( varN1, varH4 )
Print "14 Expected: False, returned: " boolReturn
boolReturn = AtIsMember( strN2, varH1 )
Print "21 Expected: True, returned: " boolReturn
boolReturn = AtIsMember( strN2, varH2 )
Print "22 Expected: False, returned: " boolReturn
boolReturn = AtIsMember( strN2, strH3List )
Print "23 Expected: True, returned: " boolReturn
boolReturn = AtIsMember( strN2, varH4 )
Print "24 Expected: False, returned: " boolReturn
boolReturn = AtIsMember( strN3List, varH1 )
Print "31 Expected: False, returned: " boolReturn
boolReturn = AtIsMember( strN3List, varH2 )
Print "32 Expected: True, returned: " boolReturn
boolReturn = AtIsMember( strN3List, strH3List )
Print "33 Expected: False, returned: " boolReturn
boolReturn = AtIsMember( strN3List, varH4 )
Print "34 Expected: True, returned: " boolReturn
boolReturn = AtIsMember( docN4, varH1 )
Print "41 Expected: Error, returned: " boolReturn
boolReturn = AtIsMember( strN3List, docN4 )
Print "42 Expected: Error, returned: " boolReturn
boolReturn = AtIsMember( "", varH1 )
Print "43 Expected: False, returned: " boolReturn
boolReturn = AtIsMember( "test", "" )
Print "44 Expected: Error, returned: " boolReturn
Goto Done
errHandler1109:
boolReturn = False
Print "OUR CUSTOM ERROR FOR TESTING AtIsMember() " & Err & ": " & Error$
Resume Next
errHandler:
' Call ErrorMessage( Err, Erl, Error$, Lsi_info( 2 ), False )
Resume Done
Done:
End Function |