I need check a list of words against an alphabet (a string of letters)
and return the index of the letter in the word that is the highest in
the alphabet.

For example, for the alphabet is "etaoin", these words would return
these indices:

Word Index
to 4
ate 3
ten 6
neat 6
tee 2

Is there some magic Excel function that will do this?

If not, can someone post the guts of a search loop to select each
letter in sWord and find the index in sAlphabet?

The actual alphabet will have all 26 letters and might look like this:

etaoinsrhldcumgfpwybvkjxzq

"Henrietta Horne" wrote in message
...
I need check a list of words against an alphabet (a string of letters)
and return the index of the letter in the word that is the highest in
the alphabet.

For example, for the alphabet is "etaoin", these words would return
these indices:

Word Index
to 4
ate 3
ten 6
neat 6
tee 2

Is there some magic Excel function that will do this?

If not, can someone post the guts of a search loop to select each
letter in sWord and find the index in sAlphabet?

The actual alphabet will have all 26 letters and might look like this:

etaoinsrhldcumgfpwybvkjxzq



Untested "air code"

Function GetIndex(sWord As String, sAlphabet As String) _
As Integer

Dim sChar As String
Dim iIndex As Integer
Dim iLoop As Integer
Dim iTemp As Integer

For iLoop = 1 To Len(sWord)
sChar = Mid(sWord, iLoop)
iTemp = InStr(sChar, sAlphabet, vbTextCompare)
If iTemp iIndex Then
iIndex = iTemp
End If
Next iLoop

GetIndex = iIndex

End Function

HTH!

--
Clif McIrvin

(clare reads his mail with moe, nomail feeds the bit bucket :-)

On Tue, 11 Jan 2011 10:31:47 -0600, "Clif McIrvin"
wrote:

"Henrietta Horne" wrote in message
.. .
I need check a list of words against an alphabet (a string of letters)
and return the index of the letter in the word that is the highest in
the alphabet.

For example, for the alphabet is "etaoin", these words would return
these indices:

Word Index
to 4
ate 3
ten 6
neat 6
tee 2

Is there some magic Excel function that will do this?

If not, can someone post the guts of a search loop to select each
letter in sWord and find the index in sAlphabet?

The actual alphabet will have all 26 letters and might look like this:

etaoinsrhldcumgfpwybvkjxzq



Untested "air code"

Function GetIndex(sWord As String, sAlphabet As String) _
As Integer

Dim sChar As String
Dim iIndex As Integer
Dim iLoop As Integer
Dim iTemp As Integer

For iLoop = 1 To Len(sWord)
sChar = Mid(sWord, iLoop)
iTemp = InStr(sChar, sAlphabet, vbTextCompare)
If iTemp iIndex Then
iIndex = iTemp
End If
Next iLoop

GetIndex = iIndex

End Function

HTH!

Thank you so much. You and Garry had essentially the same solution.
Hugs.

Henrietta Horne explained on 1/11/2011 :
I need check a list of words against an alphabet (a string of letters)
and return the index of the letter in the word that is the highest in
the alphabet.

For example, for the alphabet is "etaoin", these words would return
these indices:

Word Index
to 4
ate 3
ten 6
neat 6
tee 2

Is there some magic Excel function that will do this?

If not, can someone post the guts of a search loop to select each
letter in sWord and find the index in sAlphabet?

The actual alphabet will have all 26 letters and might look like this:

etaoinsrhldcumgfpwybvkjxzq

Try this...

Function GetHighIndex(sWord As String, sAlphabet As String) As Integer
Dim i As Integer, iPos As Integer
For i = 1 To Len(sWord)
iPos = InStr(1, sAlphabet, Mid$(sWord, i, 1), vbTextCompare)
If iPos GetHighIndex Then GetHighIndex = iPos
Next
End Function

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

On Tue, 11 Jan 2011 11:46:14 -0500, GS wrote:

Henrietta Horne explained on 1/11/2011 :
I need check a list of words against an alphabet (a string of letters)
and return the index of the letter in the word that is the highest in
the alphabet.

For example, for the alphabet is "etaoin", these words would return
these indices:

Word Index
to 4
ate 3
ten 6
neat 6
tee 2

Is there some magic Excel function that will do this?

If not, can someone post the guts of a search loop to select each
letter in sWord and find the index in sAlphabet?

The actual alphabet will have all 26 letters and might look like this:

etaoinsrhldcumgfpwybvkjxzq

Try this...

Function GetHighIndex(sWord As String, sAlphabet As String) As Integer
Dim i As Integer, iPos As Integer
For i = 1 To Len(sWord)
iPos = InStr(1, sAlphabet, Mid$(sWord, i, 1), vbTextCompare)
If iPos GetHighIndex Then GetHighIndex = iPos
Next
End Function

Thank you so much. You and Clif had essentially the same solution.
Hugs.

Here is a shorter function that approaches the problem "backwards"...

Function GetIndex(Word As String, Alphabet As String) As Long
For GetIndex = Len(Alphabet) To 1 Step -1
If InStr(1, Word, Mid(Alphabet, GetIndex, _
1), vbTextCompare) Then Exit Function
Next
End Function

Rick Rothstein (MVP - Excel)




"Henrietta Horne" wrote in message
...

I need check a list of words against an alphabet (a string of letters)
and return the index of the letter in the word that is the highest in
the alphabet.

For example, for the alphabet is "etaoin", these words would return
these indices:

Word Index
to 4
ate 3
ten 6
neat 6
tee 2

Is there some magic Excel function that will do this?

If not, can someone post the guts of a search loop to select each
letter in sWord and find the index in sAlphabet?

The actual alphabet will have all 26 letters and might look like this:

etaoinsrhldcumgfpwybvkjxzq

On Tue, 11 Jan 2011 12:35:36 -0500, "Rick Rothstein"
wrote:

Here is a shorter function that approaches the problem "backwards"...

Function GetIndex(Word As String, Alphabet As String) As Long
For GetIndex = Len(Alphabet) To 1 Step -1
If InStr(1, Word, Mid(Alphabet, GetIndex, _
1), vbTextCompare) Then Exit Function
Next
End Function

Rick Rothstein (MVP - Excel)

Clever solution. Instead of comparing each letter in the word to the
alphabet, you are comparing the alphabet to the work backwards and
stopping as soon as you get one match.

I wonder which is actually faster (for my data)? My alphabet is 26
letters. Most of my words are less than 5-6 letters.

On Tue, 11 Jan 2011 08:15:48 -0800, Henrietta Horne
wrote:

I need check a list of words against an alphabet (a string of letters)
and return the index of the letter in the word that is the highest in
the alphabet.

For example, for the alphabet is "etaoin", these words would return
these indices:

Word Index
to 4
ate 3
ten 6
neat 6
tee 2

Is there some magic Excel function that will do this?

If not, can someone post the guts of a search loop to select each
letter in sWord and find the index in sAlphabet?

The actual alphabet will have all 26 letters and might look like this:

etaoinsrhldcumgfpwybvkjxzq

When I ran the function against the actual data, I quickly discovered
that some words have characters (like "-" and "'"), that are not in
the alphabet. I decided I want those letters treated as if they were
at the end of the alphabet.

The modified function is now:


Function GetHighIndex(sWord As String, sAlphabet As String) As Integer

Dim i As Integer 'Loop index
Dim iPos As Integer 'Index variable
Dim iLenA As Integer 'Length of alphabet
iLenA = Len(sAlphabet) '.For index for missing characters

For i = 1 To Len(sWord)
iPos = InStr(1, sAlphabet, Mid$(sWord, i, 1), vbTextCompare)
If iPos = 0 Then iPos = iLenA + 1
If iPos GetHighIndex Then GetHighIndex = iPos
Next i

End Function

Comments?

The actual alphabet will have all 26 letters and might look like this:

etaoinsrhldcumgfpwybvkjxzq

