Get Even More Visitors To Your Blog, Upgrade To A Business Listing >>

VBA CHR

Excel VBA CHR

VBA CHR Function

VBA Chr Function is categorized under Text/String function.

Each & every character in Excel has a specific assigned number code (ASCII code) to itself. VBA Chr function returns or results in String containing the character associated with the specified character code (also referred to as ASCII value). ASCII stands for American Standard Code for Information Interchange

ASCII_Value: It is a standard for the relationship between a number of value and a character type.

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

  • Between 0 to 31 range is an ASCII control character OR Non-Printing Control Codes.
  • 32 to 127 range is an ASCII printable characters.
  • 128 to 255 range is an Extended ASCII codes.

Syntax of CHR in Excel VBA

After typing Chr, click on spacebar the below-mentioned syntax appears.

Syntax VBA CHR

Chr( ascii_value ) or Chr(charcode)

It contains one input parameter or argument (Compulsory & required), Charcode argument is a Long which identifies a character. It will result in both printable and unprintable characters.

Mapping of Characters with their ASCII Values (Each code explained in Excel file _ CHARACTER CODE DESCRIPTION SHEET)

1 – 9 58 : 87 W 116 t 145 174 ® 203 Ë 232 è
10 59 ; 88 X 117 u 146 175 ¯ 204 Ì 233 é
11 – 31 60 89 Y 118 v 147 176 ° 205 Í 234 ê
32 61 = 90 Z 119 w 148 177 ± 206 Î 235 ë
33 ! 62 > 91 [ 120 x 149 178 ² 207 Ï 236 ì
34 63 ? 92 \ 121 y 150 179 ³ 208 Ð 237 í
35 # 64 @ 93 ] 122 z 151 180 ´ 209 Ñ 238 î
36 $ 65 A 94 ^ 123 { 152 ˜ 181 µ 210 Ò 239 ï
37 % 66 B 95 _ 124 | 153 ™ 182 211 Ó 240 ð
38 & 67 C 96 ` 125 } 154 š 183 · 212 Ô 241 ñ
39 68 D 97 a 126 ~ 155 184 ¸ 213 Õ 242 ò
40 ( 69 E 98 b 127  156 œ 185 ¹ 214 Ö 243 ó
41 ) 70 F 99 c 128 157  186 º 215 × 244 ô
42 * 71 G 100 d 129  158 ž 187 » 216 Ø 245 õ
43 + 72 H 101 e 130 159 Ÿ 188 ¼ 217 Ù 246 ö
44 , 73 I 102 f 131 ƒ 160 189 ½ 218 Ú 247 ÷
45 74 J 103 g 132 161 ¡ 190 ¾ 219 Û 248 ø
46 . 75 K 104 h 133 162 ¢ 191 ¿ 220 Ü 249 ù
47 / 76 L 105 i 134 163 £ 192 À 221 Ý 250 ú
48 0 77 M 106 j 135 164 ¤ 193 Á 222 Þ 251 û
49 1 78 N 107 k 136 ˆ 165 ¥ 194 Â 223 ß 252 ü
50 2 79 O 108 l 137 166 ¦ 195 Ã 224 à 253 ý
51 3 80 P 109 m 138 Š 167 § 196 Ä 225 á 254 þ
52 4 81 Q 110 n 139 168 ¨ 197 Å 226 â 255 ÿ
53 5 82 R 111 o 140 Œ 169 © 198 Æ 227 ã
54 6 83 S 112 p 141  170 ª 199 Ç 228 ä
55 7 84 T 113 q 142 Ž 171 « 200 È 229 å
56 8 85 U 114 r 143  172 ¬ 201 É 230 æ
57 9 86 V 115 s 144  173 202 Ê 231 ç

How to Use CHR Function in Excel VBA?

Below are the different examples to use CHR Function in Excel using VBA code.

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

Now let us test this CHR Function through some examples and learn how they work.

VBA CHR Function – Example #1

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

VBA Char Example 1-1

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

VBA CHR Module Example 1.2

To create a blank module, under the Microsoft excel objects, right-click on sheet 1(VB_CHR) click on Insert and select Module so that a new blank module gets created.

VBA CHR Function – Example #2

Step 1: Now the blank module is created, it is also called a code window, where you can start writing VBA CHR function statement codes.

Code:

Sub CHRAC()

Sheet1.Range("D6") = CHR(77)

End Sub

 Example 2.1

Step 2: The above-mentioned VBA code returns or results in an Upper-case alphabet M in “D6” cell, i.e. for the ASCII value 77.

Example 1.2

VBA CHR Function – Example #3

Step 1: Start another subprocedure as follows,

Code:

Sub CHRAC1()

End Sub

Example 3.1

Step 2: Similarly, multiple character codes can be obtained in the cell with the help of below-mentioned code

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

VBA CHR Example 3.2

Step 3: Let us execute the above code. Once you run the above-mentioned code, you can notice a character code in the excel file.

VBA CHR Example 2.2

  • CHR(37) will return a % (Percent symbol) in a cell “D9” whereas,
  • CHR(47) returns / (Slash/Divide symbol) in the cell “D11”
  • CHR(57) returns a numeric value 9 in the cell  “D13” &
  • CHR(128) returns a Euro Sign in the cell “D11”

VBA CHR Function – Example #4

In the below-mentioned example, suppose I want to insert double quotation for the middle name i.e. MANI

Here, for this, I can use VBA CHR function.

Example 4.0

Step 1: Once, I run the below-mentioned code with the CHR code 34, it will insert a double quotation mark in the middle name.

Code:

Sub CHRAC2()

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

End Sub

VBA CHR Example 4.1
Step 2: Let us execute the above code by clicking on the play button and the result in the below-mentioned output in the cell “E19”

VBA chr 2

VBA CHR Function – Example #5

In the below-mentioned example, suppose I want to add a trademark symbol for company i.e. GOOGLE

Here, for this, I can use VBA CHR function with ASCII code “153”

Example 5.1

Step 1: Once, I run the below-mentioned code with the CHR code 153, it will show TM symbol for the word GOOGLE.

Code:

Sub CHRAC3()

Sheet4.Range("C3") = "GOOGLE" & CHR(153)

End Sub

Example 5.2

Step 2: The above code, result in the below-mentioned output in the cell “K18”

VBA Chr3

Save your workbook as “Excel macro-enabled workbook”. By clicking on save as at the left corner of the worksheet. Once again if you open a file, you can click on shortcut key i.e. Fn + Alt +F8, “Macro” dialog box appears, where you can run a saved macro code of your choice or you can click on Fn + Alt + F11 for a full macro window.

Things to Remember

If you enter an invalid number to the Chr function i.e. Number in a range of lesser than 0 or more than 255, then you will get the error i.e. Run-time error.

Recommended Articles

This is a guide to VBA CHR. Here we discuss how to use Excel VBA CHR function along with practical examples and downloadable excel template. You can also go through our other suggested articles –

  1. Copy Paste Function in VBA
  2. Substring Excel Function
  3. VBA Subscript out of Range
  4. Excel ISNUMBER Formula

The post VBA CHR appeared first on EDUCBA.



This post first appeared on Best Online Training & Video Courses | EduCBA, please read the originial post: here

Subscribe to Best Online Training & Video Courses | Educba

Get updates delivered right to your inbox!

Thank you for your subscription

×