Daily posts of Excel tips…and other stuff
VBA provides the UCase and LCase functions to convert text to upper and lower case, respectively. But what about proper case? For that, you’ll need to use the StrConv function.
StrConv(”my text here”, vbProperCase)
will return
My Text Here
Alternatively, you can use the worksheet function PROPER, like this
Application.Proper(”more proper text”)
More Proper Text
Certain comments are subject to moderation and may not appear immediately. You can use HTML tags in your comment. If you include a greater-than or less-than sign or anything else that could be interpreted as HTML, your comment won't look nice. You need to escape those characters. To post VBA code in your comment, use [VB] tags, like this: [VB]Code goes here[/VB].
Toad:
Is one method inherently preferable? I guess I would have gone straight for the VBA version of the PROPER function, since I’m used to using that function already.
19 April 2004, 1:11 pmDick:
Toad: I would guess that calling a worksheet function is slower than a built-in VBA function, but only marginally so. If you were concerned with speed, you would probably want StrConv, but for most applications you can use what you like.
19 April 2004, 1:16 pmAntro:
Maybe I’m not getting the implementation quite right, but I couldn’t get the worksheet function PROPER to work, but everything gets a little tricky when working with Excel through OLE Automation in Word. But I got the StrConv to work on first try
1 November 2004, 8:18 amJuan Pablo:
Antro,
When working from outside of Excel, you need to replace “Application” with the object variable that points to Excel, for example instead of
Application.Proper(”more proper text”)
you’d use
XlApp.Proper(”more proper text”)
and that’s the trick about it…
1 November 2004, 12:03 pmTramadol:
The secret of health for both mind and body is not to mourn for the past, worry about the future, or anticipate troubles but to live in the present moment wisely and earnestly. Buy Tramadol
27 December 2004, 4:23 amEd Brooks:
It should be noted that these two functions will return different results. For the string “JAMES, P.D.” strconv will return “James, P.d”. Application.proper will return the value “James, P.D.”
The second example is the preferred result, if you are talking about a surname followed by initials.
24 February 2005, 4:01 pmPaddy Heron:
When using strconv can you use a variable inside the bracets i.e.
14 August 2008, 3:16 pmStrconv(TheString, vbpropercase). When I try it I am propmted with the = sign.
Can anyone help?