Audric Thevenet


I wrote a little article on this matter:

http://metasharp.net/index.php title=VBA_creation_of_a_character_constant

here's a copy:

Creating a character constant might be a pain. Hopefully here at MetaSharp, we found a way.

Here's how we declare it very simply:

Code Snippet
Public Const MYCONST As String = "a"

However I can hear you: easy. Now let's go next step. How do I do the same for non printable characters Here's what I tried at first:

Code Snippet
Public Const MYCONST As String = Chr(29) ' non printable character

Of course it doesn't work because a constant can't be initialized with a function (Chr here) return value.

I also tried to do the following...

Code Snippet
Public Const MYCONST As String = vbNullChar + 29 ' non printable character (that is: Chr(0) + 29)

...didn't work.

And also this...

Code Snippet
Public Const MYCONST As String = " " ' non printable character ( is obtained by ALT+29)

... didn't work either. (ALT+ method uses the PC850 keymap, not the ascii table)

Here we are, the final solution:

Code Snippet
Public Const MYCONST As String = " " ' non printable character

This one is correct and pretty simple. The big question that should hit your mind is: how can I get to type in my code the character corresponding exactly to any ascii value, even non printable ones

Here's how. Create this method in an Excel module:

Code Snippet
Public Sub Test()
Dim C as String
C = Chr(29)
C = "done" ' put a breakpoint with F9 on this line
End Sub

  • set your breakpoint and run the method (with F5 for example).
  • add a quickwatch on the C variable
  • double clik on the value field of C variable
  • copy paste the displayed character from the debugger window to your code

Success