Will it **always** have the 26 letters of the alphabet in it? Also,
important for speeding things up a little, will the letters **always** be
lower case?

When I ran the function against the actual data, I quickly
discovered that some words have characters (like "-" and "'"),
that are not in the alphabet. I decided I want those letters
treated as if they were at the end of the alphabet.

Are those two characters the **only** non-letter characters that can be in
your word? If not, are you willing to have **any** non-letter return 27
(assuming you "alphabet" always contains 26 letters)?

Rick Rothstein (MVP - Excel)

On Tue, 11 Jan 2011 14:03:19 -0500, "Rick Rothstein"
wrote:

The actual alphabet will have all 26 letters and might look like this:

etaoinsrhldcumgfpwybvkjxzq

Will it **always** have the 26 letters of the alphabet in it?

For this application, yes.

Also,
important for speeding things up a little, will the letters **always** be
lower case?

In the application that will use this data, the words will be in
sentences, so the first word and proper names will be uppercase. But
for this UDF, all lower case.

When I ran the function against the actual data, I quickly
discovered that some words have characters (like "-" and "'"),
that are not in the alphabet. I decided I want those letters
treated as if they were at the end of the alphabet.

Are those two characters the **only** non-letter characters that can be in
your word? If not, are you willing to have **any** non-letter return 27
(assuming you "alphabet" always contains 26 letters)?

There can be others, but for now, I'm happy to have all of them return
a "27".

Larger alphabets and/or shorter words benefit the forward algorithm,
as far as compute time, no?

It happens that Henrietta Horne formulated :
On Tue, 11 Jan 2011 14:03:19 -0500, "Rick Rothstein"
wrote:

The actual alphabet will have all 26 letters and might look like this:

etaoinsrhldcumgfpwybvkjxzq

Will it **always** have the 26 letters of the alphabet in it?

For this application, yes.

Also,
important for speeding things up a little, will the letters **always** be
lower case?

In the application that will use this data, the words will be in
sentences, so the first word and proper names will be uppercase. But
for this UDF, all lower case.

When I ran the function against the actual data, I quickly
discovered that some words have characters (like "-" and "'"),
that are not in the alphabet. I decided I want those letters
treated as if they were at the end of the alphabet.

Are those two characters the **only** non-letter characters that can be in
your word? If not, are you willing to have **any** non-letter return 27
(assuming you "alphabet" always contains 26 letters)?

There can be others, but for now, I'm happy to have all of them return
a "27".

Larger alphabets and/or shorter words benefit the forward algorithm,
as far as compute time, no?

So does using fewer variables!<g

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

See inline comments...

In the application that will use this data, the words will
be in sentences, so the first word and proper names will
be uppercase. But for this UDF, all lower case.

No sense giving you different code for this "test" case when your "real"
case will be different.

There can be others, but for now, I'm happy to have all
of them return "27".

Again, it make no sense not designing for you final application.

Larger alphabets and/or shorter words benefit the forward
algorithm, as far as compute time, no?

For such short text strings, for a single call of the function, you would be
hard pressed to measure the difference. If you were examining hundreds of
thousands of words in a loop, then maybe the difference would start to
become noticeable enough to choose one method over the other.

Okay, with all that said, here is my code, modified to report one more than
the length of your alphabet for non-letters...

Function GetIndex(Word As String, Alphabet As String) As Long
If Word Like "*[!" & Alphabet & "]*" Then
GetIndex = Len(Alphabet) + 1
Else
For GetIndex = Len(Alphabet) To 1 Step -1
If InStr(Word, Mid(Alphabet, GetIndex, 1)) Then Exit For
Next
End If
End Function

Rick Rothstein (MVP - Excel)

On Tue, 11 Jan 2011 15:02:43 -0500, "Rick Rothstein"
wrote:

See inline comments...

In the application that will use this data, the words will
be in sentences, so the first word and proper names will
be uppercase. But for this UDF, all lower case.

No sense giving you different code for this "test" case when your "real"
case will be different.

There can be others, but for now, I'm happy to have all
of them return "27".

Again, it make no sense not designing for you final application.

This is my final data for the first step of the project. The results
will be used in the second step where the data will be much larger.

Larger alphabets and/or shorter words benefit the forward
algorithm, as far as compute time, no?

For such short text strings, for a single call of the function, you would be
hard pressed to measure the difference. If you were examining hundreds of
thousands of words in a loop, then maybe the difference would start to
become noticeable enough to choose one method over the other.

It will be called at least 60,000 times, but I just tested it with
that data and my version only takes a couple of seconds.

I realize that any difference would be minor, but don't we always want
to write the best code we can? ;-)

Okay, with all that said, here is my code, modified to report one more than
the length of your alphabet for non-letters...

Function GetIndex(Word As String, Alphabet As String) As Long
If Word Like "*[!" & Alphabet & "]*" Then
GetIndex = Len(Alphabet) + 1
Else
For GetIndex = Len(Alphabet) To 1 Step -1
If InStr(Word, Mid(Alphabet, GetIndex, 1)) Then Exit For
Next
End If
End Function

I'll have to study that one. Thanks.

Henrietta Horne submitted this idea :
On Tue, 11 Jan 2011 08:15:48 -0800, Henrietta Horne
wrote:

I need check a list of words against an alphabet (a string of letters)
and return the index of the letter in the word that is the highest in
the alphabet.

For example, for the alphabet is "etaoin", these words would return
these indices:

Word Index
to 4
ate 3
ten 6
neat 6
tee 2

Is there some magic Excel function that will do this?

If not, can someone post the guts of a search loop to select each
letter in sWord and find the index in sAlphabet?

The actual alphabet will have all 26 letters and might look like this:

etaoinsrhldcumgfpwybvkjxzq

When I ran the function against the actual data, I quickly discovered
that some words have characters (like "-" and "'"), that are not in
the alphabet. I decided I want those letters treated as if they were
at the end of the alphabet.

The modified function is now:


Function GetHighIndex(sWord As String, sAlphabet As String) As Integer

Dim i As Integer 'Loop index
Dim iPos As Integer 'Index variable
Dim iLenA As Integer 'Length of alphabet
iLenA = Len(sAlphabet) '.For index for missing characters

For i = 1 To Len(sWord)
iPos = InStr(1, sAlphabet, Mid$(sWord, i, 1), vbTextCompare)
If iPos = 0 Then iPos = iLenA + 1
If iPos GetHighIndex Then GetHighIndex = iPos
Next i

End Function

Comments?

So then, you might want to exit the function if the character is not in
sAlphabet to skip further looping:

Function GetHighIndex(sWord As String, sAlphabet As String) As Integer
Dim i As Integer, iPos As Integer
For i = 1 To Len(sWord)
iPos = InStr(1, LCase$(sAlphabet), Mid$(LCase$(sWord), i, 1), 1)
If iPos = 0 Then GetHighIndex = Len(sAlphabet) + 1: Exit Function
If iPos GetHighIndex Then GetHighIndex = iPos
Next
End Function

I also had the same concern as Rick regarding case and so note that I
forced lowercase.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

"GS" wrote in message
...
Henrietta Horne submitted this idea :
On Tue, 11 Jan 2011 08:15:48 -0800, Henrietta Horne
wrote:

<snip

So then, you might want to exit the function if the character is not
in sAlphabet to skip further looping:

Excellent point!


Function GetHighIndex(sWord As String, sAlphabet As String) As Integer
Dim i As Integer, iPos As Integer
For i = 1 To Len(sWord)
iPos = InStr(1, LCase$(sAlphabet), Mid$(LCase$(sWord), i, 1), 1)
If iPos = 0 Then GetHighIndex = Len(sAlphabet) + 1: Exit Function
If iPos GetHighIndex Then GetHighIndex = iPos
Next
End Function

I also had the same concern as Rick regarding case and so note that I
forced lowercase.

