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

DecHexBinaryHTMLCharDescription
000&#0;NULNull
111&#1;SOHStart of Header
2210&#2;STXStart of Text
3311&#3;ETXEnd of Text
44100&#4;EOTEnd of Transmission
55101&#5;ENQEnquiry
66110&#6;ACKAcknowledge
77111&#7;BELBell
881000&#8;BSBackspace
991001&#9;HTHorizontal Tab
100A1010&#10;LFLine Feed
110B1011&#11;VTVertical Tab
120C1100&#12;FFForm Feed
130D1101&#13;CRCarriage Return
140E1110&#14;SOShift Out
150F1111&#15;SIShift In
161010000&#16;DLEData Link Escape
171110001&#17;DC1Device Control 1
181210010&#18;DC2Device Control 2
191310011&#19;DC3Device Control 3
201410100&#20;DC4Device Control 4
211510101&#21;NAKNegative Acknowledge
221610110&#22;SYNSynchronize
231710111&#23;ETBEnd of Transmission Block
241811000&#24;CANCancel
251911001&#25;EMEnd of Medium
261A11010&#26;SUBSubstitute
271B11011&#27;ESCEscape
281C11100&#28;FSFile Separator
291D11101&#29;GSGroup Separator
301E11110&#30;RSRecord Separator
311F11111&#31;USUnit Separator
3220100000&#32;spaceSpace
3321100001&#33;!Exclamation mark
3422100010&#34;Double quote
3523100011&#35;#Number
3624100100&#36;$Dollar sign
3725100101&#37;%Percent
3826100110&#38;&Ampersand
3927100111&#39;Single quote
4028101000&#40;(Left parenthesis
4129101001&#41;)Right parenthesis
422A101010&#42;*Asterisk
432B101011&#43;+Plus
442C101100&#44;,Comma
452D101101&#45;Minus
462E101110&#46;.Period
472F101111&#47;/Slash
4830110000&#48;0Zero
4931110001&#49;1One
5032110010&#50;2Two
5133110011&#51;3Three
5234110100&#52;4Four
5335110101&#53;5Five
5436110110&#54;6Six
5537110111&#55;7Seven
5638111000&#56;8Eight
5739111001&#57;9Nine
583A111010&#58;:Colon
593B111011&#59;;Semicolon
603C111100&#60;Less than
613D111101&#61;=Equality sign
623E111110&#62;Greater than
633F111111&#63;?Question mark
64401000000&#64;@At sign
65411000001&#65;ACapital / Upper case  A
66421000010&#66;BCapital / Upper case  B
67431000011&#67;CCapital / Upper case  C
68441000100&#68;DCapital / Upper case  D
69451000101&#69;ECapital / Upper case  E
70461000110&#70;FCapital / Upper case  F
71471000111&#71;GCapital / Upper case  G
72481001000&#72;HCapital / Upper case  H
73491001001&#73;ICapital / Upper case  I
744A1001010&#74;JCapital / Upper case  J
754B1001011&#75;KCapital / Upper case  K
764C1001100&#76;LCapital / Upper case  L
774D1001101&#77;MCapital / Upper case  M
784E1001110&#78;NCapital / Upper case  N
794F1001111&#79;OCapital / Upper case  O
80501010000&#80;PCapital / Upper case  P
81511010001&#81;QCapital / Upper case  Q
82521010010&#82;RCapital / Upper case  R
83531010011&#83;SCapital / Upper case  S
84541010100&#84;TCapital / Upper case  T
85551010101&#85;UCapital / Upper case  U
86561010110&#86;VCapital / Upper case  V
87571010111&#87;WCapital / Upper case  W
88581011000&#88;XCapital / Upper case  X
89591011001&#89;YCapital / Upper case  Y
905A1011010&#90;ZCapital / Upper case  Z
915B1011011&#91;[Left square bracket
925C1011100&#92;\Backslash
935D1011101&#93;]Right square bracket
945E1011110&#94;^Caret / circumflex
955F1011111&#95;_Underscore
96601100000&#96;`Grave / accent
97611100001&#97;aLower case  a
98621100010&#98;bLower case  b
99631100011&#99;cLower case  c
100641100100&#100;dLower case  d
101651100101&#101;eLower case  e
102661100110&#102;fLower case  f
103671100111&#103;gLower case  g
104681101000&#104;hLower case  h
105691101001&#105;iLower case  i
1066A1101010&#106;jLower case  j
1076B1101011&#107;kLower case  k
1086C1101100&#108;lLower case  l
1096D1101101&#109;mLower case  m
1106E1101110&#110;nLower case  n
1116F1101111&#111;oLower case  o
112701110000&#112;pLower case  p
113711110001&#113;qLower case  q
114721110010&#114;rLower case  r
115731110011&#115;sLower case  s
116741110100&#116;tLower case  t
117751110101&#117;uLower case  u
118761110110&#118;vLower case  v
119771110111&#119;wLower case  w
120781111000&#120;xLower case  x
121791111001&#121;yLower case  y
1227A1111010&#122;zLower case  z
1237B1111011&#123;{Left curly bracket
1247C1111100&#124;|Vertical bar
1257D1111101&#125;}Right curly bracket
1267E1111110&#126;~Tilde
1277F1111111&#127;DELDelete

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
Output of escape character demo in VBA

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.