Surprise in StrConv using vbProperCase

[Gary Schuldt]

VBA Help says using the vbProperCase option in the StrConv function converts
the first letter of every word to uppercase.

The surprise is that it converts all other characters to lower case!

This is an *unpleasant* surprise in my application, because it changes
"O'Malley" to "O'malley", which I don't want.

I just want a function to capitalize the first letter of each word and leave
the other letters alone. Anyone know of such a function so I don't have to
code it myself?

Gary

 

[Ken Snell [MVP]]

Try this (the behavior you note for StrConv is a commonly discussed issue
here in the newsgroups):

Public Function CapFirstLetter(strString As String) As String
Dim strLetter As String
strLetter = Left(strString, 1)
Select Case Asc(strLetter)
Case 97 To 122
strLetter = Chr(Asc(strLetter) - 32)
End Select
CapFirstLetter = strLetter & Mid(strString, 2)
End Function

 

[Gary Schuldt]

Thanks, Ken,

(Before posting, I searched for StrConv and proper case and vbProperCase and
got no hits on any of them.)

Your code will capitalize the first letter of a string. The strings I have
could consist of multiple words (they are cultivar names of plants, such as
MacTavish's Sky Pencil), so the specs for StrConv using vbProperCase match
what I need. (Unfortunately, it doesn't work as advertised.)

Because the cultivar often includes the cultivator's name, you could
conceivably have Meyers-Briggs Delight.

The Int'l Rules for Botanical Nomenclature say each word in the cultivar
name must be capitalized. There is no rule to cover the case when hyphens
are present, so I will just have to trust the user to type the correct name
in those cases.

I'm thinking of writing a StrCapitalize that will:

1. Do what your CapFirstLetter below does, but for each "word" in the
string;
2. Assume/use a blank as a delimiter for words;
3. Squeeze out superfluous blanks (since they are so hard to detect
visually).

My pseudocode would be something like:

1. Use Split(string into array of words using blank as delimiter);
2. (I'm not what sure Split does when it encounters two consecutive
delimiters; does it yield an (extra) array element with a value of a
zero-length string?)
3. Use your CapFirstLetter function on each element of the array
4. Use Join(the array back into a string using blank as delimiter).

I would display the result and tell the user if it's not yet correct (like
intracaps needed for MacDonald and O'Hara and Barr-Mason) they can correct
it themselves.

What do you think?

Gary

 

[Brendan Reynolds]

From memory, I believe you're right about Split - that it will include an
empty element if it finds two consecutive delimiters with nothing between
them. Something like the following should eliminate multiple spaces though.
This is untested air-code, so could potentially include typos or syntax
errors on my part, but I think you'll get the gist of it ...

Do While InStr(1, YourString, Space$(2)) > 0
YourString = Replace(YourString, Space$(2), Space$(1))
Loop

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

 

[Dirk Goldgar]

Thanks, Ken,

(Before posting, I searched for StrConv and proper case and
vbProperCase and got no hits on any of them.)

Your code will capitalize the first letter of a string. The strings
I have could consist of multiple words (they are cultivar names of
plants, such as MacTavish's Sky Pencil), so the specs for StrConv
using vbProperCase match what I need. (Unfortunately, it doesn't
work as advertised.)

Because the cultivar often includes the cultivator's name, you could
conceivably have Meyers-Briggs Delight.

The Int'l Rules for Botanical Nomenclature say each word in the
cultivar name must be capitalized. There is no rule to cover the
case when hyphens are present, so I will just have to trust the user
to type the correct name in those cases.

I'm thinking of writing a StrCapitalize that will:

1. Do what your CapFirstLetter below does, but for each "word"
in the string;
2. Assume/use a blank as a delimiter for words;
3. Squeeze out superfluous blanks (since they are so hard to
detect visually).

My pseudocode would be something like:

1. Use Split(string into array of words using blank as
delimiter);
2. (I'm not what sure Split does when it encounters two
consecutive delimiters; does it yield an (extra) array element with a
value of a zero-length string?)
3. Use your CapFirstLetter function on each element of the array
4. Use Join(the array back into a string using blank as
delimiter).

I would display the result and tell the user if it's not yet correct
(like intracaps needed for MacDonald and O'Hara and Barr-Mason) they
can correct it themselves.

What do you think?

'----- start of code ------
Function CapWords(pstrGivenString) As String

Dim astrWords() As String
Dim strWord As String
Dim strOutput As String
Dim I As Integer

astrWords = Split(pstrGivenString)

For I = LBound(astrWords) To UBound(astrWords)
strWord = astrWords(I)
If Len(strWord) > 0 Then
strOutput = strOutput & " " & _
UCase(Left(strWord, 1)) & Mid(strWord, 2)
End If
Next I

If Len(strOutput) > 0 Then
CapWords = Mid(strOutput, 2)
End If

End Function
'----- end of code ------

You could, if you wanted, put in an inner loop to split words on a
hyphen, too.

 

[Ken Snell [MVP]]

Sorry, Gary... my minds' eye narrowed its vision field too much when I put
that function together. Wasn't thinking about multiple words being handled
that way. But I think Dirk has posted a more comprehensive function for you.

 

[Gary Schuldt]

Thanks, Dirk,

I just read your code and I think it passed my mind-compiler's tests for
logic, understandability and elegance!

Question: Are the initialized values of String variables (and functions) by
default set to ""? I see you didn't initialize either strOutput nor
CapWords.

Otherwise, it looks like I can use it and upgrade it to handle intra-hyphens
if needed.

Gary

 

[Gary Schuldt]

Thanks, Ken.

See my comments on Dirk's code; I think we're getting close!

Gary

 

[Dirk Goldgar]

Thanks, Dirk,

I just read your code and I think it passed my mind-compiler's tests
for logic, understandability and elegance!

Question: Are the initialized values of String variables (and
functions) by default set to ""? I see you didn't initialize either
strOutput nor CapWords.

Yes.

 

[Gary Schuldt]

Thanks, Brendan,

I'm sure I'll be able to use the technique in your code somewhere!

Gary

 

[Gary Schuldt]

Thanks to all, it's working like a charm!

 

[Brendan Reynolds]

Nah, forget my code in this situation, Dirk's code, which skips any
zero-length strings when generating the output, is much more efficient!

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.