Left Lookup Custom function

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jacob - 27 Dec 2006 17:52 GMT

Hello all I am trying to write a custom function to do the work of my
current function, so that all I have to do is type the custom function
name in and select the cell. This is what I have so far, but is not
working for me. As I am new to Custom Functions some help would be
greatly appreciated.

This is the original function

=Left(A2,Search(" ",A2,1)-1)

This is what I am trying to use in VBA

Function LeftSearch(textStr As Variant)

LeftSearch = Left(textStr, Search(" ",textStr,1) - 1)

End Function

Thanks in Advance

Jacob

Reply to this Message

Dave Peterson - 27 Dec 2006 18:06 GMT

VBA has the equivalent of =Search() in its InStr.

But you'll find that the UDF you create will be slower than using that formula
you want to dump.

Option Explicit
Function LeftSearch(textStr As String) As String
Dim SpacePos As Long
SpacePos = InStr(1, textStr & " ", " ", vbTextCompare)
LeftSearch = Left(textStr, SpacePos - 1)
End Function

> Hello all I am trying to write a custom function to do the work of my
> current function, so that all I have to do is type the custom function
[quoted text clipped - 17 lines]
>
> Jacob

Signature

Dave Peterson

Reply to this Message

Jacob - 27 Dec 2006 18:51 GMT

Thanks Dave, This works great.

Reply to this Message

Rate this thread: