Function that emulates FIND(find_text,within_text,start_num)

  • Thread starter Thread starter Henrik
  • Start date Start date
H

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
 
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.
 
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]
 
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
 
Back
Top