EDUCBA

Home » VBA » Blog » VBA String and Text Functions » VBA UCASE

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.

Watch our Demo Courses and Videos

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

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

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 developer’s tab from the files tab and then from the options section to use VBA in excel.

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.

VBA UCASE Example 1-2

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

VBA UCASE Example 1-3

Step 3: Now we know that we need to declare a macro name by using 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 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 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

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

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 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,

xample 3-1

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

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

Example 4-1

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

VBA UCASE Example 4-2

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

VBA UCASE Example 4-3

Step 5: Declare a variable A as 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 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.
  • 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
1 Shares
Share
Tweet
Share