Using the UCase Function in Excel VBA
There may be a time in Excel that you need data that you are fetching from an Excel spreadsheet or importing from external data to be amended to upper case. This can, of course, be done manually, but if it is a large amount of data, you may want to automate it using the
UCase Function that is contained within VBA.
Using UCase in an individual procedure to amend text
The syntax of the UCase function in Excel VBA is:
For demonstration purposes, we can create a sub routine in Excel to convert some inputted data into upper case.
Sub ConvertCase() Dim strName As String Dim strUName As String strName = "fred smith" strUName = UCase(strName) MsgBox strUName End Sub
If you run this routine, the following message box will appear:
Using UCase in a procedure to amend text in an Excel worksheet
In the following example, we are going to get the value from an Excel cell, and then convert the value to upper case. We will then return the value to the Excel cell.
Sub TestCase() 'declare the variables Dim strName As String Dim strName2 As String 'populate the first variable with the value in the cell strName = ActiveSheet.Range("A1") 'convert to upper case strName2 = UCase(strName) 'return the new value to the cell ActiveSheet.Range("A1") = strName2 End Sub
- The variables are declared and the first variable gets the information from the Excel cell.
- The next variable then converts the string to upper case.
- The information is then returned to the cell.
Changing text in a cell to proper case
If we wanted to use an Excel function to amend text in a cell to Proper Case, we could use the
PROPER function that is built into Excel.
However, VBA does not have this function. To convert a string to proper case in VBA, we need to use the
Sub TestCase() 'declare the variables Dim strName As String Dim strName2 As String 'populate the first variable with the value in the cell strName = ActiveSheet.Range("A1") 'convert to upper case strName2 = StrConv(strName, vbProperCase) 'return the new value to the cell ActiveSheet.Range("A1") = strName2 End Sub
When you use the
StrConv method, VBA will use Intellisense to give you a list of options that are available to the method.
The syntax for the
StrConv method is therefore:
Conversion arguments are required, while the
LocalID is optional as it has square brackets around it.
The options available for conversion are as follows:
|vbUpperCase||Converts the string to uppercase characters.|
|vbLowerCase||Converts the string to lowercase characters.|
|vbProperCase||Converts the first letter of every word in string to uppercase.|
|vbWide*||Converts narrow (single-byte) characters in string to wide (double-byte) characters.|
|vbNarrow*||Converts wide (double-byte) characters in string to narrow (single-byte) characters.|
|vbKatakana**||Converts Hiragana characters in string to Katakana characters.|
|vbHiragana**||Converts Katakana characters in string to Hiragana characters.|
|vbUnicode||Converts the string to Unicode using the default code page of the system.|
|vbFromUnicode||Converts the string from Unicode to the default code page of the system.|
*Applies to East Asia locales.
**Applies to Japan only.
Checking to see the characters in a cell are in upper case
A useful procedure to create in Excel might be to check to see if a character in a cell is in upper case. For example, you may have a list of names and want to make sure each name starts with a capital letter, and if doesn’t, convert it to a capital letter.
You would need to create an Excel function to check if a character is in upper case
Function CheckCase(strC as string) as Boolean If UCase(strC)= strC then CheckCase = True Else CheckCase = False End If End Function
To use this function, you can call it from an Excel cell.
Using Upper function in a formula in an Excel worksheet
You can also use a built-in Excel function in your worksheet to amend the text in your cell to upper case.
To amend the data in a cell in Excel to upper case, you can use the
- In B1 type “=”
- You can type the word UPPER and then open a bracket.
- Click on the text you wish to convert – in this case A1, and then close the bracket.
- Your text will then appear in upper case.
Similarly, you can use the
LOWER functions for proper case and lower case.
This article has just touched on how you can utilize string functions in Excel and Excel VBA to manipulate your data. They are extremely useful when you have large amounts of data that need amending. VBA automation can make a tedious manual task into a quick and easy procedure.