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 we'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 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. 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.
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 (internally).
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 hidden, 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.
More sample code
Here is a lightning-fast procedure that can change case for numerous cells. I've written it to convert cells to upper case, but you can easily adapt it for proper, lower, etc.
Sub Convert_Letters_To_Upper() Dim arrData() As Variant Dim arrReturnData() As Variant Dim rng As Excel.Range Dim lRows As Long Dim lCols As Long Dim i As Long, j As Long lRows = Selection.Rows.Count lCols = Selection.Columns.Count ReDim arrData(1 To lRows, 1 To lCols) ReDim arrReturnData(1 To lRows, 1 To lCols) Set rng = Selection arrData = rng.Value For j = 1 To lCols For i = 1 To lRows arrReturnData(i, j) = UCase$(arrData(i, j)) Next i Next j rng.Value = arrReturnData Set rng = Nothing End Sub