I was under the impression that is what using vbTextCompare
accomplished. Am I mistaken?

I note that you are using the "$" variation of the function names
(LCase$(), Mid$()) ... is there a speed difference vs. using LCase() and
Mid()?

I understand that using variants is slower than typed variables; I typed
the function parameters as variant in my original suggestion to allow
the possibility of testing for Empty, though I didn't include that in my
code.


--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc





--
Clif McIrvin

(clare reads his mail with moe, nomail feeds the bit bucket :-)

Clif McIrvin used his keyboard to write :
"GS" wrote in message
...
Henrietta Horne submitted this idea :
On Tue, 11 Jan 2011 08:15:48 -0800, Henrietta Horne
wrote:

<snip

So then, you might want to exit the function if the character is not in
sAlphabet to skip further looping:

Excellent point!


Function GetHighIndex(sWord As String, sAlphabet As String) As Integer
Dim i As Integer, iPos As Integer
For i = 1 To Len(sWord)
iPos = InStr(1, LCase$(sAlphabet), Mid$(LCase$(sWord), i, 1), 1)
If iPos = 0 Then GetHighIndex = Len(sAlphabet) + 1: Exit Function
If iPos GetHighIndex Then GetHighIndex = iPos
Next
End Function

I also had the same concern as Rick regarding case and so note that I
forced lowercase.

I was under the impression that is what using vbTextCompare accomplished. Am
I mistaken?

No, you're not mistaken. If I read Rick's context correctly, I believe
he was refering to the typical issue of users having the Caps Lock on
inadvertently OR without giving it any thought. IMO, I'd use a filter
function to determine any chars not in my expected string, and strip
them out. But this is not what fits the OP's task.

I note that you are using the "$" variation of the function names (LCase$(),
Mid$()) ... is there a speed difference vs. using LCase() and Mid()?

I read somewhere that it has an effect. Not sure of the details
exactly, but I started using the $ after reading the article.

I understand that using variants is slower than typed variables; I typed the
function parameters as variant in my original suggestion to allow the
possibility of testing for Empty, though I didn't include that in my code.

And this is why my loop UBounds to Len(sWord). If it's an empty string
there's nothing to do, and the function immediately exits and returns
'0'. I think that clearly indicates a zero length string.
CBool(Len("")=0) returns TRUE. (This makes a good arg for
documentation!<g)

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

"GS" wrote in message
...
Clif McIrvin used his keyboard to write :
"GS" wrote in message
...
Henrietta Horne submitted this idea :
On Tue, 11 Jan 2011 08:15:48 -0800, Henrietta Horne
wrote:

<snip

I note that you are using the "$" variation of the function names
(LCase$(), Mid$()) ... is there a speed difference vs. using LCase()
and Mid()?

I read somewhere that it has an effect. Not sure of the details
exactly, but I started using the $ after reading the article.

I seem to recall reading somewhere that the "sans-$" routines include
code to handle implicit type conversions ... which would be a good
argument for using the $ when you can <g.


I understand that using variants is slower than typed variables; I
typed the function parameters as variant in my original suggestion to
allow the possibility of testing for Empty, though I didn't include
that in my code.

And this is why my loop UBounds to Len(sWord). If it's an empty string
there's nothing to do, and the function immediately exits and returns
'0'. I think that clearly indicates a zero length string.
CBool(Len("")=0) returns TRUE. (This makes a good arg for
documentation!<g)

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc



This converstaion is *way* too much fun ... I'd better get back to work
while there's some day left <grin.


--
Clif McIrvin

(clare reads his mail with moe, nomail feeds the bit bucket :-)

On Tue, 11 Jan 2011 14:34:38 -0500, GS wrote:

Henrietta Horne submitted this idea :
On Tue, 11 Jan 2011 08:15:48 -0800, Henrietta Horne
wrote:

I need check a list of words against an alphabet (a string of letters)
and return the index of the letter in the word that is the highest in
the alphabet.

For example, for the alphabet is "etaoin", these words would return
these indices:

Word Index
to 4
ate 3
ten 6
neat 6
tee 2

Is there some magic Excel function that will do this?

If not, can someone post the guts of a search loop to select each
letter in sWord and find the index in sAlphabet?

The actual alphabet will have all 26 letters and might look like this:

etaoinsrhldcumgfpwybvkjxzq

When I ran the function against the actual data, I quickly discovered
that some words have characters (like "-" and "'"), that are not in
the alphabet. I decided I want those letters treated as if they were
at the end of the alphabet.

The modified function is now:


Function GetHighIndex(sWord As String, sAlphabet As String) As Integer

Dim i As Integer 'Loop index
Dim iPos As Integer 'Index variable
Dim iLenA As Integer 'Length of alphabet
iLenA = Len(sAlphabet) '.For index for missing characters

For i = 1 To Len(sWord)
iPos = InStr(1, sAlphabet, Mid$(sWord, i, 1), vbTextCompare)
If iPos = 0 Then iPos = iLenA + 1
If iPos GetHighIndex Then GetHighIndex = iPos
Next i

End Function

Comments?

So then, you might want to exit the function if the character is not in
sAlphabet to skip further looping:

Function GetHighIndex(sWord As String, sAlphabet As String) As Integer
Dim i As Integer, iPos As Integer
For i = 1 To Len(sWord)
iPos = InStr(1, LCase$(sAlphabet), Mid$(LCase$(sWord), i, 1), 1)
If iPos = 0 Then GetHighIndex = Len(sAlphabet) + 1: Exit Function
If iPos GetHighIndex Then GetHighIndex = iPos
Next
End Function

I also had the same concern as Rick regarding case and so note that I
forced lowercase.

Thanks. My version is now:



Function GetHighIndex(sWord As String, sAlphabet As String) As Integer

Dim i As Integer 'Loop index
Dim iPos As Integer 'Index variable
Dim iPosNF As Integer 'Index for characters not found
iPosNF = Len(sAlphabet) + 1 '.Use 1 + max index

For i = 1 To Len(sWord) '
iPos = InStr(1, sAlphabet, Mid$(sWord, i, 1), vbTextCompare)
If iPos = 0 Then GetHighIndex = iPosNF: Exit For
If iPos GetHighIndex Then GetHighIndex = iPos
Next i

End Function


Thanks for the help

"Henrietta Horne" wrote in message
...
On Tue, 11 Jan 2011 08:15:48 -0800, Henrietta Horne
wrote:

I need check a list of words against an alphabet (a string of letters)
and return the index of the letter in the word that is the highest in
the alphabet.

For example, for the alphabet is "etaoin", these words would return
these indices:

Word Index
to 4
ate 3
ten 6
neat 6
tee 2

Is there some magic Excel function that will do this?

If not, can someone post the guts of a search loop to select each
letter in sWord and find the index in sAlphabet?

The actual alphabet will have all 26 letters and might look like this:

etaoinsrhldcumgfpwybvkjxzq

When I ran the function against the actual data, I quickly discovered
that some words have characters (like "-" and "'"), that are not in
the alphabet. I decided I want those letters treated as if they were
at the end of the alphabet.

The modified function is now:


Function GetHighIndex(sWord As String, sAlphabet As String) As Integer

Dim i As Integer 'Loop index
Dim iPos As Integer 'Index variable
Dim iLenA As Integer 'Length of alphabet
iLenA = Len(sAlphabet) '.For index for missing characters

For i = 1 To Len(sWord)
iPos = InStr(1, sAlphabet, Mid$(sWord, i, 1), vbTextCompare)
If iPos = 0 Then iPos = iLenA + 1
If iPos GetHighIndex Then GetHighIndex = iPos
Next i

End Function

Comments?


I'm *guessing* that the time difference between Rick's function and ours
would be measured in micro-seconds ... if you're calling it millions of
times it "might" be worth investigating <grin.

