What is VBA UCase?
The VBA UCase function converts all the string characters into upper case. Here, U stands for UPPER. Hence, the formula can read as “UPPERCASE.” VBA UCase is a built-in function that takes a string value as input and, in return, gives all the characters in capital letters. The VBA UCase function converts all characters into the upper case, not the first one.
For example, look at the following data in Excel.

We have a city, “New York,” in cell A2, and we have to update cell B2 with the upper characters of the same city name. The following VBA code will convert all the characters of cell A2 to upper case and store them in cell B2. We will get the following result in cell B2 when we execute it.
Sub Upper_Intro()
Range(“B2”).Value = UCase(Range(“A2”).Value)
End Sub

All the characters from cell A2 are in cell B2 converted to upper case characters.
Table of contents
Key Takeaways
- VBA UCase is a built-in function which converts string value characters to upper case. This is a replica of UPPER function in Excel worksheet.
- We can convert all the characters of the given string to the upper case. Partial conversion is not possible.
- We can loop through all the cells and convert the string to the upper case.
- We can add conditions to VBA UCase using the IF logical condition and convert the string to upper case only if it matches the criteria.
Syntax – VBA UCase Function
The following is the syntax of the VBA UCase function.

There is only one mandatory argument. It is the string that needs to be converted to the upper case. The string could be a variable that holds the string value or a cell reference in excel.
Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)
If you want to learn Excel and VBA professionally, then Excel VBA All in One Courses Bundle (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.
How to Convert Text to Uppercase using VBA UCase Function?
Here is the step-by-step approach to applying the VBA UCase function to convert a string to the uppercase.
For example, assume we have the word “Washington d c,” and we must convert it to upper case. Follow the steps listed below.
Step 1: Start the sub-routine procedure by naming the VBA macro.

Step 2: Define a variable to assign the string value to be converted to the upper case.

Note: Since we assign only string values to the variable in this example, we have assigned string VBA data type.
Step 3: Assign the word “Washington d c” to the variable.

Step 4: Define another variable to assign the VBA UCase function.

Step 5: To this newly defined variable, assign the VBA UCase function.

Step 6: The argument of the VBA UCase function is “string,” i.e., the string value to be converted to upper case. We have already assigned the string value to the variable “MyString.” Hence, let’s enter the variable name instead of the string value.

The VBA UCase function will now convert the given string to upper case.
Step 7: Display the result in a message box.

The following is the complete code, which can be directly copied to your VBA module.
Sub UCase_Basic()
Dim MyString As String
MyString = “Washington d c”
Dim Result_String As String
Result_String = UCase(MyString)
MsgBox Result_String
End Sub
Let’s execute the code. We can see the following result in a message box.

As seen, the original text “Washington d c” has been converted to upper case characters.
Thus, we can use the VBA UCase function to convert the string to upper case.
Example of VBA UCase Function
Let us show you some real-time scenarios to apply the VBA UCase function.
Example #1: Convert the cell value into Upper Case Characters
In the typical world, we have values in Excel cells, and we play around with those cell values. For example, look at the following data in Excel.

We have some country names in Column A and cities that belong to those countries in Column B. We must convert those city names in Column B into upper case characters in Column C. The targeted VBA UCase column here is column “C.”
First, let us convert one cell data into upper case. The following code will convert the B2 cell value into upper case characters and store it in cell C2.
Sub UCase_Ex1()
‘Define a variable to store the original text from cell
Dim MyString As String
‘Assign the value from cell B2 to the variable
MyString = Range(“B2”).Value
‘Define a variable to apply the VBA UCase function
Dim Result_String As String
‘Apply VBA UCase function
Result_String = UCase(MyString)
‘Store the VBA UCase function in cell C2
Range(“C2”).Value = Result_String
End Sub
Let’s execute this code. We get the following result in cell C2.

All the characters are converted to upper-case characters.
However, we need to convert the other city names as well. We cannot write individual code for each cell; we need to use loops to counter that, and we will see that in the next example.
Example #2: Use FOR NEXT Loop to Avoid Multiple Lines of Coding
In Example #1, we had many countries and city names. Converting all the city names into the upper case does not require individual cell coding. Rather, we must use loops to avoid multiple lines of coding.
The following code will dynamically convert all the city names into uppercase characters.
Sub UCase_Ex2()
‘Define a variable to find the last used cell
Dim LR As Long
‘Find the last used cell row number
LR = Cells(Rows.Count, 1).End(xlUp).Row
‘Define a variable to be used for loops
Dim k As Long
‘Intitate the loop
For k = 2 To LR
‘Apply VBA UCase function
Cells(k, 3).Value = UCase(Cells(k, 2).Value)
Next k
End Sub
Execute this code. It will convert all the city names into uppercase in column C.

Although we add a few more cities after the existing data, it will dynamically expand the VBA UCase function to all the cells and convert the city names into uppercase.
Example #3: Conditional VBA UCase
In the previous example, we have seen how to apply the VBA UCase function inside a loop and get the job done. Apart from this we can also apply conditions to convert the string to upper case characters.
For example, assume we must convert all the city names into upper case characters except for the country “France.” We must apply logical conditions and check the country name and convert the city name if the country name is not equal to “France.”
The following code will do the same.
Sub UCase_Ex3()
‘Define a variable to find the last used cell
Dim LR As Long
‘Find the last used cell row number
LR = Cells(Rows.Count, 1).End(xlUp).Row
‘Define a variable to be used for loops
Dim k As Long
‘Intitate the loop
For k = 2 To LR
‘Apply IF logical condition
If Cells(k, 1).Value <> “France” Then
‘Apply VBA UCase function
Cells(k, 3).Value = UCase(Cells(k, 2).Value)
Else
‘If the country name is France then only blank will come
Cells(k, 3).Value = “”
End If
Next k
End Sub
It is an extension of the previous code. We have added the IF logical condition inside the loop to check if the country name in column A is not equal to “France.” If not equal to “France,” it will convert the city name to upper case. Els,e it will insert only a blank value.
Example #4: Apply VBA UCase for Selected Range
Applying VBA UCase for a range selection is possible, where users will select the range where VBA UCase must be applied and then click on the button to convert the selected cell range values into uppercase characters. For example, look at the following data in Excel.

We have city names, and we need to select the city range and click on the button to convert them to upper case characters. The following code will do the same.
Sub UCase_Ex4()
‘Define a variable to set the targeted range
Dim Rng As Range
‘Set the targeted range to equal to selection from the user
Set Rng = Selection
‘Apply For Each loop to go through each cell object
For Each Cell In Rng
Cell.Value = UCase(Cell)
Next Cell
End Sub
Copy this code to your VBA module.

In the worksheet insert one of the shapes.

Step 1: Right-click on this shape and assign the macro copied to the module in the previous step.

Step 2: Insert a text to the shape to make the user aware of this shape functionality.

Step 3: Now, select the range which needs to be converted to upper case character. For example, we will select the range of cells A2 to A5.

Step 4: Click on the shape which has the macro reference.

As we can see once we click on the macro referenced button it has converted all the selected cell values into upper case characters.
This macro will convert only the selected cells values irrespective of the column or row it belongs to.
Important Things to Note
- VBA UCase function will throw a VBA UCase compile error if the variable is not defined explicitly when the option explicit option is enabled at the top of the module.
- VBA UCase function accepts only one cell value at a time; if the input value is referenced to multiple cells, it will throw Run time error of Type Mismatch.
- Example #4 code will convert only the selected cell range values into upper case character. If no cell range is selected, then the current active cell will be treated as selected cell and convert that cell value into upper case character.
Frequently Asked Questions (FAQs)
The following are some of the differences between UCase and Upper functions in VBA.
• UCase is a VBA function whereas Upper is a worksheet function.
• We don’t get to see UCase as worksheet function and Upper as VBA function. However, we can still access the UPPER function as part of the worksheet function category.
The functionality of both the functions is identical.
UCase accepts only one cell value as an input. If we give references of multiple cell range, then it will not work ended up giving Runtime Error Type mismatch.
LCase: This converts all the characters of the given string to lower case characters.
UCase: This converts all the characters of the given string to upper case characters.
Download Template
This article must be helpful to understand the VBA UCase, with its formula and examples. You can download the template here to use it instantly.
Recommended Articles
This has been a guide to VBA UCase. Here we learn how to convert text to Uppercase using UCase function with examples & downloadable excel template. You can learn more from the following articles –
Leave a Reply