Excel VBA Chr() Function

In this article I will explain about the Chr() function. The Chr() function is a very useful function when working with strings and Excel columns.

Generally speaking, numbers and characters are mapped. Meaning that each character is mapped to a number and vice versa. For example the character “A” is mapped to the number 65 and the number 65 to the characer “A”.  The character C is mapped to the number 67 and the number 67 to the character “C” …

The Chr() function takes as input a number and returns the corresponding character. Below I have brought 2 example where this function becomes very useful. You can download the code and files related to this article here:

Jump To:

Example 1:

The Chr() function becomes very useful when you want to reference ranges using the “A1:B1” notation. For more information about referencing ranges using the “A1:B1” notation please see Referencing Ranges Using the “A1:B1” notation. Lets say you have a column of data and you want to transpose this column into a row of data using the range object with the “A1:B1” notation:

Excel, VBA, Chr, Input Column of Data

In achieving this I will use the function Get_Alphabet()Get_Alphabet() is a function I’ve written. it takes as input the numeric index of a column and returns the alphabetic index of the column:

'''
'Returns the alphabet associated with the column
'intNumber: The column number
'Return Value: Alphabet associated with the column number
Private Function Get_Alphabet(ByVal intNumber As Integer) As String
Get_Alphabet = Strings.Trim(Chr(intNumber + 64))
End Function

Some Examples:

MsgBox (Get_Alphabet(1))

Result:

Character Index of Column 1

MsgBox (Get_Alphabet(23))

Result:

Character Index of Column 23, Excel VBA

Using the function Get_Alphabet(), the code below transposes the column of data above into a row of data:

Sub Example3()
Dim i As Integer
Dim flag As Boolean
i = 2
flag = True
While flag = True
    'check to see if the end of the column has been reached
    If Cells(i, 1) <> "" Then
        Range(Get_Alphabet(i + 1) + Strings.Trim(Str(1))) = Cells(i, 1)
        i = i + 1
    Else
        'if the end has been reached abort the loop
        flag = False
    End If
Wend
End Sub

For more information about the String.Trim() and Str() functions please see VBA Excel String Processing and Manipulation. The result of the code above can be seen below:

Transposed Column to Row Excel, VBA

Note: This code was only to illustrate the use of the Chr() function while referencing ranges using the “A1:B1” notation. For other methods of referencing ranges and cells please see Referencing Ranges in Excel Using VBA.


Example 2:

In this second example I will explain how you can use the Chr() function for creating strings with unique characters. Lets say we have the following data  in column 1:

Excel, Column 1 with Data

What we want to do is generate column 2 with the following data:

Excel, VBA, Chr, Column to generate

How it works is that column 1 has numbers. For the first occurrence of the each number the string “X00-A” will be generated, where X is the number in column 1. On the next occurrences of that number the strings “X00-B”, “X00-C”, “X00-D”, … will  be generated respectively y. The code below achieves this:

Sub Example4()
Dim i As Integer
Dim j As Integer
Dim flag As Boolean
Dim flagSame As Boolean

i = 2
flag = True
'this loop keeps going until the last row has been reached
While flag = True
    j = 1
    flagSame = True
    'this loop keeps going until the number in column 1 changes
    While flagSame = True
        'checks if the last row has been reached
        If Cells(i + j - 1, 1) <> "" Then
           Cells(j + i - 1, 2) = Strings.Trim(Str(Cells(j + i - 1, 1))) + "00-" + Get_Alphabet(j)
           'checks if the number in column 1 has been changed
           If Cells(i + j, 1) = Cells(i + j - 1, 1) Then
            'goes to the next row
            j = j + 1
           Else
            i = i + j
            j = 1
            flagSame = False
           End If
        Else
           flag = False
           flagSame = False
        End If
    Wend
Wend
End Sub

See Also:

You can download the code and files related to this article here.

If you need assistance with your code, or you are looking to hire a VBA programmer feel free to contact me. Also please visit my website www.software-solutions-online.com

Leave a Reply

Your email address will not be published. Required fields are marked *