(BTW, IMO the main difference between Garry's offering and mine is "self
documentation" and ease of debugging.)

As long as your rule is valid, it seems that your solution covers all
"extraneous" characters. To cover that issue with Rick's approach would
require including all possible characters in sAlphabet ... which would
wipe out the elegance (and hurt execution time, as well), but it would
allow a variety of rules.

--
Clif McIrvin

(clare reads his mail with moe, nomail feeds the bit bucket :-)

Clif McIrvin submitted this idea :
(BTW, IMO the main difference between Garry's offering and mine is "self
documentation" and ease of debugging.)

Hi Clif,
I don't see where my offering's brevity poses any challenges for
debugging. I'll give you that yours is more "self documented", as you
put it, but at what cost? IMO, the less var tracking I have to do the
easier my code is to understand AND debug.

Not saying that there's anything wrong with your offering 'as is'. I
just like brevity<g, especially Rick's one-liners<bg!

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

"GS" wrote in message
...
Clif McIrvin submitted this idea :
(BTW, IMO the main difference between Garry's offering and mine is
"self documentation" and ease of debugging.)

Hi Clif,
I don't see where my offering's brevity poses any challenges for
debugging. I'll give you that yours is more "self documented", as you
put it, but at what cost? IMO, the less var tracking I have to do the
easier my code is to understand AND debug.

Not saying that there's anything wrong with your offering 'as is'. I
just like brevity<g, especially Rick's one-liners<bg!

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc




I'm a part-time developer, and I've learned through harsh experience
that the more documentation in the code the better! If I was coding
every day, then yes, I'd agree with your observation.

I agree that there is some difference in execution time -- and sometimes
that would even be relevant! -- but in my single user environment that
has not (so far) been an issue.

I learn from both of you guys (and others!) and I agree ... Rick's
solutions often make me back up and say something like, "I sure wish I'd
thought of that!"

--
Clif McIrvin

(clare reads his mail with moe, nomail feeds the bit bucket :-)

Clif McIrvin brought next idea :
"GS" wrote in message
...
Clif McIrvin submitted this idea :
(BTW, IMO the main difference between Garry's offering and mine is "self
documentation" and ease of debugging.)

Hi Clif,
I don't see where my offering's brevity poses any challenges for debugging.
I'll give you that yours is more "self documented", as you put it, but at
what cost? IMO, the less var tracking I have to do the easier my code is to
understand AND debug.

Not saying that there's anything wrong with your offering 'as is'. I just
like brevity<g, especially Rick's one-liners<bg!

-- Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc




I'm a part-time developer, and I've learned through harsh experience that the
more documentation in the code the better! If I was coding every day, then
yes, I'd agree with your observation.

Well, you're right regardless of what my preference is. Especially if
your familiarity with a project is infrequent. I'm also a part-time
programmer. What might be more of a difference is how I view my code as
being adequately self documenting while others may not find it so. In
cases where I think it might not be doc'd adequately I insert comments.
One thing for sure is that any code that uses lots of vars is
dauntingly hard to get a grasp of. Thus, I use vars sparingly when
possible.


I agree that there is some difference in execution time -- and sometimes that
would even be relevant! -- but in my single user environment that has not (so
far) been an issue.

Yeah, if only we all did this for our own use!<g


I learn from both of you guys (and others!) and I agree ... Rick's solutions
often make me back up and say something like, "I sure wish I'd thought of
that!"

What's nice is that you give back. That's what I try to do as
reciprocal for the learning I've gained from so many. It's definitely a
"give-n-take" environment!

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

As long as your rule is valid, it seems that your solution
covers all "extraneous" characters. To cover that issue
with Rick's approach would require including all possible
characters in sAlphabet ... which would wipe out the
elegance (and hurt execution time, as well), but it would allow a variety
of rules.

See my latest response to the OP for a still quick, and what I consider a
still "elegant" solution, to covering the non-letter characters.

Rick Rothstein (MVP - Excel)

"Rick Rothstein" wrote in message
...
As long as your rule is valid, it seems that your solution
covers all "extraneous" characters. To cover that issue
with Rick's approach would require including all possible
characters in sAlphabet ... which would wipe out the
elegance (and hurt execution time, as well), but it would allow a
variety of rules.

See my latest response to the OP for a still quick, and what I
consider a still "elegant" solution, to covering the non-letter
characters.

Rick Rothstein (MVP - Excel)


Elegant, for sure!

Reading your post after replying to Garry regarding brevity, I recall a
tale I heard many years ago about assembler code that had been running
flawlessly for years which suddenly died. The programmer no longer
worked there, and the team was stumped when they came to a dead-end in
the source code-- there was no instruction to execute! How could the
code have ever worked in the first place?!

Eventually someone realized that with the combination of processor speed
and rotation of the "swap drum" the code was running on the missing
instruction was just coming up under the read head at exactly the right
time, and the program broke when the swap drum was replaced with a head
per track disk.

The development environment has certainly changed since those days!

[BTW: I don't believe I've ever used LIKE in VBA ... I'll have to
remember that one!]

--
Clif McIrvin

(clare reads his mail with moe, nomail feeds the bit bucket :-)

See inline comments...

Reading your post after replying to Garry regarding brevity,
I recall a tale I heard many years ago...
<snipped

Cute story... is it really true though?

The development environment has certainly changed since those days!

I remember coding Fortran programs on those old IBM cards... what a pain
that was. We were is a satellite office, so we had to send our decks of
cards out to the central office where the computer was housed. One simple,
small mistake in typing any one of the cards caused the program to crash, of
course, but trying to find that one mistake in a stack of hundreds of
cards... well, let me tell you, that was a lot of fun... NOT! Yes, the
development environment has definitely changed. I do remember thinking I was
in heaven when we got remote dumb terminals where we could type-in and edit
our programs ourselves... "instantly", mind you, across our blazing fast 300
baud telephone connection to the main frame... ah yes, that was heaven
indeed.<g

[BTW: I don't believe I've ever used LIKE in VBA ...
I'll have to remember that one!]

I like Like but I do not like Like's name as it makes sentences like this
one confusing to read.<g Like is like a "poor cousin" to a Regular
Expression parser, but even with its limited parsing abilities, it still
allows you to do some fairly powerful text parsing. Here is a link to blog
article on John Walkenbach's website where he acknowledges a simplification
I sent him to reduce a 12-line function he posted down to a one-liner using
the Like operator...

http://spreadsheetpage.com/index.php...ng_a_function/

Just follow the link in the blog article (after reading the very nice
acknowledgement he gave me) to see his code and then mine.

Rick Rothstein (MVP - Excel)

On Tue, 11 Jan 2011 08:15:48 -0800, Henrietta Horne wrote:

I need check a list of words against an alphabet (a string of letters)
and return the index of the letter in the word that is the highest in
the alphabet.

For example, for the alphabet is "etaoin", these words would return
these indices:

Word Index
to 4
ate 3
ten 6
neat 6
tee 2

Is there some magic Excel function that will do this?

If not, can someone post the guts of a search loop to select each
letter in sWord and find the index in sAlphabet?

The actual alphabet will have all 26 letters and might look like this:

etaoinsrhldcumgfpwybvkjxzq

Everybody has given you UDF's.

Here is an Excel function which will work so long as Alphabet contains **all** of the characters that might be in any word:

Alphabet is the NAME of a cell that contains your alphabet, and any other characters that might be in a word. You should put the non-letters at the end of Alphabet.

eg: NAME some cell Alphabet
enter: etaoinsrhldcumgfpwybvkjxzq'/?


Then, with your word in A1,

This formula must be **array-entered**:

=MAX(SEARCH(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1), Alphabet))

I note in one of your other posts that, if you have a non-alphabetic character, you want to return a 27. That being the case, you could use:

=MIN(27,MAX(SEARCH(MID(A1,ROW(INDIRECT("1:"&LEN(A1 ))),1),Alphabet)))

----------------------------------------

To **array-enter** a formula, after entering
the formula into the cell or formula bar, hold down
<ctrl<shift while hitting <enter. If you did this
correctly, Excel will place braces {...} around the formula.

On Tue, 11 Jan 2011 20:18:51 -0500, Ron Rosenfeld
wrote:

On Tue, 11 Jan 2011 08:15:48 -0800, Henrietta Horne wrote:

I need check a list of words against an alphabet (a string of letters)
and return the index of the letter in the word that is the highest in
the alphabet.

For example, for the alphabet is "etaoin", these words would return
these indices:

Word Index
to 4
ate 3
ten 6
neat 6
tee 2

Is there some magic Excel function that will do this?

If not, can someone post the guts of a search loop to select each
letter in sWord and find the index in sAlphabet?

The actual alphabet will have all 26 letters and might look like this:

etaoinsrhldcumgfpwybvkjxzq

Everybody has given you UDF's.

Here is an Excel function which will work so long as Alphabet contains **all** of the characters that might be in any word:

Alphabet is the NAME of a cell that contains your alphabet, and any other characters that might be in a word. You should put the non-letters at the end of Alphabet.

eg: NAME some cell Alphabet
enter: etaoinsrhldcumgfpwybvkjxzq'/?


Then, with your word in A1,

This formula must be **array-entered**:

=MAX(SEARCH(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1) ,Alphabet))

I note in one of your other posts that, if you have a non-alphabetic character, you want to return a 27. That being the case, you could use:

=MIN(27,MAX(SEARCH(MID(A1,ROW(INDIRECT("1:"&LEN(A 1))),1),Alphabet)))

----------------------------------------

To **array-enter** a formula, after entering
the formula into the cell or formula bar, hold down
<ctrl<shift while hitting <enter. If you did this
correctly, Excel will place braces {...} around the formula.

I've trieds array formulas once or twice and got confused. I've got my
function working now, but thanks for the suggestion. I like the UDF
because I can add coded to do other things and I don't have to type a
complicated formula in a cell.

"Henrietta Horne" wrote in message
...
On Tue, 11 Jan 2011 20:18:51 -0500, Ron Rosenfeld
wrote:

On Tue, 11 Jan 2011 08:15:48 -0800, Henrietta Horne
wrote:

I need check a list of words against an alphabet (a string of
letters)
and return the index of the letter in the word that is the highest in
the alphabet.

For example, for the alphabet is "etaoin", these words would return
these indices:

Word Index
to 4
ate 3
ten 6
neat 6
tee 2

Is there some magic Excel function that will do this?

If not, can someone post the guts of a search loop to select each
letter in sWord and find the index in sAlphabet?

The actual alphabet will have all 26 letters and might look like
this:

etaoinsrhldcumgfpwybvkjxzq

Everybody has given you UDF's.

Here is an Excel function which will work so long as Alphabet contains
**all** of the characters that might be in any word:

Alphabet is the NAME of a cell that contains your alphabet, and any
other characters that might be in a word. You should put the
non-letters at the end of Alphabet.

eg: NAME some cell Alphabet
enter: etaoinsrhldcumgfpwybvkjxzq'/?


Then, with your word in A1,

This formula must be **array-entered**:

=MAX(SEARCH(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1 ),Alphabet))

I note in one of your other posts that, if you have a non-alphabetic
character, you want to return a 27. That being the case, you could
use:

=MIN(27,MAX(SEARCH(MID(A1,ROW(INDIRECT("1:"&LEN( A1))),1),Alphabet)))

----------------------------------------

To **array-enter** a formula, after entering
the formula into the cell or formula bar, hold down
<ctrl<shift while hitting <enter. If you did this
correctly, Excel will place braces {...} around the formula.

I've trieds array formulas once or twice and got confused. I've got my
function working now, but thanks for the suggestion. I like the UDF
because I can add coded to do other things and I don't have to type a
complicated formula in a cell.


Are you familiar with the formula auditing tools? I built a simple
worksheet with Ron's formula, then stepped through it with the "Evaluate
Formula" tool so I could understand what makes it work. Pretty neat!

--
Clif McIrvin

(clare reads his mail with moe, nomail feeds the bit bucket :-)

On Thu, 13 Jan 2011 09:53:10 -0600, "Clif McIrvin"
wrote:

"Henrietta Horne" wrote in message
.. .
On Tue, 11 Jan 2011 20:18:51 -0500, Ron Rosenfeld
wrote:

On Tue, 11 Jan 2011 08:15:48 -0800, Henrietta Horne
wrote:

I need check a list of words against an alphabet (a string of
letters)
and return the index of the letter in the word that is the highest in
the alphabet.

For example, for the alphabet is "etaoin", these words would return
these indices:

Word Index
to 4
ate 3
ten 6
neat 6
tee 2

Is there some magic Excel function that will do this?

If not, can someone post the guts of a search loop to select each
letter in sWord and find the index in sAlphabet?

The actual alphabet will have all 26 letters and might look like
this:

etaoinsrhldcumgfpwybvkjxzq

Everybody has given you UDF's.

Here is an Excel function which will work so long as Alphabet contains
**all** of the characters that might be in any word:

Alphabet is the NAME of a cell that contains your alphabet, and any
other characters that might be in a word. You should put the
non-letters at the end of Alphabet.

eg: NAME some cell Alphabet
enter: etaoinsrhldcumgfpwybvkjxzq'/?


Then, with your word in A1,

This formula must be **array-entered**:

=MAX(SEARCH(MID(A1,ROW(INDIRECT("1:"&LEN(A1))), 1),Alphabet))

I note in one of your other posts that, if you have a non-alphabetic
character, you want to return a 27. That being the case, you could
use:

=MIN(27,MAX(SEARCH(MID(A1,ROW(INDIRECT("1:"&LEN (A1))),1),Alphabet)))

----------------------------------------

To **array-enter** a formula, after entering
the formula into the cell or formula bar, hold down
<ctrl<shift while hitting <enter. If you did this
correctly, Excel will place braces {...} around the formula.

I've trieds array formulas once or twice and got confused. I've got my
function working now, but thanks for the suggestion. I like the UDF
because I can add coded to do other things and I don't have to type a
complicated formula in a cell.


Are you familiar with the formula auditing tools? I built a simple
worksheet with Ron's formula, then stepped through it with the "Evaluate
Formula" tool so I could understand what makes it work. Pretty neat!

No, I'm not. I'll add it to my todo list.

That's the problem. There are so many interesting features to learn
and they are such a temptation for procrastinating what I really nood
to do, which is get the project done. ;-)

