Excel VBA Chr() Function

Aug 29, 2015 by azurous in Chr

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
Example 2

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:

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:

MsgBox (Get_Alphabet(23))

Result:

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:

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:

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

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