How Can I Change All the Lowercase Letters in an Excel Worksheet to Uppercase Letters?

ScriptingGuy1

Hey, Scripting Guy! How can I change all the lowercase letters in an Excel worksheet to uppercase letters?

— BC

Hey, BC. Have you ever heard the old expression, “If you want something done you have to do it yourself”? Well, to tell you the truth, we Scripting Guys had heard of that old expression, but we never paid much attention to it. Do something ourselves, when maybe someone else could do it for us? That’ll be the day! (Editor’s Note: Actually, the expression is “If you want something done right you have to do it yourself.” But the Scripting Guys don’t pay much attention to that either.)

Much to our surprise, however, it turns out that today is the day. When we first saw your question we figured, “Well, this should be easy: we’ll just get Excel to change the letter case for us.” Or so we thought. Try as we might, however, we couldn’t figure out a way to get Microsoft Excel to make this change for us. We assumed that Excel had a Change Case command similar to that in Microsoft Word; it doesn’t. We assumed that we could simply select all, change the font properties and make all the letters uppercase; we couldn’t. We assumed we could do a search-and-replace, changing the letter case along the way; no such luck.

Needless to say, by that time we were in a state of panic. But then Dean Tsaltas, the wisest of all the Scripting Guys, said, “If you need to change the letter case, why not just make that change yourself, without relying on a built-in Excel function to make the change for you.” Talk about profound; no wonder all the Scripting Guys look up to Dean and turn to him whenever we need advice.

As usual, once we listened to Dean the rest was easy:

Set objExcel = CreateObject(“Excel.Application”)
objExcel.Visible = True

Set objWorkbook = objExcel.Workbooks.Add()
Set objWorksheet = objWorkbook.Worksheets(1)
objWorksheet.Cells(1,1) = “abcdef”
objWorksheet.Cells(1,2) = “ghijkl”
objWorksheet.Cells(1,3) = “mnopqr”
objWorksheet.Cells(1,4) = “stuvwx”
Wscript.Sleep 2000
Set objRange = objWorksheet.UsedRange
For Each objCell in objRange
    objCell.Value = UCase(objCell.Value)
Next

Let’s talk about what we did, and why. We start off by creating an instance of the Excel.Application object and then set the Visible property to True; that simply gives us a running instance of Excel that we can see on-screen. After that we use these two lines of code to create a new workbook, and to bind to the first worksheet in that workbook:

Set objWorkbook = objExcel.Workbooks.Add()
Set objWorksheet = objWorkbook.Worksheets(1)

After all that initial setup we use these four lines of code to type some characters in cells A1 through D1:

objWorksheet.Cells(1,1) = “abcdef”
objWorksheet.Cells(1,2) = “ghijkl”
objWorksheet.Cells(1,3) = “mnopqr”
objWorksheet.Cells(1,4) = “stuvwx”

We then pause the script for two seconds (Wscript.Sleep 2000), just so we’ll have a chance to see the spreadsheet before we start changing letter cases. Speaking of which, that spreadsheet should look something like this:

This, of course, is the point where we figured Excel would change the letter case for us. As we found out, Excel isn’t going to be of much help here. Therefore, we did what Dean would do, and changed the letter cases ourselves.

So what would Dean do in this situation (at least in theory)? Well, he’d no doubt begin by selecting all the cells on the worksheet that contained data. That sounds complicated, but it’s actually no harder than this:

Set objRange = objWorksheet.UsedRange

What we’re doing here is creating a Range object (with the object reference objRange) that’s mapped to the worksheet’s UsedRange property. The used range represents the range of contiguous cells that have data in them. For example, in our sample spreadsheet we have data in cells A1 through D1; thus the used range consists of cells A1 through D1. Suppose we had data in cell A1 and another piece of data in cell Z100. In that case the used range would consist of cells A1 through Z100, including all the blank cells in between.

Yes, a very handy little thing to know about.

Once we have a collection of cells we can set up a For Each loop to walk through each cell in the collection:

For Each objCell in objRange
    objCell.Value = UCase(objCell.Value)
Next

As you can see, inside the loop we do only one thing: we set the value of the cell to the uppercase version of whatever happens to be the value of the cell at the moment. (That’s what the VBScript function UCase is for: it converts letters to uppercase.) For example, the current value of cell A1 is this:

abcdef

After running cell A1 through the UCase function the value of cell A1 will be this:

ABCDEF

Etc., etc.

Here’s what we’ll see once we’ve run through the entire collection:

Which is just exactly what we wanted to see: all the letters on the worksheet have been converted to their uppercase equivalents. Best of all, we did it all by ourselves. Thanks, Dean: like we’ve always said, you’re the best!

ScriptingGuy1

Follow