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.

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

• Arguments

• Versions

Workable from Excel 2003.

Uses of VBA UCASE

Example 1 – Uppercase String in a Message Box

We have a string “we are learning vba ucase” which is stored in the B4 cell of our worksheet.

Code:

Sub UCase_MsgBox()

Dim text As String

text = UCase(Range("B4"))

MsgBox text

End Sub

• You need to insert 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)

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.
• Run the code by pressing Alt + F8 and you will find the string in uppercase.

Read More: Excel VBA MsgBox Function 

Example 2 – Uppercase String in Cells

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

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 (2^nd column 4^th row).

We have the string at the C4 cell and we want to set the uppercase of the string at the C5.

We are using a 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.

Example 3 – Uppercase a Specific Range of Strings

We have a dataset of a few names all in lowercase.

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

We didn’t use any string variable to store the converted string, using the UCASE function to 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.

Read More: How to Use VBA Str Function in Excel

Example 4 – Select the Cell to Convert to Uppercase

The dataset below has some widely used proverbs.

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

• We have created a Button and named it Uppercase.

• Insert this code into the 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.

• Select a cell and click the button Uppercase.

• You will find the string in uppercase.

• You can select a range of cells also.

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

Read More: How to Use VBA Case Statement

Quick Notes

You can provide a string that 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.

You can 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.

Practice Workbook

Related Articles

• How to Use Or Function in Excel VBA
• How to Call a Sub in VBA in Excel
• Use VBA Asc Function
• How to Use VBA Function Procedure with Arguments in Excel