Changing Text Case in the Excel worksheet using VBA

This sub will help speed up the process of changing text case on your worksheets. As you know, there are at least four different text cases (if you know others, do let me know).

UPPER Case
lower case
Proper Case
Sentence case

I have affected the above text accordingly, to demonstrate each case. Normally I recommend using a helper column and putting the case formulas directly onto the worksheet, rather than a VBA solution, but if you are pressed for time or already doing text manipulation in your code (for example, as part of a userform), this may help.

The first thing I'll do at the top of a new standard module is set up a global boolean variable. I do this in case I want to use this code in another procedure, to test if it was successful. It's a perfect example of modular programming and making smaller, standalone functions which can be easily cut and pasted into new projects. I'll demonstrate a sample of this below, after showing you the main code.

Dim bWasDone As Boolean

And here is the code. it accepts two arguments:

A string variable representing a valid range
A number representing the Case option. This is optional, but if you don't specify a case, it assumes you want upper case.

Sub FixText(rng As String, Optional lType As Long)
'
' lType can be any one of these parameters:
' 1 - Convert to UPPER CASE
' 2 - Convert to lower case
' 3 - Convert to Proper Case
' 4 - Convert to Sentence case
'
Dim rngSource As Excel.Range
Dim cell As Excel.Range

Application.ScreenUpdating = False

' test for valid range
On Error Resume Next
Set rngSource = Range(rng)
On Error GoTo 0

If TypeName(rngSource) = "Nothing" Then
    bWasDone = False
    GoTo ExitProc
End If
    
If TypeName(rngSource) <> "Range" Then
    bWasDone = False
    GoTo ExitProc
End If

' if ltype was left blank, do upper case by default
If lType = 0 Then lType = 1

Select Case lType
    Case 1 ' upper case
        For Each cell In rngSource.SpecialCells(xlCellTypeConstants, 2)
            cell = UCase$(cell)
        Next cell
    Case 2 ' lower case
        For Each cell In rngSource.SpecialCells(xlCellTypeConstants, 2)
            cell = LCase$(cell)
        Next cell
    Case 3 ' proper case
        For Each cell In rngSource.SpecialCells(xlCellTypeConstants, 2)
            If Len(cell) > 2 Then
                cell = StrConv(cell, vbProperCase)
            End If
        Next cell
    Case 4 ' sentence case
' code taken from http://vbamacros.blogspot.com/2007_09_01_archive.html
        For Each cell In rngSource.SpecialCells(xlCellTypeConstants, 2)
            s = cell.Value
            Start = True
            For i = 1 To Len(s)
                ch = Mid$(s, i, 1)
                Select Case ch
                    Case "."
                        Start = True
                    Case "?"
                        Start = True
                    Case "a" To "z"
                        If Start Then ch = UCase$(ch)
                        Start = False
                    Case "A" To "Z"
                        If Start Then
                            Start = False
                        Else
                            ch = LCase$(ch)
                        End If
                    End Select
                Mid$(s, i, 1) = ch
            Next i
            cell.Value = s
        Next cell
    Case Else ' default to upper case
        For Each cell In rngSource.SpecialCells(xlCellTypeConstants, 2)
            cell = UCase$(cell)
        Next cell
End Select

bWasDone = True

ExitProc:
Set rngSource = Nothing
Application.ScreenUpdating = True
End Sub

First the code checks to make sure it was passed a valid range. It tries to set the string to a Range object variable. If it didn't work, we exit the procedure. If the lType argument (optional) was left out, or if someone tries to be funny and types something else there, we default to upper case (1). Then depending on what was typed, we loop through each Constant cell in the range and perform the case changing procedure.

Notice that I use a For Each loop, this is faster than a counter loop (ex: For i = 1 to rngSource.Cells.count).

We use the SpecialCells property to limit the cells we act on to just the constants (text). The number 2 after 'xlCellTypeConstants' means the second option from the Go To Special dialog box, after you click 'Constants'.

Also notice that I use the string versions of Mid, UCase, LCase. If you check the help for these functions, it says "Returns a Variant (String)" - if you see this, that means you can append a '$' to the end of the function. Since we want these functions to return a string (not a variant), we use the '$' versions to make the code run significantly faster, because VBA isn't converting the String to Variant and back to a String.

The end result is significantly streamlined, compact and fast code.

If you wanted to keep this code hidden from others, there is no need to use the Private keyword in the first line of the macro, because any macro with arguments is automatically made Private, even if all of the arguments are Optional. Otherwise, how would you pass the arguments to it?

Here is a sample showing how to implement the code. Since the range must be passed as a string, it must be in quotes. Remember:

FixText "A1:A10"

and

FixText "A1:A10", 1

are the same.

Sub TestOutMyNewMacro()

FixText "A1:A10", 4

End Sub

And if I wanted to use the global boolean variable I mentioned above, it would go something like this:

Dim bWasDone As Boolean
Sub TestOutMyNewMacro()

FixText "A1:A10", 4

If bWasDone = True Then
    MsgBox "It worked!"
Else
    MsgBox "It failed."
End If

End Sub

When FixText is done, it will have set the bWasDone variable to False if it failed, otherwise it was set to True. Since it is declared at the top of the module, outside of any procedure, as a global variable it retains its value after the FixText procedure ends, and it is available to be checked by the TestOutMyNewMacro procedure. Keep in mind all three sets of code (the global boolean variable bWasDone, the FixText procedure, and the TestOutMyNewMacro procedure were all located in the same module, for demonstration purposes.

Changing Text Case in the Excel worksheet using VBA

LAST UPDATED: March 26, 2008