"Henrietta Horne" wrote in message
...
On Thu, 13 Jan 2011 09:53:10 -0600, "Clif McIrvin"
wrote:

<...
Are you familiar with the formula auditing tools? I built a simple
worksheet with Ron's formula, then stepped through it with the
"Evaluate
Formula" tool so I could understand what makes it work. Pretty neat!

No, I'm not. I'll add it to my todo list.

That's the problem. There are so many interesting features to learn
and they are such a temptation for procrastinating what I really nood
to do, which is get the project done. ;-)


Been there. Almost every day, in fact <g.

Don't be afraid to slap together some ugly code that works - how
important it is that you ever get back to refine it depends on your
circumstances. If that macro is going out to 500 users in the
organization, then you'd better find the time to make it pretty
bullet-proof and self explanatory. If you're the only one that's ever
going to use it, it can stay that way forever - at least until the need
comes up to modify the rules, at which time you can apply the knowledge
you've gained in the interim to improve it. At least, that's the way I
approach it here <grin.


--
Clif McIrvin

(clare reads his mail with moe, nomail feeds the bit bucket :-)

On Wed, 12 Jan 2011 20:35:34 -0800, Henrietta Horne wrote:

I've trieds array formulas once or twice and got confused. I've got my
function working now, but thanks for the suggestion. I like the UDF
because I can add coded to do other things and I don't have to type a
complicated formula in a cell.

