wimgielis.com

Changing the case of letters

• Dec. 29, 2013

Introduction

Small VBA code snippets and built-in functionality can help us to change the case of the text in cells: to UPPERCASE, to lowercase, Alternating Uppercase And Lowercase, or Only tghe first letter has uppercase. In the VBA code snippets the user can be asked to make his/her choice. The basis for our text conversions is the StrConv function in VBA. You can achieve the same result with native Excel functions, although it could take more effort. In Excel, use the functions UPPER, lower en Proper. As an aside, this functionality of changing the case is also available if you download the ASAP utilities.

The, admittedly not so interesting texts below, should be converted automatically to a different case. This dummy text originates from the web page lipsum (lorem ipsum). We are dealing we fixed (constant) text values rather than formaulas.

Inputbox

Through an inputbox the user determines how the conversion will be done, with 1 out of 4 choices:

• option 1: uppercase
• option 2: lowercase
• option 3: propercase
• option 4: only uppercase for the first letter of the cell

Sub ChangeCase_Inputbox()
'''''
' VBA-code to change the case of texts
' 29/12/13
'''''
    Dim Rng As Range, iModus As Integer
    Do Until iModus >= 1 And iModus <= 4
        iModus = Application.InputBox("Please choose 1 for UPPERCASE, 2 for lowercase,
                        3 for Proper Case, 4 for First letter only uppercase", "Case selection", 0, Type:=1)
    Loop

    On Error Resume Next
    For Each Rng In Selection.SpecialCells(2, 2).Cells
        If iModus = 4 Then
            Rng.Value = UCase$(Left(Rng.Text, 1)) & Mid$(Rng.Text, 2)
        Else
            Rng.Value = StrConv(Rng.Text, iModus)
        End If
    Next

End Sub

You can see that we do not need an awful lot of code for these conversions, even not when we offer the user 4 different possibilities for conversions. This has to do with the StrConv function, that does the heavy-lifting for us with arguments 1, 2 and 3. Only choice number 4 is somewhat less straightforward.

Toggling the case automatically

We can now move beyond the prior code. Let’s have code that determines what type of letters we have in the first cell of the selection. A macro can, with that information, toggle automatically between the 3 scenarios:

• if we have uppercase in the first cell, the selection will be changed to lowercase
• if we have lowercase in the first cell, the selection will be changed to propercase
• if not, the selection will be changed to uppercase

Sub ChangeCase_Intelligence()
'''''
' VBA-code to switch the case of texts automatically, inspecting existing case
' 29/12/13
'''''
    Dim Rng As Range, iModus As Integer
    On Error Resume Next
    With Selection.SpecialCells(2, 2)
        Select Case True
        Case .Cells(1).Text = UCase$(.Cells(1).Text): iModus = 2
        Case .Cells(1).Text = LCase$(.Cells(1).Text): iModus = 3
        Case Else: iModus = 1
        End Select

        For Each Rng In .Cells
            Rng.Value = StrConv(Rng.Text, iModus)
        Next
    End With

End Sub

Beware to not incorporate Option Compare Text at the top of the module. In that case, a = A, b = B, etc., such that the code can not discriminate between the different possibilities. Either leave it out, either use Option Compare Binary op.

Switch in a random way

To end this blog article, we use randomness to decide how we do the case. The Rnd function draws a random number between 0 and 1.

Sub ChangeCase_Random()
'''''
' VBA-code to switch the case of texts in a random way
' 29/12/13
'''''
    Dim Rng As Range, iModus As Integer
    iModus = Int(Rnd() * 4) + 1

    On Error Resume Next
    For Each Rng In Selection.SpecialCells(2, 2).Cells
        If iModus = 4 Then
            RRng.Value = UCase$(Left$(Rng.Text, 1)) & LCase$(Mid$(Rng.Text, 2))
        Else
            Rng.Value = StrConv(Rng.Text, iModus)
        End If
    Next

End Sub

• « Previous Excel article
• Next Excel article »