excel - Unicode string literals in VBA

Question

I would like to declare (in a VBA class module) some private constant strings that contain Japanese characters. Is there a way to construct String literals (or combining literals in a way) that may be accepted as initializers in a Const declaration? i.e. something like:

Private Const MY_CONST = ...

or

Private Const MY_CONST As String = ...

I use MS Excel v14.0.6112.5000 (MS Office Professional Plus 2010).

What won't work:

• Pasting the Japanese chars directly in a string literal (e.g. ... = "変数") because the VBA editor will mess with the chars;
• Using ChrW() or ChrW$() (e.g. ... = ChrW$(22793) & ChrW$(25968)), because function calls are not allowed in Const initializers.

What I wouldn't like:

• Faking the Const by creating Private Property Get returning the string, because the string will be recreated every time I access the property (plus, is confusing and ugly... but, okay, the last two things are rather a matter of taste).

Answer 1

Faking the Const by creating Private Property Get returning the string, because the string will be recreated every time I access the property (plus, is confusing and ugly... but, okay, the last two things are rather a matter of taste).

You need not recreate the string each time you access the property.

While this is still ugly as a matter of taste, make a read-only property (essentially Const, since it doesn't have a Property Let procedure), and construct the string in the Class_Initialize event:

'## CLASS MODULE
Private pUnicodeString As String

Sub Class_Initialize()
    pUnicodeString = ChrW(22793) & ChrW(25968)
End Sub

Property Get UnicodeString() As String
    UnicodeString = pUnicodeString
End Property

And then invoke it like:

'## STANDARD MODULE
Sub Test()
Dim c As myClass
Set c = New myClass

[A1].Value = c.UnicodeString

End Sub