MrExcel Publishing
Your One Stop for Excel Tips & Solutions

EXCEL USER-DEFINED FUNCTION USING EXCEL TEXT FUNCTION


Posted by John Walker on September 19, 2001 11:31 AM

I tried creating a user-defined function in Visual Basic using some of the text functions in Excel. I wanted to create the following:

Function Whatever(TextDelim, CellText)
Whatever = Right(CellText, Len(CellText)-Search(TextDelim, CellText))

End Function

I get an error concerning the non-existence of the worksheet function "Search." I can write a function using "Right" and "Len" but not with "Search," even though Search is a legitimate Excel text function. What gives?


Posted by Juan Pablo on September 19, 2001 11:36 AM

Replace Search with WorksheetFunction.Find (NT)

Posted by Russell Hauf on September 19, 2001 11:51 AM

So are you trying to return the portion of the text to the right of the delimiter you pass in?

For example, if you had the string:

Hello World;_Goodbye Sun

If would return:

_Goodbye Sun

If you passed a semi-colon (;) as the delimiter.

If so, say cell A1 had the string mentioned above, and B1 had a semi-colon. You could easily write a worksheet formula for this:

=RIGHT(A1, LEN(A1)-FIND(B1,A1))

If you really wanted a user-defined function, it would look something like this (you need to make sure that you put a return type for the function, or it won't return anything):

'******************************************************************************
Public Function Whatever(strText as String, strDelim as String) as String

Whatever = Right(strText, Len(strText) - InStr(strText, strDelim)+1)

End Function
'******************************************************************************


Or, an even better way:

'******************************************************************************
Public Function Whatever(strText as String, strDelim as String) as String

Whatever = Mid(strText, InStr(strText, strDelim)+1)

End Function
'******************************************************************************

Hope this helps,

Russell

Posted by Ivan F Moala on September 19, 2001 7:12 PM

Len, right, left are members of the string class
in your VBA application.
Search is a member of the worksheet function class
under the Excel library.

So define it as Application.Worksheetfunction.search etc

Ivan