Proper Case in VBA

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

Posted in Uncategorized

10 thoughts on “Proper Case in VBA

  1. 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.

  2. 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.

  3. 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

  4. 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…

  5. 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.

  6. 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?

  7. 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

  8. 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


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.