What is the CHR function in Excel

VBA CHR

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è
1059;88X117u146'175¯204I233é
11 - 3160<89Y.118v147176°205I234ê
3261=90Z119w148"177±206I235ë
33!62>91(120x149178²207I236I
3463?92\121y150-179³208Ð237I
35#64@93)122z151-180´209Ñ238I
36$65A94^123(152˜181µ210Ö239I
37%66B.95_124|153182211Ö240ð
38&67C.96`125)154š183·212Ö241ñ
39'68D.97a126~155184¸213Ö242Ö
40(69E.98b127156œ185¹214Ö243Ö
41)70F.99c128157186º215×244Ö
42*71G100d129158ž187»216Ö245Ö
43+72H101e130159Ÿ188¼217Ù246Ö
44,73I102f131ƒ160189½218Ú247÷
45-74J103G132161¡190¾219Û248Ö
46.75K104H133162¢191¿220Ü249ù
47/76L.105I134163£192A221Ý250ú
48077M.106j135164¤193A222Þ251û
49178N107k136ˆ165¥194A223ß252ü
50279Ö108l137166¦195A224a253ý
51380P.109m138Š167§196A225a254þ
52481Q.110n139168¨197A226a255ÿ
53582R.111Ö140Œ169©198Æ227a
54683S.112p141170ª199Ç228a
55784T113q142Ž171«200È229a
56885U114r143172201É230æ
57986V.115s144173202Ê231ç

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.

Code:

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:

Code:

Sub CHRAC1 () End Sub

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

Code:

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.

Code:

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.

Code:

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