Calling an Excel function from a VBA macro
 

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"

Calling an Excel function from a VBA macro
 

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"

Calling an Excel function from a VBA macro
 

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

Calling an Excel function from a VBA macro
 

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"