No question but that UDF's can be more flexible and, depending on how they are written, easier to understand.

But some advantages of worksheet functions include that they are usually much faster; and, if you are distributing your worksheet, you don't have to worry about policies that restrict the use of VBA Macros and/or UDF's.

On Thu, 13 Jan 2011 12:49:45 -0500, Ron Rosenfeld
wrote:

On Wed, 12 Jan 2011 20:35:34 -0800, Henrietta Horne wrote:

I've trieds array formulas once or twice and got confused. I've got my
function working now, but thanks for the suggestion. I like the UDF
because I can add coded to do other things and I don't have to type a
complicated formula in a cell.

No question but that UDF's can be more flexible and, depending on how they are written, easier to understand.

But some advantages of worksheet functions include that they are usually much faster; and, if you are distributing your worksheet, you don't have to worry about policies that restrict the use of VBA Macros and/or UDF's.

Good point.

This formula array entered (Ctrl+Shift+Enter) works without any VBA.

=MAX((TRANSPOSE(MID($A$18,ROW($A$1:INDEX(A:A,LEN($ A$18))),1))=MID($C5,ROW($A$1:INDEX(A:A,LEN($C5))), 1))*TRANSPOSE(ROW($A$1:INDEX(A:A,LEN($A$18)))))

When I entered this the word was in C5 and the alphabet string was in A18.

:)

On Tuesday, January 11, 2011 11:15 AM Henrietta Horne wrote:

I need check a list of words against an alphabet (a string of letters)
and return the index of the letter in the word that is the highest in
the alphabet.

For example, for the alphabet is "etaoin", these words would return
these indices:

Word Index
to 4
ate 3
ten 6
neat 6
tee 2

Is there some magic Excel function that will do this?

If not, can someone post the guts of a search loop to select each
letter in sWord and find the index in sAlphabet?

The actual alphabet will have all 26 letters and might look like this:

etaoinsrhldcumgfpwybvkjxzq


On Tuesday, January 11, 2011 11:31 AM Clif McIrvin wrote:

Untested "air code"

Function GetIndex(sWord As String, sAlphabet As String) _
As Integer

Dim sChar As String
Dim iIndex As Integer
Dim iLoop As Integer
Dim iTemp As Integer

For iLoop = 1 To Len(sWord)
sChar = Mid(sWord, iLoop)
iTemp = InStr(sChar, sAlphabet, vbTextCompare)
If iTemp iIndex Then
iIndex = iTemp
End If
Next iLoop

GetIndex = iIndex

End Function

HTH!

--
Clif McIrvin

(clare reads his mail with moe, nomail feeds the bit bucket :-)


On Tuesday, January 11, 2011 11:46 AM GS wrote:

Henrietta Horne explained on 1/11/2011 :

Try this...

Function GetHighIndex(sWord As String, sAlphabet As String) As Integer
Dim i As Integer, iPos As Integer
For i = 1 To Len(sWord)
iPos = InStr(1, sAlphabet, Mid$(sWord, i, 1), vbTextCompare)
If iPos GetHighIndex Then GetHighIndex = iPos
Next
End Function

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc


On Tuesday, January 11, 2011 12:12 PM Henrietta Horne wrote:

Thank you so much. You and Garry had essentially the same solution.
Hugs.


On Tuesday, January 11, 2011 12:12 PM Henrietta Horne wrote:

Thank you so much. You and Clif had essentially the same solution.
Hugs.


On Tuesday, January 11, 2011 12:35 PM Rick Rothstein wrote:

Here is a shorter function that approaches the problem "backwards"...

Function GetIndex(Word As String, Alphabet As String) As Long
For GetIndex = Len(Alphabet) To 1 Step -1
If InStr(1, Word, Mid(Alphabet, GetIndex, _
1), vbTextCompare) Then Exit Function
Next
End Function

Rick Rothstein (MVP - Excel)




"Henrietta Horne" wrote in message

I need check a list of words against an alphabet (a string of letters)
and return the index of the letter in the word that is the highest in
the alphabet.

For example, for the alphabet is "etaoin", these words would return
these indices:

Word Index
to 4
ate 3
ten 6
neat 6
tee 2

Is there some magic Excel function that will do this?

If not, can someone post the guts of a search loop to select each
letter in sWord and find the index in sAlphabet?

The actual alphabet will have all 26 letters and might look like this:

etaoinsrhldcumgfpwybvkjxzq


On Tuesday, January 11, 2011 1:07 PM Henrietta Horne wrote:

When I ran the function against the actual data, I quickly discovered
that some words have characters (like "-" and "'"), that are not in
the alphabet. I decided I want those letters treated as if they were
at the end of the alphabet.

The modified function is now:


Function GetHighIndex(sWord As String, sAlphabet As String) As Integer

Dim i As Integer 'Loop index
Dim iPos As Integer 'Index variable
Dim iLenA As Integer 'Length of alphabet
iLenA = Len(sAlphabet) '.For index for missing characters

For i = 1 To Len(sWord)
iPos = InStr(1, sAlphabet, Mid$(sWord, i, 1), vbTextCompare)
If iPos = 0 Then iPos = iLenA + 1
If iPos GetHighIndex Then GetHighIndex = iPos
Next i

End Function

Comments?


On Tuesday, January 11, 2011 1:15 PM Henrietta Horne wrote:

Clever solution. Instead of comparing each letter in the word to the
alphabet, you are comparing the alphabet to the work backwards and
stopping as soon as you get one match.

I wonder which is actually faster (for my data)? My alphabet is 26
letters. Most of my words are less than 5-6 letters.


On Tuesday, January 11, 2011 2:03 PM Rick Rothstein wrote:

Will it **always** have the 26 letters of the alphabet in it? Also,
important for speeding things up a little, will the letters **always** be
lower case?


Are those two characters the **only** non-letter characters that can be in
your word? If not, are you willing to have **any** non-letter return 27
(assuming you "alphabet" always contains 26 letters)?

Rick Rothstein (MVP - Excel)


On Tuesday, January 11, 2011 2:17 PM Henrietta Horne wrote:

For this application, yes.


In the application that will use this data, the words will be in
sentences, so the first word and proper names will be uppercase. But
for this UDF, all lower case.


There can be others, but for now, I am happy to have all of them return
a "27".

Larger alphabets and/or shorter words benefit the forward algorithm,
as far as compute time, no?


On Tuesday, January 11, 2011 2:34 PM GS wrote:

Henrietta Horne submitted this idea :

So then, you might want to exit the function if the character is not in
sAlphabet to skip further looping:

Function GetHighIndex(sWord As String, sAlphabet As String) As Integer
Dim i As Integer, iPos As Integer
For i = 1 To Len(sWord)
iPos = InStr(1, LCase$(sAlphabet), Mid$(LCase$(sWord), i, 1), 1)
If iPos = 0 Then GetHighIndex = Len(sAlphabet) + 1: Exit Function
If iPos GetHighIndex Then GetHighIndex = iPos
Next
End Function

I also had the same concern as Rick regarding case and so note that I
forced lowercase.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc


On Tuesday, January 11, 2011 2:36 PM GS wrote:

It happens that Henrietta Horne formulated :

So does using fewer variables!<g

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc


On Tuesday, January 11, 2011 2:40 PM Clif McIrvin wrote:

I am *guessing* that the time difference between Rick's function and ours
would be measured in micro-seconds ... if you are calling it millions of
times it "might" be worth investigating <grin.

