I'm having trouble writing an Excel macro (actually a function) that
will call another Excel function.

Background:

The FIND function in Excel returns the location in one string with the
first occurrence of another string. For example:
FIND("ar","marker marker") returns the number 2.

I'm trying to write a user-defined function in Excel that will find
the location of the last character of the last occurence; in other
words, starting from the right side of a particular string. My
function FINDR("ar","marker marker") would return the value 9.

There are probably several ways to do it, but here's what I have done
so far: First, I wrote a function ReverseString, which reverses all
the characters in the given string. (at the bottom of this posting;
improvements are welcome)

My idea was to use this new function to reverse both the "within_text"
string and the "find_text" strings, and then use the regular Excel
function FIND to search for the reversed "find_text" in the
"within_text" string.

In my example, the Excel FIND function would find the first "ra" in
"rekram rekram", and return the number 4. Then I would then subtract
4 from the length of the original string (13) to get the desired 9.

The problem is, when I call FIND from a VBA function, it does not do
the same thing as the FIND function. Instead, the VBA FIND does
something completely different. It's complicated and I don't really
understand it. Basically its search domain is not limited to a single
string of characters, but instead it "finds" information about the
entire worksheet. This includes fonts, other formatting information,
and other data far beyond the scope that I need.

I know I could recreate the Excel FIND in VBA, but it seems like
re-inventing the wheel.

How can I call the Excel function "FIND" from a function or macro in
Excel VBA?

The function ReverseString below works as desired. The function FINDR
does not.

Thanks for any help.

Steven

' = = = = = = = = =
Function ReverseString(in_text As String) As String
r_s = ""
For i = Len(in_text) To 1 Step -1
r_s = r_s & Mid(in_text, i, 1)
Next
ReverseString = r_s
End Function

Function FINDR(find_text As String, within_text As String) As Long
find_rev = ReverseString(find_text)
within_rev = ReverseString(within_text)

' The following line does NOT WORK
loc = Find(find_rev, within_rev)

FINDR = LEN(within_text) - loc

End Function

' = = = = = = = = =

--
Steven M - lid
(remove wax and invalid to reply)

On a web site of tips on how to use Microsoft Word:
"Word rarely misses an opportunity to perplex"

Steven,

The inStr and RevInstr functions do what you want.
Take a look in VBA help.

Also to use an Excel worksheet functions with VBA,
prefix the call with Application or WorksheetFunction.
All 3 of these should work...
Application.Find
WorksheetFunction.Find
Application.WorksheetFunction.Find

Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


"Steven M (remove wax and invalid to reply)"
wrote in message
I'm having trouble writing an Excel macro (actually a function) that
will call another Excel function.
Background:
The FIND function in Excel returns the location in one string with the
first occurrence of another string. For example:
FIND("ar","marker marker") returns the number 2.

I'm trying to write a user-defined function in Excel that will find
the location of the last character of the last occurence; in other
words, starting from the right side of a particular string. My
function FINDR("ar","marker marker") would return the value 9.

There are probably several ways to do it, but here's what I have done
so far: First, I wrote a function ReverseString, which reverses all
the characters in the given string. (at the bottom of this posting;
improvements are welcome)

My idea was to use this new function to reverse both the "within_text"
string and the "find_text" strings, and then use the regular Excel
function FIND to search for the reversed "find_text" in the
"within_text" string.

In my example, the Excel FIND function would find the first "ra" in
"rekram rekram", and return the number 4. Then I would then subtract
4 from the length of the original string (13) to get the desired 9.

The problem is, when I call FIND from a VBA function, it does not do
the same thing as the FIND function. Instead, the VBA FIND does
something completely different. It's complicated and I don't really
understand it. Basically its search domain is not limited to a single
string of characters, but instead it "finds" information about the
entire worksheet. This includes fonts, other formatting information,
and other data far beyond the scope that I need.

I know I could recreate the Excel FIND in VBA, but it seems like
re-inventing the wheel.

How can I call the Excel function "FIND" from a function or macro in
Excel VBA?

The function ReverseString below works as desired. The function FINDR
does not.

Thanks for any help.

Steven

' = = = = = = = = =
Function ReverseString(in_text As String) As String
r_s = ""
For i = Len(in_text) To 1 Step -1
r_s = r_s & Mid(in_text, i, 1)
Next
ReverseString = r_s
End Function

Function FINDR(find_text As String, within_text As String) As Long
find_rev = ReverseString(find_text)
within_rev = ReverseString(within_text)

' The following line does NOT WORK
loc = Find(find_rev, within_rev)

FINDR = LEN(within_text) - loc

End Function
' = = = = = = = = =
Steven M - lid
(remove wax and invalid to reply)
On a web site of tips on how to use Microsoft Word:
"Word rarely misses an opportunity to perplex"

Steven - try looking for InstrRev function.
Jim had right function ... but typed in the name, uh, backwards.
Ironic, no?

Hi

you made your own ReverseString function, but VBA has already
string reverse function - that is StrReverse(expression).
if you use this function, your FINDR will be more short and simple.

Function findr(ByVal s As String, ByVal src As String) As Long
findr = Len(src) - Application.Find(StrReverse(s), StrReverse(src))
End Function

keizi

"Steven M (remove wax and invalid to reply)" wrote
in message ...
I'm having trouble writing an Excel macro (actually a function) that
will call another Excel function.

Background:

The FIND function in Excel returns the location in one string with the
first occurrence of another string. For example:
FIND("ar","marker marker") returns the number 2.

I'm trying to write a user-defined function in Excel that will find
the location of the last character of the last occurence; in other
words, starting from the right side of a particular string. My
function FINDR("ar","marker marker") would return the value 9.

There are probably several ways to do it, but here's what I have done
so far: First, I wrote a function ReverseString, which reverses all
the characters in the given string. (at the bottom of this posting;
improvements are welcome)

My idea was to use this new function to reverse both the "within_text"
string and the "find_text" strings, and then use the regular Excel
function FIND to search for the reversed "find_text" in the
"within_text" string.

In my example, the Excel FIND function would find the first "ra" in
"rekram rekram", and return the number 4. Then I would then subtract
4 from the length of the original string (13) to get the desired 9.

The problem is, when I call FIND from a VBA function, it does not do
the same thing as the FIND function. Instead, the VBA FIND does
something completely different. It's complicated and I don't really
understand it. Basically its search domain is not limited to a single
string of characters, but instead it "finds" information about the
entire worksheet. This includes fonts, other formatting information,
and other data far beyond the scope that I need.

I know I could recreate the Excel FIND in VBA, but it seems like
re-inventing the wheel.

How can I call the Excel function "FIND" from a function or macro in
Excel VBA?

The function ReverseString below works as desired. The function FINDR
does not.

Thanks for any help.

Steven

' = = = = = = = = =
Function ReverseString(in_text As String) As String
r_s = ""
For i = Len(in_text) To 1 Step -1
r_s = r_s & Mid(in_text, i, 1)
Next
ReverseString = r_s
End Function

Function FINDR(find_text As String, within_text As String) As Long
find_rev = ReverseString(find_text)
within_rev = ReverseString(within_text)

' The following line does NOT WORK
loc = Find(find_rev, within_rev)

FINDR = LEN(within_text) - loc

End Function

' = = = = = = = = =

--
Steven M - lid
(remove wax and invalid to reply)

On a web site of tips on how to use Microsoft Word:
"Word rarely misses an opportunity to perplex"