4.2 LCase and UCase in Excel VBA

Two of the easier in-built functions to get the hang of are LCase and UCase. As you might expect, these are used to changes letters into lowercase or uppercase characters. Let's see how they work.

To try these out, create a new blank workbook in Excel. Save the file as string_functions.xlsm. Enter some headings in cells A1, B1, and C1. Enter: Text, LCase, and UCase. Enter a name in cell A2, anything you like. Your spreadsheet might then look like this:

Click the Developer ribbon at the top of Excel, then click the View Code item on the Controls panel.

In the VBA Editor, if you can't see a blank code window for Sheet1, double click Sheet1 in the Project Explorer on the left. (If you can't see the Project Explorer, click View > Project Explorer from the menu at the top.)

Create a new sub in your blank Sheet1 coding window. Call it ChangeCase. To get the text out of cell A2 on your spreadsheet, add the following lines:

Dim FullName As String

FullName = Range("A2").Value

All this code does is to set up a variable called FullName. The variable is declared with As String. To place something into this variable, we've used Range("A2").Value.

Your code should now look like this:

To use the lowercase function, you only need the following on the right of an equal sign:

LCase(Text_To_Convert)

Whatever you're trying to convert goes between the round brackets of the LCase function. The text you're trying to convert can be direct text surrounded by double quotes, or a variable that holds some a string of text.

We want to place the converted text in cell B2, just under the LCase heading. All we need to do is use offset with our A2 Range:

Range("A2").Offset(, 1).Value = LCase(FullName)

Range("A2").Offset(, 1) moves us one column to the right of cell A2. We then access the Value property. To the right of the equal sign we have out LCase function. VBA will convert whatever we have in the variable called FullName to lowercase, and use that as the Value for cell B2.

Add the line to your own Sub and your code will look like this:

Click anywhere inside of your Sub to try it out. Now press F5 on your keyboard to run the code. Switch to your spreadsheet and you should see this:

The code to convert the name to uppercase is very similar. It's this:

Range("A2").Offset(, 2).Value = UCase(FullName)

Only two things have change, here. For the Offset, we have a 2 instead of a 1. This moves us two columns to the right of cell A2. The function that converts to uppercase is UCase. It's used in exactly the same way as LCase.

Add the line to your own code. Run your Sub and your spreadsheet will look like this:

So we have now converted the name in cell A2 to lowercase and uppercase. Notice that the name in A2, David Gilmour, is in Proper Case. This is when you capitalise the first letter of each word.

Sadly, Excel VBA doesn't have a nice, easy function to convert to Proper Case, so there's no PCase.

There is, however, a Worksheet function called Proper. To use it, try the following code:

Dim FullName As String

FullName = "DAVID GILMOUR"

Range("A2").Offset(, 3).Value = Application.WorksheetFunction.Proper(FullName)

The code that converts to Proper Case is this:

Application.WorksheetFunction.Proper(FullName)

Application is a top-level object, meaning the whole of Excel. WorksheetFunction is used to access Excel's in-built function. One of these functions is Proper. In between the round brackets of Proper, you type the variable you're trying to convert. As well as typing a variable name, you can type direct text surrounded by double quotes.

In the next lesson, we'll cover three more inbuilt string functions: Trim, Len, and Space.

<< Excel VBA Course Menu

Lots more free online courses here on our main Home and Learn site

© All course material copyright Ken Carney