« Copy Subtotals Only
Beginning VBA: The Visual Basic Editor »

Proper Case in VBA

19th April 2004, 11:50 am by Dick Kusleika

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

Category: VBA Basics  |  Comment (RSS)

7 Comments

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

  2. Dick:

    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 pm

  3. Antro:

    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 am

  4. Juan 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 pm

  5. Tramadol:

    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 am

  6. Ed 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 pm

  7. Paddy Heron:

    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?

    14 August 2008, 3:16 pm

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

Leave a comment