Some time ago I wrote a short article on forcing VBA to use some form of unicode to allow simple insertion of non-latin1 text into VBA modules. It sort of worked, for the most part, kind of. Well, it didn't. The problem is down to the VBA editor being locked to the local encoding of the machine it is running on. You can only type characters from BIG5 in China, Shift-JIS in Japan, and so on. If you have a need to make a VBA module that uses strings suitable for all locales ... you're pretty much stuffed.

Except you're not. If you do it properly, you can have any unicode character displayed in any VBA locale. So how do you do it properly? Well, you're supposed to use ChrW$() to generate unicode characters individually. Yes. Really.

Say you want to include the Chinese character 汉 in your latin1 text. The only way to do it is to use a string like this:

    MyString = "This is my string with a " &
    ChrW$(&H6C49) & " in it."

Imagine if you have entire sentences made up of such characters. Doesn't bear thinking about, does it? However, I've made a simple way to work with it. The trick is to write your strings outside the VBA editor using a unicode encoding (e.g. UTF-8). For example, take this little proto-module:

Sub Tomato

    MyForm.MyLabel.Caption = "Chinese or the Sinitic language(s) (汉语/漢語, pinyin: Hànyǔ; 中文, pinyin: Zhōngwén) can be considered a language or a language family and is originally the indigenous languages spoken by the Han Chinese in China."

End Sub

That won't work with VBA due to the Chinese characters. However, they display fine in unicode. Hrm. What to do. Well, you can use my little utility to fix it. It's a simple .NET app that should run on just about anything (Mono included, although I've not tested it).

Using it is very simple. Just paste your unicode string into the top box and click the Unicode to VBA button. The output should look like this:

You can also go the other way, so copy the already converted string back out of your VBA module into the top box and hit VBA to Unicode. You should see this:

That's basically it. Once the converted string is copied into your VBA, it will look like this.

Sub Tomato

    MyForm.MyLabel.Caption = "Chinese or the Sinitic language(s) (" & ChrW$(&H6C49) & ChrW$(&H8BED) & "/" & ChrW$(&H6F22) & ChrW$(&H8A9E) & ", pinyin: Hàny" & ChrW$(&H1D4) & "; " & ChrW$(&H4E2D) & ChrW$(&H6587) & ", pinyin: Zh" & ChrW$(&H14D) & "ngwén) can be considered a language or a language family and is originally the indigenous languages spoken by the Han Chinese in China."

End Sub

That should be fine for inclusion directly in VBA and all will work properly.

You can download VBAUnicodeConverter.exe directly, or the source files in VBAUnicodeCoverter.zip. There is also the original Perl script that you can find in convert.pl. There are no particular restrictions on the source, except that you should credit me if you nick bits of it. Not that there's much to nick.