VBA UCase

Upper Case Function in VBA

There are situations where we need to convert some of the text values to UPPERCASE in Excel. This can be done by using an UPPER function in regular worksheet function and UCase function in VBA code.

If you are already searching for the UPPER function in VBA then you will not find it not even with Worksheet Function class. In VBA it is completely a different and short name function i.e. “UCASE”. Here “U” stands for “UPPER”, so formula reads “UPPERCASE”.

Excel VBA UpperCase Syntax

Now, look at the syntax of the Excel VBA UCASE function.

String – It is nothing but what is the text value we are trying to convert to Upper Case. This could be a direct value or cell reference as well. We will see both kinds of examples in a short while.

Examples of UCase Function in VBA

Let’s see some simple examples of VBA Ucase function to understand it better.

Example #1

Let’s try to convert the text value excel VBA to upper case text by using “UCASE” function in VBA.

Step 1: Start the sub procedure by creating the macro.

Code:

Sub UCase_Example1()

End Sub

Step 2: Declare the variable as VBA String.

Code:

Sub UCase_Example1()

Dim k As String

End Sub

Step 3: Assign the value to the variable “k” by applying the VBA “UCASE” function.

Step 4: Here string is our targeted text value that we are trying to convert to upper case and the string value is “excel vba”.

Code:

Sub UCase_Example1()

Dim k As String

K = UCase ("excel vba")

End Sub

Step 5: Let’s display the result of the variable in the VBA message box.

Code:

Sub UCase_Example1()

Dim k As String

k = UCase("excel vba")

MsgBox k

End Sub

Ok, we are done with the VBA coding part. Let’s run the macro to see the result in a message box.

So UCASE converted the text value “excel vba” to “EXCEL VBA” in a matter of a fraction of seconds.

Example #2

Let’s look at the example of using a cell reference to the function. The same text value I have entered in the cell A1.

Step 1: We will show the result in Range B1 cell, so the code will be Range (“B”).Value =

Code:

Sub UCase_Example2()

Range("B1").Value =

End Sub

Step 2: In cell B1 through UCASE function we will store the data, so open the UCASE function.

Step 3: Here string value is cell reference this time. So give the cell reference as Range (“A1”).Value.

Code:

Sub UCase_Example2()

Range("B1").Value = UCase(Range("A1").Value)

End Sub

So, done.

Run the code and see the result in the B1 cell.

Example #3

In the above example, we have seen only single cell value to be converted to upper case. Imagine If you have several names like the below image.

In these cases, we cannot keep writing the code for every single line, so we need to enclose the formula with loops. Below code will convert the above text values to upper case all at once.

Code:

Sub UCase_Example3()

Dim k As Long

For k = 2 To 8
Cells(k, 2).Value = UCase(Cells(k, 1).Value)
Next k

End Sub

This will convert all the text values to upper case from row 2 to row 8 as follows.

Imagine if you wish to convert all the selected cell values in excel to the upper case then use the below code.

Code:

Sub UCase_Example4()

Dim Rng As Range
Set Rng = Selection

For Each Rng In Selection
Rng = UCase(Rng.Value)
Next Rng

End Sub

For this code to work, first we need to select the range of cells we wish to convert to upper case, then run the macro. In the selected range only it will convert the text values to upper case characters.

You can download this VBA UCase Excel Template from here – VBA UCase Excel Template

Recommended Articles

This has been a guide to VBA UCase. Here we discuss how to convert text to Uppercase in Excel VBA along with practical examples and downloadable excel template. Below are some useful articles related to VBA –

• Count Function in VBA
• CDBL Function in Excel VBA
• Syntax of VBA Union Excel Method
• Excel VBA ArrayList
• VBA ENUM
• VBA Functions in Excel
• How to use Outlook with VBA?
• Insert Hyperlinks with VBA
• LEN in VBA