Programming Excel with VBA and .NET

Convert Strings

I touched on two very common conversion functions already: LCase and UCase convert a string to lower- or uppercase, usually because you want to ignore case while comparing strings. Your computer can perform these conversions and comparisons because it actually stores strings as numbers using something called ANSI character codes .

The Asc function converts characters to their numeric ANSI character codes; Chr converts those numeric codes back to characters. The following code displays the ANSI character codes in the Immediate window (Figure 3-2):

    Sub ShowAnsiCodes( )
        Dim i As Integer, str As String
        For i = 0 To 255
            str = str & i & ": " & Chr(i) & vbTab
            If i Mod 10 = 0 Then
                Debug.Print str
                str = ""
            End If
        Next
    End Sub

Tip

Not all character codes have an appearance. Chr(0), Chr(9), Chr(10), and Chr(13) represent the null, tab, line-feed, and carriage-return characters respectively.

Looking at Figure 3-2, you can see that you can convert individual characters from upper- to lowercase by adding 32 or from lower- to uppercase by subtracting 32. UCase and LCase just make those conversions easier.

The StrConv function is related to UCase and LCase. It can perform the same conversions, plus it can convert the words in a string to use initial capitalization as is used in proper names:

    ' Displays St. Thomas Aquinas
    Debug.Print StrConv("st. thomas aquinas", vbProperCase)

Tip

StrConv also converts strings to or from other encodings or locales. Those are pretty advanced topics and ...