(BTW, IMO the main difference between Garry's offering and mine is "self
documentation" and ease of debugging.)

As long as your rule is valid, it seems that your solution covers all
"extraneous" characters. To cover that issue with Rick's approach would
require including all possible characters in sAlphabet ... which would
wipe out the elegance (and hurt execution time, as well), but it would
allow a variety of rules.

--
Clif McIrvin

(clare reads his mail with moe, nomail feeds the bit bucket :-)


On Tuesday, January 11, 2011 2:57 PM GS wrote:

Clif McIrvin submitted this idea :

Hi Clif,
I do not see where my offering's brevity poses any challenges for
debugging. I will give you that yours is more "self documented", as you
put it, but at what cost? IMO, the less var tracking I have to do the
easier my code is to understand AND debug.

Not saying that there is anything wrong with your offering 'as is'. I
just like brevity<g, especially Rick's one-liners<bg!

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc


On Tuesday, January 11, 2011 3:00 PM Clif McIrvin wrote:

<snip

Excellent point!


I was under the impression that is what using vbTextCompare
accomplished. Am I mistaken?

I note that you are using the "$" variation of the function names
(LCase$(), Mid$()) ... is there a speed difference vs. using LCase() and
Mid()?

I understand that using variants is slower than typed variables; I typed
the function parameters as variant in my original suggestion to allow
the possibility of testing for Empty, though I did not include that in my
code.




--
Clif McIrvin

(clare reads his mail with moe, nomail feeds the bit bucket :-)


On Tuesday, January 11, 2011 3:02 PM Rick Rothstein wrote:

See inline comments...


No sense giving you different code for this "test" case when your "real"
case will be different.


Again, it make no sense not designing for you final application.


For such short text strings, for a single call of the function, you would be
hard pressed to measure the difference. If you were examining hundreds of
thousands of words in a loop, then maybe the difference would start to
become noticeable enough to choose one method over the other.

Okay, with all that said, here is my code, modified to report one more than
the length of your alphabet for non-letters...

Function GetIndex(Word As String, Alphabet As String) As Long
If Word Like "*[!" & Alphabet & "]*" Then
GetIndex = Len(Alphabet) + 1
Else
For GetIndex = Len(Alphabet) To 1 Step -1
If InStr(Word, Mid(Alphabet, GetIndex, 1)) Then Exit For
Next
End If
End Function

Rick Rothstein (MVP - Excel)


On Tuesday, January 11, 2011 3:07 PM Rick Rothstein wrote:

See my latest response to the OP for a still quick, and what I consider a
still "elegant" solution, to covering the non-letter characters.

Rick Rothstein (MVP - Excel)


On Tuesday, January 11, 2011 3:07 PM Clif McIrvin wrote:

I am a part-time developer, and I have learned through harsh experience
that the more documentation in the code the better! If I was coding
every day, then yes, I'd agree with your observation.

I agree that there is some difference in execution time -- and sometimes
that would even be relevant! -- but in my single user environment that
has not (so far) been an issue.

I learn from both of you guys (and others!) and I agree ... Rick's
solutions often make me back up and say something like, "I sure wish I'd
thought of that!"

--
Clif McIrvin

(clare reads his mail with moe, nomail feeds the bit bucket :-)


On Tuesday, January 11, 2011 3:17 PM GS wrote:

Clif McIrvin used his keyboard to write :

No, you are not mistaken. If I read Rick's context correctly, I believe
he was refering to the typical issue of users having the Caps Lock on
inadvertently OR without giving it any thought. IMO, I'd use a filter
function to determine any chars not in my expected string, and strip
them out. But this is not what fits the OP's task.

I read somewhere that it has an effect. Not sure of the details
exactly, but I started using the $ after reading the article.

And this is why my loop UBounds to Len(sWord). If it is an empty string
there is nothing to do, and the function immediately exits and returns
'0'. I think that clearly indicates a zero length string.
CBool(Len("")=0) returns TRUE. (This makes a good arg for
documentation!<g)

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc


On Tuesday, January 11, 2011 3:20 PM Clif McIrvin wrote:

Elegant, for sure!

Reading your post after replying to Garry regarding brevity, I recall a
tale I heard many years ago about assembler code that had been running
flawlessly for years which suddenly died. The programmer no longer
worked there, and the team was stumped when they came to a dead-end in
the source code-- there was no instruction to execute! How could the
code have ever worked in the first place?!

Eventually someone realized that with the combination of processor speed
and rotation of the "swap drum" the code was running on the missing
instruction was just coming up under the read head at exactly the right
time, and the program broke when the swap drum was replaced with a head
per track disk.

The development environment has certainly changed since those days!

[BTW: I do not believe I have ever used LIKE in VBA ... I will have to
remember that one!]

--
Clif McIrvin

(clare reads his mail with moe, nomail feeds the bit bucket :-)


On Tuesday, January 11, 2011 3:30 PM Clif McIrvin wrote:

<snip

I seem to recall reading somewhere that the "sans-$" routines include
code to handle implicit type conversions ... which would be a good
argument for using the $ when you can <g.


This converstaion is *way* too much fun ... I'd better get back to work
while there is some day left <grin.


--
Clif McIrvin

(clare reads his mail with moe, nomail feeds the bit bucket :-)


On Tuesday, January 11, 2011 3:36 PM GS wrote:

Clif McIrvin brought next idea :

Well, you are right regardless of what my preference is. Especially if
your familiarity with a project is infrequent. I am also a part-time
programmer. What might be more of a difference is how I view my code as
being adequately self documenting while others may not find it so. In
cases where I think it might not be doc'd adequately I insert comments.
One thing for sure is that any code that uses lots of vars is
dauntingly hard to get a grasp of. Thus, I use vars sparingly when
possible.


Yeah, if only we all did this for our own use!<g


What's nice is that you give back. That's what I try to do as
reciprocal for the learning I have gained from so many. it is definitely a
"give-n-take" environment!

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc


On Tuesday, January 11, 2011 3:47 PM Clif McIrvin wrote:

<snip

You're welcome; and I completely agree!

--
Clif McIrvin

(clare reads his mail with moe, nomail feeds the bit bucket :-)


On Tuesday, January 11, 2011 4:10 PM Rick Rothstein wrote:

See inline comments...


Cute story... is it really true though?


I remember coding Fortran programs on those old IBM cards... what a pain
that was. We were is a satellite office, so we had to send our decks of
cards out to the central office where the computer was housed. One simple,
small mistake in typing any one of the cards caused the program to crash, of
course, but trying to find that one mistake in a stack of hundreds of
cards... well, let me tell you, that was a lot of fun... NOT! Yes, the
development environment has definitely changed. I do remember thinking I was
in heaven when we got remote dumb terminals where we could type-in and edit
our programs ourselves... "instantly", mind you, across our blazing fast 300
baud telephone connection to the main frame... ah yes, that was heaven
indeed.<g


I like Like but I do not like Like's name as it makes sentences like this
one confusing to read.<g Like is like a "poor cousin" to a Regular
Expression parser, but even with its limited parsing abilities, it still
allows you to do some fairly powerful text parsing. Here is a link to blog
article on John Walkenbach's website where he acknowledges a simplification
I sent him to reduce a 12-line function he posted down to a one-liner using
the Like operator...

http://spreadsheetpage.com/index.php...ng_a_function/

Just follow the link in the blog article (after reading the very nice
acknowledgement he gave me) to see his code and then mine.

Rick Rothstein (MVP - Excel)


On Tuesday, January 11, 2011 4:49 PM Clif McIrvin wrote:

it is been what - 15 years or more - since I heard that one. I am sure I
botched the details, but I had no trouble believing it when I first came
across it (after all, I'd written code that depended on the underlying
architecture myself!)


Ah, memory lane! For a self-imposed challenge as a college freshman I
wrote and keypunched a Fortran program to make the next move in a
'connect the dots' game I'd played as a kid - you close a square you get
another turn. That program almost used up an entire box of cards!



Nice.

My brain still overheats every time it comes up against regular
expressions .... for some reason after all those years of using * and ?
as wildcards I just have trouble grasping the concepts involved.
Perhaps the fact that my programming experience has been almost
exclusively working with numbers or items, not text, has some bearing
<g.

--
Clif McIrvin

(clare reads his mail with moe, nomail feeds the bit bucket :-)


On Tuesday, January 11, 2011 8:18 PM Ron Rosenfeld wrote:

Everybody has given you UDF's.

Here is an Excel function which will work so long as Alphabet contains **all** of the characters that might be in any word:

Alphabet is the NAME of a cell that contains your alphabet, and any other characters that might be in a word. You should put the non-letters at the end of Alphabet.

eg: NAME some cell Alphabet
enter: etaoinsrhldcumgfpwybvkjxzq'/?


Then, with your word in A1,

This formula must be **array-entered**:

=MAX(SEARCH(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1), Alphabet))

