How to Use VBA UCASE Function in Excel (4 Examples)

Excel provides several VBA functions that can be used while writing a macro or to define your own function to perform your desired tasks. In this article, we are going to show you a VBA function called UCASE.

Overview - VBA UCASE Function

Practice Workbook

To download the workbook click the link below.

The VBA UCASE Function

1. Basics of VBA UCASE


The VBA UCASE function in Microsoft Excel converts the provided string into an all uppercase text.




string: A text value that is to be converted into upper case.


Workable from Excel 2003.

2. Uses of VBA UCASE

I. Uppercase String in Message Box

From the description of the function, you might have understood that the UCASE function returns a string in an uppercase form.

Let’s have a string “we are learning vba ucase” which is stored at the B3 cell of our practice sheet.

Example String - VBA UCASE Function

You can see the string in lowercase. Now we are going to convert the string into uppercase. And our code will be

Sub UCase_MsgBox()

Dim text As String

text = UCase(Range("B3"))

MsgBox text

End Sub

You need to write the code in Microsoft Visual Basic for Applications window which will be found in the Developer tab in Excel (or you can use the ALT + F11 shortcut)

UCase - MsgBox output - VBA UCASE Function

Using the Dim keyword, we have declared a string-type variable text. We will set our uppercased string in this variable.

To introduce the Cell Reference we need to use Range and there we have set B3. The UCASE function then will convert the string stored in B3.  

We have used MsgBox to return the output through a message box.

Run the code and you will find the string in uppercase.

Result in Message box - VBA UCASE Function

II. Uppercase String in Cells

In the earlier section, we have seen the output through a message box. We can find the result at any of the cells in an Excel sheet.

Let’s use a direct input in the UCASE function and write code to see the output at a cell of the Excel sheet.

We are using “we are learning vba ucase” as our string. And our code will be

Sub UCase_Cells()

Dim text As String

text = UCase("we are learning vba ucase")

Cells(3, 2).Value = text

End Sub


Direct input to UCase - VBA UCASE Function

Here we have used Cells that take two parameters; row_index and column_index. We have inserted 3 and 2 as row_index and column_index respectively. And the value of the cell has been set as the text variable.

Run the code the uppercased string will be stored at the B3 (Second column third row).

Output in Cell - VBA UCASE Function

Without taking the direct input we can also perform our task. For example, let’s imagine we have the string at the C3 cell and we want to set the uppercase of the string at the C4.

Data in cell to convert - VBA UCASE Function

Just for a change, we are using Range. It’s also helpful as you can explicitly insert your desired Cell Reference without any confusion.

Sub UCase_Range()

Dim text As String

text = UCase(Range("C3"))

Range("C4").Value = text

End Sub


Code to convert and output in cell - VBA UCASE Function

Here Range helped us to introduce the input string into the UCASE function as well as to set the output cell. Run the code.

Result in cell - VBA UCASE Function

The converted string will be at the desired cell.

Similar Readings:

III. Uppercase a Specific Range of String

Using the UCASE function we can convert the string from any specific range of cells at one go. To show you examples we have brought a dataset of a few famous names all in lowercase.

Dataset to specific range convert - VBA UCASE Function

We are set to convert all the strings into uppercase and our code will be the same as the one below

Sub UCase_EntireRange()

Dim i As Integer

For i = 4 To 9

Cells(i, 3).Value = UCase(Cells(i, 2).Value)

Next i

End Sub


Code to convert specific range - VBA UCASE Function

Here we didn’t use any string variable to store the converted string rather go for using the UCASE function and directly insert that into the cell we prefer.

We have used a For loop to store the value of each cell from range correctly. You can see the names in our dataset are in B4 to B9 cells. That’s why we have used 4 to 9 as our range for the loop.

Run the code to find the names in uppercase.

Result of conversion of specific range - VBA UCASE Function

Here we have the loop and the parameter values of Cells as per our circumstances, you need to change them according to your dataset.

IV. Select Cell to Convert Uppercase

We can write the macro code in such a way that the value from our selected cell or range of cells is converted into uppercase.

Our example dataset has some widely used proverbs.

Dataset to select data and convert - VBA UCASE Function

Our approach will be using a Button that will help Excel to trigger the code upon selecting the cell.

You will find the Button in the Insert section from the Developer tab.

Button Inclusion

Here we have brought a Button and named it as Upper Case. Now insert the code into the Button.

Sub UCase_Selection() 

Dim range As range

Set range = Selection

For Each Cell In range

Cell.Value = UCase(Cell)

Next Cell 

End Sub


Code to select and convert

We have used a For loop to fetch and convert the selected cell’s value into uppercase. Save the code.

Now select any particular cell and Click the button Upper Case

Select data

You will find the string in uppercase (The image below).

Click data and converted result

You can select a range of cells also.

Select range of cells

Select the rest of the cells and Click the button.

Result of selected cells conversion

You will find all the strings in uppercase.

3. Quick Notes

  1. You can provide a string, contains uppercase texts

Mixed data

The uppercase will remain as it is, the function will only convert the lowercase texts.

Mixed data result

  1. If you insert an empty string in the function,

Empty String

you will find the empty string in return.

Empty String result


That’s all for today. We have tried showing how you can use the VBA UCASE function. You can use the function to convert the string into uppercase. Hope you will find this helpful.

Feel free to comment if anything seems difficult to understand. Let us know any of your UCASE function-related scenarios where you have stuck, we are ready to help.

Further Readings

Shakil Ahmed

Shakil Ahmed

My name’s Shakil. My articles are targeted to support you in enriching knowledge regarding different features related to Microsoft Excel. I am fond of literature, hope some knowledge from them will help me providing you some engaging articles even though some weary technical terms.

We will be happy to hear your thoughts

Leave a reply