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
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.
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.
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
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…
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
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.
When using strconv can you use a variable inside the bracets i.e.
Strconv(TheString, vbpropercase). When I try it I am propmted with the = sign.
Can anyone help?
Hi There
In repsonse to Paddy’s question, I don’t see any reply to his post,
even though it’s quite old I thought I would answer it might help someone else.
I ran into the smae problem of being prompted with the “=” sign the
solution is quite simple using your example change it to look like this
TheString = StrConv(TheString, vbProperCase)Ian
Hello,
If you are using paranthesis you have to assign to a variable. Otherwise you use without paranthesis. But in this subject it does nothing without them :)
StrConv “test”, vbProperCase ‘No syntax error, but there is no effect
mvar = StrConv (“test”, vbProperCase) ‘in this case mvar is “Test”
Özkan