I note in one of your other posts that, if you have a non-alphabetic character, you want to return a 27. That being the case, you could use:

=MIN(27,MAX(SEARCH(MID(A1,ROW(INDIRECT("1:"&LEN(A1 ))),1),Alphabet)))

----------------------------------------

To **array-enter** a formula, after entering
the formula into the cell or formula bar, hold down
<ctrl<shift while hitting <enter. If you did this
correctly, Excel will place braces {...} around the formula.


On Wednesday, January 12, 2011 11:28 PM Henrietta Horne wrote:

Thanks. My version is now:



Function GetHighIndex(sWord As String, sAlphabet As String) As Integer

Dim i As Integer 'Loop index
Dim iPos As Integer 'Index variable
Dim iPosNF As Integer 'Index for characters not found
iPosNF = Len(sAlphabet) + 1 '.Use 1 + max index

For i = 1 To Len(sWord) '
iPos = InStr(1, sAlphabet, Mid$(sWord, i, 1), vbTextCompare)
If iPos = 0 Then GetHighIndex = iPosNF: Exit For
If iPos GetHighIndex Then GetHighIndex = iPos
Next i

End Function


Thanks for the help


On Wednesday, January 12, 2011 11:33 PM Henrietta Horne wrote:

This is my final data for the first step of the project. The results
will be used in the second step where the data will be much larger.


It will be called at least 60,000 times, but I just tested it with
that data and my version only takes a couple of seconds.

I realize that any difference would be minor, but do not we always want
to write the best code we can? ;-)


I will have to study that one. Thanks.


On Wednesday, January 12, 2011 11:35 PM Henrietta Horne wrote:

wrote:


I have trieds array formulas once or twice and got confused. I have got my
function working now, but thanks for the suggestion. I like the UDF
because I can add coded to do other things and I do not have to type a
complicated formula in a cell.


On Thursday, January 13, 2011 10:53 AM Clif McIrvin wrote:

Are you familiar with the formula auditing tools? I built a simple
worksheet with Ron's formula, then stepped through it with the "Evaluate
Formula" tool so I could understand what makes it work. Pretty neat!

--
Clif McIrvin

(clare reads his mail with moe, nomail feeds the bit bucket :-)


On Thursday, January 13, 2011 11:55 AM Rick Rothstein wrote:

That depends on what you mean by "best". The fastest code that can be
written in VB almost always involves making extensive amounts of API
function calls. Such code tends to involve large amounts of code lines which
end up being nearly impossible to read in the end, but will tend to be
magnitudes faster than any code you write using standard built-in VB
function. Are you advocating writing all your code this way because it will
always be much, much, much faster in the end? Your answer should be "No", by
the way, because the time involved in writing such code, especially for
those not familiar with API functions, would be unreasonable, not to mention
a maintenance nightmare.

I did want to say something about your initial posting by the way. You
originally wrote...

"For example, for the alphabet is 'etaoin'..."

giving us the impression that the number of characters in your "alphabets"
were somewhat small. I realized you were just "simplifying" the question for
us, but I would like to suggest that you not do that for future questions
you ask. If it were indeed the case that your alphabets were 6, 7, 8 or so
characters long, then the code I posted would have almost always been faster
than the other code submissions you received (it was designed that way).
However, once you told us your alphabets were always 26 characters long, my
code lost it advantage. With a 26-character alphabet, your test strings
would need to be, on average, around 14 characters long (or longer) for my
code to be almost always faster... the boundary length being
2*Len(TestString)-1. The point I am trying to make here is that if you
simplify your questions us, then we will give you solutions, sometimes
really good ones, for a situation you do not actually have. It is better to
tell your exact setup so we can design our answers for the question that you
actually have.

Rick Rothstein (MVP - Excel)


On Thursday, January 13, 2011 12:49 PM Ron Rosenfeld wrote:

No question but that UDF's can be more flexible and, depending on how they are written, easier to understand.

But some advantages of worksheet functions include that they are usually much faster; and, if you are distributing your worksheet, you do not have to worry about policies that restrict the use of VBA Macros and/or UDF's.


On Saturday, January 15, 2011 1:07 AM Henrietta Horne wrote:

No, I am not. I will add it to my todo list.

That's the problem. There are so many interesting features to learn
and they are such a temptation for procrastinating what I really nood
to do, which is get the project done. ;-)


On Saturday, January 15, 2011 1:07 AM Henrietta Horne wrote:

wrote:


Good point.


On Saturday, January 15, 2011 11:29 AM Clif McIrvin wrote:

<...


Been there. Almost every day, in fact <g.

Don't be afraid to slap together some ugly code that works - how
important it is that you ever get back to refine it depends on your
circumstances. If that macro is going out to 500 users in the
organization, then you would better find the time to make it pretty
bullet-proof and self explanatory. If you are the only one that is ever
going to use it, it can stay that way forever - at least until the need
comes up to modify the rules, at which time you can apply the knowledge
you have gained in the interim to improve it. At least, that is the way I
approach it here <grin.


--
Clif McIrvin

(clare reads his mail with moe, nomail feeds the bit bucket :-)

On Sat, 24 Sep 2011 18:18:51 GMT, Oliver Annells wrote:

This formula array entered (Ctrl+Shift+Enter) works without any VBA.

=MAX((TRANSPOSE(MID($A$18,ROW($A$1:INDEX(A:A,LEN( $A$18))),1))=MID($C5,ROW($A$1:INDEX(A:A,LEN($C5))) ,1))*TRANSPOSE(ROW($A$1:INDEX(A:A,LEN($A$18)))))

When I entered this the word was in C5 and the alphabet string was in A18.



I made Alphabet a Defined Name.
With the alphabet string in F1,
Alphabet Refers To: =MID(Sheet1!$F$1,ROW(INDIRECT("1:99")),1)


This makes Alphabet an array constant with each letter a single element, then:

array-entered: =MAX(MATCH(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),A lphabet,0)) gives the requested results.

"Ron Rosenfeld" wrote in message
...
On Sat, 24 Sep 2011 18:18:51 GMT, Oliver Annells
wrote:

This formula array entered (Ctrl+Shift+Enter) works without any VBA.

=MAX((TRANSPOSE(MID($A$18,ROW($A$1:INDEX(A:A,LEN ($A$18))),1))=MID($C5,ROW($A$1:INDEX(A:A,LEN($C5)) ),1))*TRANSPOSE(ROW($A$1:INDEX(A:A,LEN($A$18)))))

When I entered this the word was in C5 and the alphabet string was in
A18.



I made Alphabet a Defined Name.
With the alphabet string in F1,
Alphabet Refers To: =MID(Sheet1!$F$1,ROW(INDIRECT("1:99")),1)


This makes Alphabet an array constant with each letter a single
element, then:

array-entered:
=MAX(MATCH(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),A lphabet,0)) gives
the requested results.


Oliver, Ron -
Thanks to both of you for sharing a different approach!

--
Clif McIrvin

(clare reads his mail with moe, nomail feeds the bit bucket :-)