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:
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:
Using the function Get_Alphabet(), the code below transposes the column of data above into a row of data:
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.
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:
- 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.