VBA creation of a character constant

From MetaSharp

---

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

Here's how we declare it very simply:

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:

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...

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

...didn't work.

And also this...

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:

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:

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