What is the CHR function in Excel


VBA CHR function

The VBA CHR function is categorized under Text / String Function.

Each character in Excel has a specific assigned numerical code (ASCII code) for itself. The VBA Chr function returns or results in a string that contains the character associated with the specified character code (also known as an ASCII value). ASCII stands for American Standard Code for Information Interchange

ASCII_Value: This is a standard for the relationship between a number of values ​​and a character type.

The ASCII value should be in the range of or between 0 and 255, which are divided into 3 types.

  • Between 0 and 31 there is an ASCII control character OR a non-printable control code.
  • The range from 32 to 127 includes printable ASCII characters.
  • The range from 128 to 255 is an extended ASCII code.

Syntax of CHR in Excel VBA

After typing Chr, click the space bar. The syntax shown below is displayed.

Chr (ascii_value) or Chr (charcode)

It contains an input parameter or an argument (mandatory and required). The charcode argument is a long that identifies a character. This results in printable and non-printable characters.

Mapping of characters with their ASCII values (each code is explained in the Excel file _ CHARACTER CODE DESCRIPTION SHEET)

1 - 958:87W.116t145'174®203Ë232è
11 - 3160<89Y.118v147176°205I234ê

Using the CHR function in Excel VBA

Below are the various examples of using the CHR function in Excel using VBA code.

You can download this VBA CHR Excel Template here - VBA CHR Excel Template

Now let's test this CHR function with a few examples and learn how they work.

VBA CHR function - example # 1

Step 1: Select or click Visual Basic in the Code group on the Developer tab, or click the Alt + F11 keyboard shortcut directly.

Step 2: Now you can see the VB editor window. Under the project window you can see the listed work file in the VBA project (i.e. Sheet1 (VB_CHR)

To create a blank module, right-click Sheet 1 (VB_CHR) under Microsoft Excel Objects, click Insert, and select Module to create a new blank module.

VBA CHR function - example # 2

Step 1: After the blank module is created, it is also known as the code window where you can start writing VBA CHR function instruction codes.


Sub CHRAC () Sheet1.Range ("D6") = CHR (77) End Sub

Step 2: The VBA code mentioned above gives an uppercase letter M. in cell "D6" or leads to this, ie for the ASCII value 77.

VBA CHR function - example # 3

Step 1: Start another sub-procedure as follows:


Sub CHRAC1 () End Sub

Step 2: Similarly, multiple character codes in the cell can be obtained using the code below


Sub CHRAC1 () Sheet1.Range ("D9") = CHR (37) Sheet1.Range ("D11") = CHR (47) Sheet1.Range ("D13") = CHR (57) Sheet1.Range ("D15" )) = CHR (128) End Sub

Step 3: Let's run the code above. Once you run the above code, you can find a character code in the excel file.

  • CHR (37) returns a% (percent sign) in a cell "D9" while
  • CHR (47) returns / (slash / divide symbol) in cell "D11"
  • CHR (57) returns a numeric value 9 in cell "D13" &.
  • CHR (128) returns a euro symbol in cell "D11"

VBA CHR function - example # 4

For example, suppose I want to include a double quote for the middle name, i.e. MANI, in the example below

I can use the VBA-CHR function for this.

Step 1: As soon as I run the code below with the CHR code 34, a double quotation mark is inserted in the middle name.


Sub CHRAC2 () Sheet3.Range ("C4") = "MANI" & CHR (34) & "RAM" & CHR (34) & "KUMAR" End Sub

Step 2: Let's run the code above by clicking the play button and seeing the result in the below given output in cell "E19".

VBA CHR function - example # 5

In the example below, let's say I want a branding symbol for the company; B. GOOGLE, add

For this I can use the VBA CHR function with the ASCII code "153".

Step 1: As soon as I run the code given below with the CHR code 153, the TM- Icon showing the word GOOGLE.


Under CHRAC3 () Sheet4.Range ("C3") = "GOOGLE" & CHR (153) End Sub

Step 2: The above code results in the below mentioned output in cell "K18"

Save your workbook as an "Excel Macro-Capable Workbook". Click Save As in the left corner of the worksheet. When you open a file again, you can click the keyboard shortcut Fn + Alt + F8 click. The Macro dialog box appears. There you can execute a stored macro code of your choice or click on Fn + Alt + F11 click a full macro window.

Things to remember

If you enter an invalid number in the Chr function, e.g. B. a number in a range less than 0 or greater than 255 , the error is output, e.g. B. a runtime error.

Recommended articles

This is a guide to VBA CHR. Here we discuss how to use the Excel VBA CHR function along with practical examples and a downloadable Excel template. You can also go through our other item suggestions -

  1. Copy paste function in VBA
  2. Sub-string Excel function
  3. VBA index out of range
  4. Excel ISNUMBER Formula