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:

=UCase(string)

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

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:

The String and Conversion arguments are required, while the LocalID is optional as it has square brackets around it.

The options available for conversion are as follows:

Constant	Description
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 UPPER function.

1. 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 PROPER and 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.