S
Steven M (remove wax and invalid to reply)
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 - (e-mail address removed)
(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"
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 - (e-mail address removed)
(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"