Guide to Escape Characters in VBA

An Introduction to “Escape Characters”

During the runtime of a program you might need to display certain information or ask a question. Some examples are :

  1. While raising a support ticket, we get a ticket number after clicking on the submit button. Along with a number, we also get a message like “Keep this number for future reference. You will get a response within 6 days."
  2. While filing income tax returns, we might get a message, “Your return has been filed successfully." And then some additional instructions after that.
  3. When filing out a form, we might get instructions from a rejection/error message stating an expected password structure or mandatory fields.

These messages are either dynamic or hard-coded strings that are typed within double quotes.

Examples:

1. Your user id is “RAD00234".

2. Maximum number of invalid attempts to login to this bank account.

Your account as been locked for security reasons.

Please contact your branch to get your account unlocked.

Strings are usually presented within double quotes in general. In the first example above, the user id value is within double quotes. This means that the first double quote — the one in front of the user id — would mark the end of that string. So can you guess how it is possible to display that entire sentence, double quotes and all?

In the second example, do you see that there are three sentences with each one starting in a new line?

It is an escape character that you display text like this. An escape character helps the character(s) that follow it be interpreted differently from their default behavior by the programming language.

Escape Character in VBA

In programming languages “\" — a backslash — is generally used as an escape character.

Example : \n for newline, \t for tab space.

But in VBA, we use a function named “chr()". This in-built function returns the ASCII equivalent value of the parameter.

Syntax:

Chr(<number>)

Where number can be any number from 0 to 127 .

This function returns the corresponding ASCII value from the table below.

The ASCII Table

