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:
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
See Also:
- Referencing Ranges in Excel Using VBA
- VBA Excel String Processing and Manipulation
- Using Chr() as an Escape Character
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