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.


Practice Workbook

To download the workbook click the link below.


The VBA UCASE Function

Basics of VBA UCASE

  • Summary

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

  • Syntax

UCase(string) 

  • Arguments
Argument Explanation
string A text value that is to be converted into upper case.
  • Versions

Workable from Excel 2003.


Uses of VBA UCASE

1. 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 in the B4 cell of our worksheet.

VBA UCase Function for Uppercase in Msg Box

You can see the string in lowercase. Now we are going to convert the string into uppercase.

Code:

Sub UCase_MsgBox()

Dim text As String

text = UCase(Range("B4"))

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)

Code for Msg Box with VBA UCase Function

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

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

We have used MsgBox to return the output through a message box. Press ALT+Q to switch back to the worksheet window.

  • Now, Run the code by pressing ALT+F8 and you will find the string in uppercase.


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

Code:

Sub UCase_Cells()

Dim text As String

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

Cells(4, 2).Value = text

End Sub

VBA UCase Function in Cell

Here we have used Cells that take two parameters; row_index and column_index. We have inserted 4 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 B4 (2nd column 4th row).

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

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

Code:

Sub UCase_Range()

Dim text As String

text = UCase(Range("C4"))

Range("C5").Value = text

End Sub

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

The converted string will be at the desired cell.


Similar Readings:


3. Uppercase a Specific Range of String

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

We are set to convert all the strings into uppercase.

Code:

Sub UCase_EntireRange()

Dim i As Integer

For i = 5 To 10

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

Next i

End Sub

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 the range correctly. You can see the names in our dataset are in B5 to B10 cells. That’s why we have used 5 to 10 as our range for the loop.

Run the code to find the names in uppercase.

VBA UCase Function for Entire Range

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


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

The dataset below has some widely used proverbs.

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.

Here we have brought a Button and named it Uppercase.

  •  Now insert the code into that Button.

Code:

Sub UCase_Selection() 

Dim range As range

Set range = Selection

For Each Cell In range

Cell.Value = UCase(Cell)

Next Cell 

End Sub

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

VBA UCase Function

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

  • You can select a range of cells also.

  • Select the rest of the cells and click the button.

VBA UCase Function

You will find all the strings in uppercase.


Quick Notes

  1. You can provide a string, contains uppercase text.

Code:

Sub UCaseDirectly_MsgBox()

Dim text As String

text = UCase("Hello World")

MsgBox text

End Sub

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

VBA Ucase Function


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

Code:

Sub EmptyString_MsgBox()

Dim text As String

text = UCase("")

MsgBox text

End Sub

you will find the empty string in return.


Conclusion

That’s all for today. We have tried showing you 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

ExcelDemy
Logo