Dec Hex Binary HTML Char Description
0 0 0 &#0; NUL Null
1 1 1 &#1; SOH Start of Header
2 2 10 &#2; STX Start of Text
3 3 11 &#3; ETX End of Text
4 4 100 &#4; EOT End of Transmission
5 5 101 &#5; ENQ Enquiry
6 6 110 &#6; ACK Acknowledge
7 7 111 &#7; BEL Bell
8 8 1000 &#8; BS Backspace
9 9 1001 &#9; HT Horizontal Tab
10 0A 1010 &#10; LF Line Feed
11 0B 1011 &#11; VT Vertical Tab
12 0C 1100 &#12; FF Form Feed
13 0D 1101 &#13; CR Carriage Return
14 0E 1110 &#14; SO Shift Out
15 0F 1111 &#15; SI Shift In
16 10 10000 &#16; DLE Data Link Escape
17 11 10001 &#17; DC1 Device Control 1
18 12 10010 &#18; DC2 Device Control 2
19 13 10011 &#19; DC3 Device Control 3
20 14 10100 &#20; DC4 Device Control 4
21 15 10101 &#21; NAK Negative Acknowledge
22 16 10110 &#22; SYN Synchronize
23 17 10111 &#23; ETB End of Transmission Block
24 18 11000 &#24; CAN Cancel
25 19 11001 &#25; EM End of Medium
26 1A 11010 &#26; SUB Substitute
27 1B 11011 &#27; ESC Escape
28 1C 11100 &#28; FS File Separator
29 1D 11101 &#29; GS Group Separator
30 1E 11110 &#30; RS Record Separator
31 1F 11111 &#31; US Unit Separator
32 20 100000 &#32; space Space
33 21 100001 &#33; ! Exclamation mark
34 22 100010 &#34; Double quote
35 23 100011 &#35; # Number
36 24 100100 &#36; $ Dollar sign
37 25 100101 &#37; % Percent
38 26 100110 &#38; & Ampersand
39 27 100111 &#39; Single quote
40 28 101000 &#40; ( Left parenthesis
41 29 101001 &#41; ) Right parenthesis
42 2A 101010 &#42; * Asterisk
43 2B 101011 &#43; + Plus
44 2C 101100 &#44; , Comma
45 2D 101101 &#45; Minus
46 2E 101110 &#46; . Period
47 2F 101111 &#47; / Slash
48 30 110000 &#48; 0 Zero
49 31 110001 &#49; 1 One
50 32 110010 &#50; 2 Two
51 33 110011 &#51; 3 Three
52 34 110100 &#52; 4 Four
53 35 110101 &#53; 5 Five
54 36 110110 &#54; 6 Six
55 37 110111 &#55; 7 Seven
56 38 111000 &#56; 8 Eight
57 39 111001 &#57; 9 Nine
58 3A 111010 &#58; : Colon
59 3B 111011 &#59; ; Semicolon
60 3C 111100 &#60; Less than
61 3D 111101 &#61; = Equality sign
62 3E 111110 &#62; Greater than
63 3F 111111 &#63; ? Question mark
64 40 1000000 &#64; @ At sign
65 41 1000001 &#65; A Capital / Upper case  A
66 42 1000010 &#66; B Capital / Upper case  B
67 43 1000011 &#67; C Capital / Upper case  C
68 44 1000100 &#68; D Capital / Upper case  D
69 45 1000101 &#69; E Capital / Upper case  E
70 46 1000110 &#70; F Capital / Upper case  F
71 47 1000111 &#71; G Capital / Upper case  G
72 48 1001000 &#72; H Capital / Upper case  H
73 49 1001001 &#73; I Capital / Upper case  I
74 4A 1001010 &#74; J Capital / Upper case  J
75 4B 1001011 &#75; K Capital / Upper case  K
76 4C 1001100 &#76; L Capital / Upper case  L
77 4D 1001101 &#77; M Capital / Upper case  M
78 4E 1001110 &#78; N Capital / Upper case  N
79 4F 1001111 &#79; O Capital / Upper case  O
80 50 1010000 &#80; P Capital / Upper case  P
81 51 1010001 &#81; Q Capital / Upper case  Q
82 52 1010010 &#82; R Capital / Upper case  R
83 53 1010011 &#83; S Capital / Upper case  S
84 54 1010100 &#84; T Capital / Upper case  T
85 55 1010101 &#85; U Capital / Upper case  U
86 56 1010110 &#86; V Capital / Upper case  V
87 57 1010111 &#87; W Capital / Upper case  W
88 58 1011000 &#88; X Capital / Upper case  X
89 59 1011001 &#89; Y Capital / Upper case  Y
90 5A 1011010 &#90; Z Capital / Upper case  Z
91 5B 1011011 &#91; [ Left square bracket
92 5C 1011100 &#92; \ Backslash
93 5D 1011101 &#93; ] Right square bracket
94 5E 1011110 &#94; ^ Caret / circumflex
95 5F 1011111 &#95; _ Underscore
96 60 1100000 &#96; ` Grave / accent
97 61 1100001 &#97; a Lower case  a
98 62 1100010 &#98; b Lower case  b
99 63 1100011 &#99; c Lower case  c
100 64 1100100 &#100; d Lower case  d
101 65 1100101 &#101; e Lower case  e
102 66 1100110 &#102; f Lower case  f
103 67 1100111 &#103; g Lower case  g
104 68 1101000 &#104; h Lower case  h
105 69 1101001 &#105; i Lower case  i
106 6A 1101010 &#106; j Lower case  j
107 6B 1101011 &#107; k Lower case  k
108 6C 1101100 &#108; l Lower case  l
109 6D 1101101 &#109; m Lower case  m
110 6E 1101110 &#110; n Lower case  n
111 6F 1101111 &#111; o Lower case  o
112 70 1110000 &#112; p Lower case  p
113 71 1110001 &#113; q Lower case  q
114 72 1110010 &#114; r Lower case  r
115 73 1110011 &#115; s Lower case  s
116 74 1110100 &#116; t Lower case  t
117 75 1110101 &#117; u Lower case  u
118 76 1110110 &#118; v Lower case  v
119 77 1110111 &#119; w Lower case  w
120 78 1111000 &#120; x Lower case  x
121 79 1111001 &#121; y Lower case  y
122 7A 1111010 &#122; z Lower case  z
123 7B 1111011 &#123; { Left curly bracket
124 7C 1111100 &#124; | Vertical bar
125 7D 1111101 &#125; } Right curly bracket
126 7E 1111110 &#126; ~ Tilde
127 7F 1111111 &#127; DEL Delete

Escaping a Double Quote

In order to display one double quote as a part of the string, we have to use double quotes twice consecutively. So you should use a double quote to escape a double quote.

Below, I also show you how to use the Chr() function to output a line feed, the character “#”, and “%”.

Example:

Sub demo_escape()

' see the differences in output
    Debug.Print "I love to drink tea."
    Debug.Print "I love to drink ""tea""."
    
    Debug.Print "I live in England."
    Debug.Print "I live in ""England""."
    
'chr(10) is equivalent to a new line. two sentences are concatenated here.
    Debug.Print "I have got promoted. What about you?"
    Debug.Print "I have got promoted." & Chr(10) & "What about you?"
 
    Debug.Print Chr(35) & "Number"
    Debug.Print Chr(37) & "Percent"
    
End Sub

Conclusion

An escape character is very helpful for displaying strings or messages the way we want. As an automation specialist, I have faced situations where some unknown characters need to be identified on a webpage. The built-in function Chr() has very helpful for me in difficult situations like this.

I’ve also widely used this in my coding too, because of its robustness and reliability. VBA has certainly simplified the use of escape characters by introducing a function that uses the ASCII table. One last bonus tip — there is a function ASCII(<character>) that you can use to return the ASCII number (Dec) from the table above.

Leave a Reply

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