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.
To download the workbook click the link below.
The VBA UCASE Function
1. Basics of VBA UCASE
The VBA UCASE function in Microsoft Excel converts the provided string into an all uppercase text.
string: A text value that is to be converted into upper case.
Workable from Excel 2003.
2. Uses of VBA UCASE
I. 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 at the B3 cell of our practice sheet.
You can see the string in lowercase. Now we are going to convert the string into uppercase. And our code will be
Sub UCase_MsgBox() Dim text As String text = UCase(Range("B3")) 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 uppercased string in this variable.
To introduce the Cell Reference we need to use Range and there we have set B3. The UCASE function then will convert the string stored in B3.
We have used MsgBox to return the output through a message box.
Run the code and you will find the string in uppercase.
II. 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. And our code will be
Sub UCase_Cells() Dim text As String text = UCase("we are learning vba ucase") Cells(3, 2).Value = text End Sub
Here we have used Cells that take two parameters; row_index and column_index. We have inserted 3 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 B3 (Second column third row).
Without taking the direct input we can also perform our task. For example, let’s imagine we have the string at the C3 cell and we want to set the uppercase of the string at the C4.
Just for a change, we are using Range. It’s also helpful as you can explicitly insert your desired Cell Reference without any confusion.
Sub UCase_Range() Dim text As String text = UCase(Range("C3")) Range("C4").Value = text End Sub
Here 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.
- 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
III. Uppercase a Specific Range of String
Using the UCASE function we can convert the string from any specific range of cells at one go. To show you examples we have brought a dataset of a few famous names all in lowercase.
We are set to convert all the strings into uppercase and our code will be the same as the one below
Sub UCase_EntireRange() Dim i As Integer For i = 4 To 9 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 range correctly. You can see the names in our dataset are in B4 to B9 cells. That’s why we have used 4 to 9 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.
IV. 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.
Our example dataset 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 as Upper Case. Now insert the code into the Button.
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 Upper Case
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.
3. Quick Notes
- You can provide a string, contains uppercase texts
The uppercase will remain as it is, the function will only convert the lowercase texts.
- If you insert an empty string in the function,
you will find the empty string in return.
That’s all for today. We have tried showing 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.