On UrbanPro

View My Profile       Proud to be a Teacher       Write a Review  

Monday, August 22, 2016

Use Special Characters in Excel Formula or in VBA Code

We can use the Special Characters ($ – Dollar Sign, ” – Double Quote, & – Ampersand Sign, ( – Opening Braces, ) – Closing Braces, @ – At the rate Sign) in the Excel Formulas or in the VBA Code, but as their Syntax only. If we want to use them as a character, we'll not be able to use. As using them, will spoil the syntax of the formula and it will through an error. But still Some times we'll be require to use Special Characters in the Excel Formulas or in the VBA Code programming as a character. So in this scenario we can use the ASCII number of those characters. ASCII values can be used to refer the characters in both Excel formulas as well as Excel VBA.

Use of ASCII numbers in Excel Formulas as well as Excel VBA programming:

Excel Formulas    :  =CHAR(ASCII Number)
Excel VBA           :  VBA.Chr(ASCII Number)

Below is the list of ASCII keys of few most used symbols which may affect the formula syntax in Excel if they are used as it is:

Symbol                       ASCII Code Syntax - Excel Formula  Syntax - Excel VBA
$ - Dollar Sign      36 =Char(36)   VBA.Chr(36)
" - Double Quote       34 =Char(34) VBA.Chr(34)
& - Ampersand Sign   38 =Char(38)   VBA.Chr(38)
( - Opening Braces 40 =Char(40)      VBA.Chr(40)
) - Closing Braces   41 =Char(41)      VBA.Chr(41)
@ - At the rate Sign 64 =Char(64)      VBA.Chr(64)