I know that this page comes up a lot in Google searches including the terms "VBA", "Unicode" and "UTF-8", so I thought I'd best include a pointer. Although the stuff in this post does appear to work (for the most part), it doesn't survive signing modules, and can cause later pain and anguish. It is better instead to head over to my article on VBA Unicode Done Right and use that method. It's uglier, but it works every time, instead of just some of the time.

I have recently had need to support 5 languages in a single VBA module. Don't ask why, because I won't tell you. However, I found that this isn't as easy as it sounds when two of those languages happen to Japanese and Simplified Chinese.

The first, and most important, thing to do is to change the encoding of the document you are using. By default on an English US or English UK installation the CP1252 codepage is used. To change it, click the document in the VBA editor and scroll to the SaveEncoding property. Set this to 1200 - msEncodingUnicodeLittleEndian and save. You now have a fully unicode aware document. I'm not sure why, but 2 hours of searching on the Internet didn't bring this setting to light; only manually searching property dialogs made it apparent. Hopefully this should save you some searching.

Now for the actual translated text. There are a couple of ways to achieve this; either have a different VBA module for each major encoding (i.e. western, Japanese, Chinese) or have a single VBA module with three different encodings. I'm going to plump for the latter because it's more fun. The choice is yours.

So, the first thing to do is to create the strings you want in the western languages. I've chosen English, French and German, and I'm going to translate the wonderfully generic term "Next Entry". In an external editor (I use Vim), choose the 'latin1' encoding (or ISO8859-1 if that's what your editor supports) and enter the strings in whatever way you choose.

Now you have the western texts in place, it is time to begin the magic. Make sure you have a font selected with all the Unicode character set included (I use 'MS Nimsum' on Windows) and change the editor's encoding to cp932 (Japanese). The accents on the German and French characters will change to Japanese symbols, but that's OK for now. Enter the Japanese text you require.

Now you've got four out of the five languages sorted, switch your editor to cp936 (Simplified Chinese) and enter the Chinese text you require. Of course, accents and Japanese characters will be a total mess, but that's OK. It'll all come out in the wash, as they say.

So, now you have five languages in a single file, using three different encodings: latin1, cp932 and cp936. Switching your editor between them will allow you to see each of the three encodings properly, so edit away.

To get these strings into VBA, simply set the encoding to latin1 and copy/paste the strange characters that result. Editing this VBA project on a Japanese or Chinese system will magically cause the releveant text to become fully readable. I shall leave the process of returning the correct string to the UI as an exercise to the reader.