Shakil Ahmed
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.
Practice Workbook
To download the workbook click the link below.
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
UCase(string)
- Arguments
| Argument | Explanation |
|---|---|
| string | A text value that is to be converted into upper case. |
- Versions
Workable from Excel 2003.
Uses of VBA UCASE
1. Uppercase String in Message Box
From the description of the function, you might have understood that the UCASE function returns a string in an uppercase form.
Let’s have a string “we are learning vba ucase” which is stored in the B4 cell of our worksheet.
You can see the string in lowercase. Now we are going to convert the string into uppercase.
Code:
Sub UCase_MsgBox()
Dim text As String
text = UCase(Range("B4"))
MsgBox text
End Sub- You need to write 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.
- Now, Run the code by pressing ALT+F8 and you will find the string in uppercase.
Read More: How to Create VBA Custom Function in Excel (2 Easy Methods)
2. Uppercase String in Cells
In the earlier section, we have seen the output through a message box. We can find the result at any of the cells in an Excel sheet.
Let’s use a direct input in the UCASE function and write code to see the output at a cell of the Excel sheet.
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
Here 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 (2nd column 4th row).
Without taking direct input we can also perform our task. For example, let’s imagine we have the string at the C4 cell and we want to set the uppercase of the string at the C5.
Just for a change, we are using 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 SubHere 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.
Read More: How to Create Custom VBA functions and Use them in the Worksheet
Similar Readings
- How to use VBA SPLIT function in Excel (5 Examples)
- Use TRIM Function in VBA in Excel (Definition + VBA Code)
- How to Create a Body Mass Index (BMI) Calculator in Excel Using VBA
- Excel VBA ASC() Function – Get ASCII Value of Character
- VBA Date Function (12 Uses of Macros with Examples)
3. Uppercase a Specific Range of String
Using the UCASE function we can convert the string from any specific range of cells in one go. To show you examples we have brought a dataset of a few names all in lowercase.
We are set to convert all the strings into uppercase.
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
Here we didn’t use any string variable to store the converted string rather go for using the UCASE function and 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.
Here we have the loop and the parameter values of Cells as per our circumstances, you need to change them according to your dataset.
Read More: How to Use VBA Str Function in Excel (4 Examples)
4. Select Cell to Convert Uppercase
We can write the macro code in such a way that the value from our selected cell or range of cells is converted into uppercase.
The dataset below has some widely used proverbs.
Our approach 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.
Here we have brought a Button and named it Uppercase.
- Now insert the code into that 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.
- Now select any particular cell and Click the button Uppercase.
- You will find the string in uppercase (The image below).
- You can select a range of cells also.
- Select the rest of the cells and click the button.
You will find all the strings in uppercase.
Read More: How to Use VBA Case Statement (13 Examples)
Quick Notes
- You can provide a string, 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.
- If you 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.
Read More: How to Use VBA Function Procedure with Arguments in Excel
Conclusion
That’s all for today. We have tried showing you how you can use the VBA UCASE function. You can use the function to convert the string into uppercase. Hope you will find this helpful.
Feel free to comment if anything seems difficult to understand. Let us know any of your UCASE function-related scenarios where you have stuck, we are ready to help.
