VBA UCASE

VBA UCASE

While working with alphabet we know that there are two ways to write an alphabet. Lower case and Upper Case. Sometimes we need to change the format of text means lower case to upper case or upper case to lower case. For a larger amount of text, we cannot do it manually every time. Excel gives us a function to do the same. To change the text from lower case to upper case there is a function in Excel VBA called UCASE.

The string we give as input can be taken from a user or can be a single cell or a range of cells. It converts all the string to an upper case not only the first string.

As explained above UCASE changes the lower case characters to upper case in VBA.

Syntax of UCASE Function in Excel VBA

UCASE function has the following syntax in Excel VBA :

Here target string is the string or set of characters which we want to change from lower case to upper case. VBA Ucase function only changes the text into an upper case it does not change any of the formatting done in the cell or does not change the special symbols in the text. The target string can be a single cell or it can be a range of cells.

For example, if we input Ucase ( anand ) the result we will have is ANAND. Also if we have another example like this Ucase ( 1 for 2 and 2 for three ) the result will be 1 FOR 2 AND 2 FOR THREE.

How to Use Excel VBA UCASE Function?

We will learn how to use a VBA UCASE function with few examples in Excel.

VBA UCASE Function – Example #1

In this example, I have a string in cell A1 and I want to change the text value to upper case. Have a look at the text below,

Follow the below steps to use UCASE function in Excel VBA.

Step 1: In the developer’s tab, we need to click visual basic to get into VBA.

Step 2: Click on insert tab and insert a module in the VBA project.

Step 3: Now we know that we need to declare a macro name by using sub-function.

Code:

Sub Sample()

End Sub

Step 4: Activate the worksheet to use its properties as our target string is in cell A1.

Code:

Sub Sample()

Worksheets("Sheet1").Activate

End Sub

Step 5: Now let us change the value in cell A1 by using Ucase function as follows.

Code:

Sub Sample()

Worksheets("Sheet1").Activate
Range("A1").Value = UCase(Range("A1"))

End Sub

Step 6: Run the above code by the run button or press F5 to see the result.

We have successfully changed the text in cell A1 to upper case.

VBA UCASE Function – Example #2

Now we will take input by a user in lower case and we will change the value to upper case.

Follow the below steps to use UCASE function in Excel VBA.

Step 1: In the developer’s tab, we need to click visual basic to get into VBA.

Step 2: Click on insert tab and insert a module in the VBA project.

Step 3: Start by declaring another subfunction.

Code:

Sub Sample1()

End Sub

Step 4: Declare two variables as a string.

Code:

Sub Sample1()

Dim A, B As String

End Sub

Step 5: Take input from the user by using an input box function and store its value in A variable.

Code:

Sub Sample1()

Dim A, B As String
A = InputBox("Write a string", "Lowercase")

End Sub

Step 6: In variable B store the value of string A when it is changed from lower case to upper case using a UCASE function.

Code:

Sub Sample1()

Dim A, B As String
A = InputBox("Write a string", "Lowercase")
B = UCase(A)

End Sub

Step 7: Display the value stored in B using the msgbox function.

Code:

Sub Sample1()

Dim A, B As String
A = InputBox("Write a string", "Lowercase")
B = UCase(A)
MsgBox B

End Sub

Step 8: Run the above code by pressing F5 and we get a prompt to give a value. Input any string.

Click on OK to see the result.

VBA UCASE Function – Example #3

Now let us test that if we have some special characters or numbers in the input string will it make any changes in it. For example, I have a string in cell C1 with some special symbols in it. Have a look at it below,

Follow the below steps to use UCASE function in Excel VBA.

Step 1: In the developer’s tab, we need to click visual basic to get into VBA.

Step 2: Click on insert tab and insert a module in the VBA project.

Step 3: Now declare a macro name by using subfunction.

Code:

Sub Sample2()

End Sub

Step 4: Activate the worksheet to use its properties as our target string is in cell C1.

Code:

Sub Sample2()

Worksheets("Sheet1").Activate

End Sub

Step 5: Now let us change the value in cell C1 by using Ucase function as follows.

Code:

Sub Sample2()

Worksheets("Sheet1").Activate
Range("c1").Value = UCase(Range("c1"))

End Sub

Step 6: Run the above code by the run button or press F5 to see the result.

We can see that UCase does not change the special symbols or characters.

VBA UCASE Function – Example #4

In the above examples, we have changed the text from lower case to upper case for a single cell. In this example, we will change the whole range of data to upper case.

In sheet 2 I have the following data. What I want to do is change the data in column A in lower case to upper case in column B.

Follow the below steps to use UCASE function in Excel VBA.

Step 1: In the developer’s tab, we need to click visual basic to get into VBA.

Step 2: Click on insert tab and insert a module in the VBA project.

Step 3: Declare a sub-function to start writing the code.

Code:

Sub Sample3()

End Sub

Step 4: To use properties of sheet 2 activate it first as the data is in sheet 2.

Code:

Sub Sample3()

Worksheets("Sheet2").Activate

End Sub

Step 5: Declare a variable A as long data type.

Code:

Sub Sample3()

Worksheets("Sheet2").Activate
Dim A As Long

End Sub

Step 6: Use for loop to change the data in each row.

Code:

Sub Sample3()

Worksheets("Sheet2").Activate
Dim A As Long
For A = 2 To 6

End Sub

We have declared variable A from 2 to 6 because from 2^nd to 6^th row we have the data in the sheet.

Step 7: Now change the value in column A to upper case and store it in column B by the following Code.

Code:

Sub Sample3()

Worksheets("Sheet2").Activate
Dim A As Long
For A = 2 To 6
Cells(A, 2).Value = UCase(Cells(A, 1).Value)
Next A

End Sub

Step 8: Run the above code by pressing F5 to see the result in sheet 2 as follows,

Things to Remember

• VBA Ucase function changes the text to upper case.
• The string we give as an input can be a single cell or multiple cells.
• The string we give as input can have multiple strings in it.
• If the input string has any special characters or symbols it remains unchanged.

Recommended Articles

This has been a guide to VBA UCASE Function. Here we discussed on how to use Excel VBA UCASE Function along with practical examples and downloadable excel template. You can also go through our other suggested articles –

1. VBA VLOOKUP
2. VBA Get Cell Value
3. VBA Left
4. VBA IsError