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:
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
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
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.
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