VBA – How to avoid naming variables with non-latin alphabet characters (special letters)

Writing code in the local language is actually taught by about 100% of the German VBA books, which I have read. Which is “kind of ok”, if the VBA code only works on German computers. However, if this is not the case these 4 special letters nicely break the whole program: ä, ö, ü, ß. Furthermore, saving a variable to a string, which contains these 4 letters in a PC outside Germany also breaks the code. The Absolute Minimum Every Software Developer Absolutely, Positively Must Know About Unicode and Character Sets is a really nice article, explaining the reasons for the broken code and the sad developers.

As a workaround, you may kindly ask the user to add German regional settings to their PC. It is a straight forward process:

  • Clock And Region (search in start menu)
  • Region (link)
  • Administrative (tab)
  • Change system locale (button)
  • Current System Locale – Germany (drop down)

However, not everyone is a big fan of this. Almost noone I know, would change their regional settings just to run some VBA code. Then the “fun” rewriting party starts. There is quite a good (but probably longer than you would expect) workaround – write a hidden “dictionary worksheet” and read the “strange” cell contents passing them to varaint type. With a translation function, getting the variant and returning the English version, you may work in VBA, considering the fact that you have created a dictionary in tblDictionary  worksheet like this:

The 1 line function, implementing VLookup is this one:

And then, you may consider looping through range “B1:E3” from the dashboard sheet, reading the foreign alphabet signs on the first row, translating them to English and carrying out some actions with them:

The code is here:


Tagged with: , ,