Access Excel VBA Chr Function to convert ASCII value to character

This Access Excel VBA tutorial explains how to use Chr Function to convert ASCII value to character, and convert from character to ASCII value with ASC Function

What is Access Excel VBA Chr Function?

Access Excel VBA Chr Function converts ASCII value to a character. ASCII (American Standard Code for Information Interchange) uses 8-bit code units, an old encoding system which stores mainly numbers, lowercase letters a to z, uppercase letters A to Z, basic punctuation symbols, control codes. Many old systems still use this encoding system. 8 bit means the computer memory uses “8” digits with 1 and 0 combination (binary) to represent a character, 8 bits memory is equal to 1 byte.

Chr Function is applicable to: Excel VBA, Access, Access VBA

Refer to the below table, Excel Char Function will convert the “Dec” value to “Char” value.

Dec Hex Oct Char Description
0 0 000 null
1 1 001 start of heading
2 2 002 start of text
3 3 003 end of text
4 4 004 end of transmission
5 5 005 enquiry
6 6 006 acknowledge
7 7 007 bell
8 8 010 backspace
9 9 011 horizontal tab
10 A 012 new line
11 B 013 vertical tab
12 C 014 new page
13 D 015 carriage return
14 E 016 shift out
15 F 017 shift in
16 10 020 data link escape
17 11 021 device control 1
18 12 022 device control 2
19 13 023 device control 3
20 14 024 device control 4
21 15 025 negative acknowledge
22 16 026 synchronous idle
23 17 027 end of trans. block
24 18 030 cancel
25 19 031 end of medium
26 1A 032 substitute
27 1B 033 escape
28 1C 034 file separator
29 1D 035 group separator
30 1E 036 record separator
31 1F 037 unit separator
32 20 040 space
33 21 041 !
34 22 042
35 23 043 #
36 24 044 $
37 25 045 %
38 26 046 &
39 27 047
40 28 050 (
41 29 051 )
42 2A 052 *
43 2B 053 +
44 2C 054 ,
45 2D 055
46 2E 056 .
47 2F 057 /
48 30 060 0
49 31 061 1
50 32 062 2
51 33 063 3
52 34 064 4
53 35 065 5
54 36 066 6
55 37 067 7
56 38 070 8
57 39 071 9
58 3A 072 :
59 3B 073 ;
60 3C 074 <
61 3D 075 =
62 3E 076 >
63 3F 077 ?
64 40 100 @
65 41 101 A
66 42 102 B
67 43 103 C
68 44 104 D
69 45 105 E
70 46 106 F
71 47 107 G
72 48 110 H
73 49 111 I
74 4A 112 J
75 4B 113 K
76 4C 114 L
77 4D 115 M
78 4E 116 N
79 4F 117 O
80 50 120 P
81 51 121 Q
82 52 122 R
83 53 123 S
84 54 124 T
85 55 125 U
86 56 126 V
87 57 127 W
88 58 130 X
89 59 131 Y
90 5A 132 Z
91 5B 133 [
92 5C 134 \
93 5D 135 ]
94 5E 136 ^
95 5F 137 _
96 60 140 `
97 61 141 a
98 62 142 b
99 63 143 c
100 64 144 d
101 65 145 e
102 66 146 f
103 67 147 g
104 68 150 h
105 69 151 i
106 6A 152 j
107 6B 153 k
108 6C 154 l
109 6D 155 m
110 6E 156 n
111 6F 157 o
112 70 160 p
113 71 161 q
114 72 162 r
115 73 163 s
116 74 164 t
117 75 165 u
118 76 166 v
119 77 167 w
120 78 170 x
121 79 171 y
122 7A 172 z
123 7B 173 {
124 7C 174 |
125 7D 175 }
126 7E 176 ~
127 7F 177 DEL

Extended Character Set

Dec Hex Unicode Open in a new window... Char Zoom... Name
128 80 U+20AC Euro Sign
129 81 Undefined
130 82 U+201A Single Low-9 Quotation Mark
131 83 U+0192 ƒ Latin Small Letter F With Hook
132 84 U+201E Double Low-9 Quotation Mark
133 85 U+2026 Horizontal Ellipsis
134 86 U+2020 Dagger
135 87 U+2021 Double Dagger
136 88 U+02C6 ˆ Modifier Letter Circumflex Accent
137 89 U+2030 Per Mille Sign
138 8A U+0160 Š Latin Capital Letter S With Caron
139 8B U+2039 Single Left-pointing Angle Quotation Mark
140 8C U+0152 ΠLatin Capital Ligature Oe
141 8D Undefined
142 8E U+017D Ž Latin Capital Letter Z With Caron
143 8F Undefined
144 90 Undefined
145 91 U+2018 Left Single Quotation Mark
146 92 U+2019 Right Single Quotation Mark
147 93 U+201C Left Double Quotation Mark
148 94 U+201D Right Double Quotation Mark
149 95 U+2022 Bullet
150 96 U+2013 En Dash
151 97 U+2014 Em Dash
152 98 U+02DC ˜ Small Tilde
153 99 U+2122 Trade Mark Sign
154 9A U+0161 š Latin Small Letter S With Caron
155 9B U+203A Single Right-pointing Angle Quotation Mark
156 9C U+0153 œ Latin Small Ligature Oe
157 9D Undefined
158 9E U+017E ž Latin Small Letter Z With Caron
159 9F U+0178 Ÿ Latin Capital Letter Y With Diaeresis
160 A0 U+00A0   No-break Space
161 A1 U+00A1 ¡ Inverted Exclamation Mark
162 A2 U+00A2 ¢ Cent Sign
163 A3 U+00A3 £ Pound Sign
164 A4 U+00A4 ¤ Currency Sign
165 A5 U+00A5 ¥ Yen Sign
166 A6 U+00A6 ¦ Broken Bar
167 A7 U+00A7 § Section Sign
168 A8 U+00A8 ¨ Diaeresis
169 A9 U+00A9 © Copyright Sign
170 AA U+00AA ª Feminine Ordinal Indicator
171 AB U+00AB « Left-pointing Double Angle Quotation Mark
172 AC U+00AC ¬ Not Sign
173 AD U+00AD ­ Soft Hyphen
174 AE U+00AE ® Registered Sign
175 AF U+00AF ¯ Macron
176 B0 U+00B0 ° Degree Sign
177 B1 U+00B1 ± Plus-minus Sign
178 B2 U+00B2 ² Superscript Two
179 B3 U+00B3 ³ Superscript Three
180 B4 U+00B4 ´ Acute Accent
181 B5 U+00B5 µ Micro Sign
182 B6 U+00B6 Pilcrow Sign
183 B7 U+00B7 · Middle Dot
184 B8 U+00B8 ¸ Cedilla
185 B9 U+00B9 ¹ Superscript One
186 BA U+00BA º Masculine Ordinal Indicator
187 BB U+00BB » Right-pointing Double Angle Quotation Mark
188 BC U+00BC ¼ Vulgar Fraction One Quarter
189 BD U+00BD ½ Vulgar Fraction One Half
190 BE U+00BE ¾ Vulgar Fraction Three Quarters
191 BF U+00BF ¿ Inverted Question Mark
192 C0 U+00C0 À Latin Capital Letter A With Grave
193 C1 U+00C1 Á Latin Capital Letter A With Acute
194 C2 U+00C2 Â Latin Capital Letter A With Circumflex
195 C3 U+00C3 Ã Latin Capital Letter A With Tilde
196 C4 U+00C4 Ä Latin Capital Letter A With Diaeresis
197 C5 U+00C5 Å Latin Capital Letter A With Ring Above
198 C6 U+00C6 Æ Latin Capital Ligature Ae
199 C7 U+00C7 Ç Latin Capital Letter C With Cedilla
200 C8 U+00C8 È Latin Capital Letter E With Grave
201 C9 U+00C9 É Latin Capital Letter E With Acute
202 CA U+00CA Ê Latin Capital Letter E With Circumflex
203 CB U+00CB Ë Latin Capital Letter E With Diaeresis
204 CC U+00CC Ì Latin Capital Letter I With Grave
205 CD U+00CD Í Latin Capital Letter I With Acute
206 CE U+00CE Î Latin Capital Letter I With Circumflex
207 CF U+00CF Ï Latin Capital Letter I With Diaeresis
208 D0 U+00D0 Ð Latin Capital Letter Eth
209 D1 U+00D1 Ñ Latin Capital Letter N With Tilde
210 D2 U+00D2 Ò Latin Capital Letter O With Grave
211 D3 U+00D3 Ó Latin Capital Letter O With Acute
212 D4 U+00D4 Ô Latin Capital Letter O With Circumflex
213 D5 U+00D5 Õ Latin Capital Letter O With Tilde
214 D6 U+00D6 Ö Latin Capital Letter O With Diaeresis
215 D7 U+00D7 × Multiplication Sign
216 D8 U+00D8 Ø Latin Capital Letter O With Stroke
217 D9 U+00D9 Ù Latin Capital Letter U With Grave
218 DA U+00DA Ú Latin Capital Letter U With Acute
219 DB U+00DB Û Latin Capital Letter U With Circumflex
220 DC U+00DC Ü Latin Capital Letter U With Diaeresis
221 DD U+00DD Ý Latin Capital Letter Y With Acute
222 DE U+00DE Þ Latin Capital Letter Thorn
223 DF U+00DF ß Latin Small Letter Sharp S
224 E0 U+00E0 à Latin Small Letter A With Grave
225 E1 U+00E1 á Latin Small Letter A With Acute
226 E2 U+00E2 â Latin Small Letter A With Circumflex
227 E3 U+00E3 ã Latin Small Letter A With Tilde
228 E4 U+00E4 ä Latin Small Letter A With Diaeresis
229 E5 U+00E5 å Latin Small Letter A With Ring Above
230 E6 U+00E6 æ Latin Small Ligature Ae
231 E7 U+00E7 ç Latin Small Letter C With Cedilla
232 E8 U+00E8 è Latin Small Letter E With Grave
233 E9 U+00E9 é Latin Small Letter E With Acute
234 EA U+00EA ê Latin Small Letter E With Circumflex
235 EB U+00EB ë Latin Small Letter E With Diaeresis
236 EC U+00EC ì Latin Small Letter I With Grave
237 ED U+00ED í Latin Small Letter I With Acute
238 EE U+00EE î Latin Small Letter I With Circumflex
239 EF U+00EF ï Latin Small Letter I With Diaeresis
240 F0 U+00F0 ð Latin Small Letter Eth
241 F1 U+00F1 ñ Latin Small Letter N With Tilde
242 F2 U+00F2 ò Latin Small Letter O With Grave
243 F3 U+00F3 ó Latin Small Letter O With Acute
244 F4 U+00F4 ô Latin Small Letter O With Circumflex
245 F5 U+00F5 õ Latin Small Letter O With Tilde
246 F6 U+00F6 ö Latin Small Letter O With Diaeresis
247 F7 U+00F7 ÷ Division Sign
248 F8 U+00F8 ø Latin Small Letter O With Stroke
249 F9 U+00F9 ù Latin Small Letter U With Grave
250 FA U+00FA ú Latin Small Letter U With Acute
251 FB U+00FB û Latin Small Letter U With Circumflex
252 FC U+00FC ü Latin Small Letter U With Diaeresis
253 FD U+00FD ý Latin Small Letter Y With Acute
254 FE U+00FE þ Latin Small Letter Thorn
255 FF U+00FF ÿ Latin Small Letter Y With Diaeresis

Why do you need Access Excel  VBAChr Function?

1. To import special characters. However, Windows may not be able to show those characters.

2. Start a new line in a Cell with Char(10) in worksheet (must need to set Wrap Text first) and MsgBox message

Syntax of Access Excel VBA Chr Function

CHR( ascii_value )

ascii_value is the ASCII value used to retrieve the character.

Note that Chr Function also exists for worksheet but the name is Char. You can convert the character back into ASCII value using Code Function in worksheet / ASC Function in VBA

Example of Access Excel VBA Chr Function

Formula Result Explanation
Chr(65) A
Chr(90) Z
Range(“A1”).Value = “line one” & Chr(10) & “line two” line one
line two
Start a new line with Char(10)
 Range(“A1”).Value = Replace(“line one,line two”, “,”, Chr(10)) line one
line two
 Replace comma with new line

To start a new line in VBA MsgBox

msgbox_03

Public Sub test1()
    sInput = MsgBox("Are you sure to delete the data?" & Chr(10) & "Data will be deleted permanently")
End Sub

Outbound References

http://www.techonthenet.com/excel/formulas/char.php

Wyman W
Wyman is a Business Analyst based in Hong Kong, specialized in creating custom Function and Sub solutions, and is proficient in report automation with Access.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist
- Microsoft Specialist
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

Leave a Reply

Your email address will not be published. Required fields are marked *