Excel builtin functions and operators can accept both scalars (single values) and arrays (such as a range of cells) as arguments, and will return scalar or array accordingly as results, spilling when approriate.
To mimic this feature in user-defined functions (UDF) is not straightforward, however. I have to test the arguments and handle them accordingly. Is there a simple way to do this without doing this complex logic for each and every UDF?
I have done one such function as shown below, and hope someone can point me to a simpler way to doing this. The function is not actually finished and has holes in it, but I hope you get the idea.
Note that the problem becomes even more complicated if both 'chars' and 'inText' are arrays. But, Excel handles such situations just fine, and you just have to watch out for mixing columns and rows.
Function FindChar(chars As String, inText As Variant, Optional start_Num As Variant = 1) As Variant
' find any char in 'Chars' within 'inText' and return the index of found char or 0 if not found.
If IsRange(inText) Then
' inText is range
Dim ii As Long, res() As Variant
ReDim res(1 To inText.Count)
For ii = 1 To inText.Count
If IsRealArray(start_Num) _
Then res(ii) = FindChar(chars, inText(ii).Value, start_Num(ii)) _
Else res(ii) = FindChar(chars, inText(ii).Value, start_Num)
Next ii
FindChar = res
Exit Function
End If
If IsRealArray(inText) Then
'inText is array
Dim errMSG As String
errMSG = "FindChar: cannot handle array yet"
Debug.Print errMSG
MsgBox errMSG
Stop
Exit Function
End If
' inText is scalar
If start_Num <= 0 Then
FindChar = CVErr(xlErrValue)
Exit Function
End If
If Not inText Like "*[" & chars & "]*" Then
FindChar = 0
Exit Function
End If
Dim jj As Integer, pos As Long, cc as string
FindChar = 0
For jj = 1 To Len(chars)
cc = Mid(chars, jj, 1)
pos = InStr(start_Num, inText, cc, vbTextCompare)
If pos <> 0 Then
FindChar=pos
Exit Function
Next jj
FindChar = 0
End Function
Function IsRange(arg As Variant) As Boolean
IsRange = "Range" = TypeName(arg)
End Function
Function IsRealArray(arg As Variant) As Boolean
' the built in function IsArray will return TRUE for range
' but this function will not
' because it looks for the '(' in TypeName
IsRealArray = InStr(TypeName(arg), "(") > 0
End Function
