EDUCBA

Home VBA VBA Resources VBA String and Text Functions VBA UCASE
 

VBA UCASE

Madhuri Thakur
Article byMadhuri Thakur

Updated May 31, 2023

VBA UCASE

 

 

VBA UCASE

While working with the alphabet, we know there are two ways to write an alphabet. Lower case and Upper Case. Sometimes we need to change the text format, which 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 to upper case, there is a function in Excel VBA called UCASE.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

The string we give as input can be taken from a user, a single cell, or a range of cells. It converts all the strings 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 :

Syntax of UCASE

Here target string is the string or set of characters that we want to change from lower to upper case. VBA Ucase function only changes the text into an upper case; it does not change any formatting 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 a few examples in Excel.

You can download this VBA UCASE Excel Template here – VBA UCASE Excel Template

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,

Example 1-1

Note: Always remember to enable the developer’s tab from the files tab and then from the options section to use VBA in Excel.

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

Step 1: We must click visual basic in the developer’s tab to get into VBA.

VBA UCASE Example 1-2

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

VBA UCASE Example 1-3

Step 3: Now we know we must declare a macro name using a sub-function.

Code:

Sub Sample()

End Sub

VBA UCASE Example 1-4

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

VBA UCASE Example 1-5

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

Code:

Sub Sample()

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

End Sub

VBA UCASE Example 1-6

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

Result of Example 1-7

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

VBA UCASE Function – Example #2

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

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

Step 1: We must click visual basic in the developer’s tab to get into VBA.

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

Step 3: Start by declaring another subfunction.

Code:

Sub Sample1()

End Sub

VBA UCASE Example 2-1

Step 4: Declare two variables as a string.

Code:

Sub Sample1()

Dim A, B As String

End Sub

VBA UCASE Example 2-2

Step 5: Take input from the user 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

VBA UCASE Example 2-3

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

VBA UCASE Example 2-4

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

VBA UCASE Example 2-5

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.

Result of Example 2-6

VBA UCASE Function – Example #3

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

xample 3-1

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

Step 1: We must click visual basic in the developer’s tab to get into VBA.

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

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

Code:

Sub Sample2()

End Sub

VBA UCASE Example 3-2

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

VBA UCASE Example 3-3

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

Code:

Sub Sample2()

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

End Sub

VBA UCASE Example 3-4

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

Result of Example 3-5

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

VBA UCASE Function – Example #4

In the above examples, we changed the text from lower 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. I want to change the data in column A from lower case to upper case in column B.

Example 4-1

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

Step 1: We must click visual basic in the developer’s tab to get into VBA.

Step 2: Click on the 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

VBA UCASE Example 4-2

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

Code:

Sub Sample3()

Worksheets("Sheet2").Activate

End Sub

VBA UCASE Example 4-3

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

Code:

Sub Sample3()

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

End Sub

VBA UCASE Example 4-4

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

VBA UCASE Example 4-5

We have declared variable A from 2 to 6 because from the 2nd to 6th 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

VBA UCASE Example 4-6

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

Result of Example 4-7

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.
  • It remains unchanged if the input string has any special characters or symbols.

Recommended Articles

We hope that this EDUCBA information on “VBA UCASE Function” was beneficial to you. You can view EDUCBA’s recommended articles for more information,

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