Function that emulates FIND(find_text,within_text,start_num)

[Henrik]

Hi,

I would like to write a function FINDC that emulated
Excel's FIND worksheet function but returns '0' rather
than #VALUE! if find_text does not appear in within_text.

Is it possible, by the way, to see the source code for
excel functions in Visual Basic?

Thanks,
Henrik

 

[Tom Ogilvy]

Excel functions aren't written in visual basic, but the short answer is no.

Public Function FindC(sStr As String, _
sStr1 As String, Optional iloc As Long = 1)
FindC = InStr(iloc, sStr1, sStr, vbTextBinary)
End Function

Lightly tested.

 

[Jake Marx]

Hi Henrik,

Alternatively, you could use the actual FIND worksheet function and trap the
error that occurs when it fails:

Public Function FINDC(find_text As String, within_text As String, _
Optional start_num As Integer = 1) As Integer
On Error Resume Next
FINDC = Application.WorksheetFunction.Find(find_text, _
within_text, start_num)
End Function

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

 

[Tom Ogilvy]

The actual constant (vbTextBinary) is incorrect, but doesn't affect
operation because it resolves to zero which is correct - but to be accurate
here is a revision. The constant should be vbBinaryCompare

Public Function FindC(sStr As String, _
sStr1 As String, Optional iloc As Long = 1)
FindC = InStr(iloc, sStr1, sStr, vbBinaryCompare)
End Function