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:
Public Function TranslateValue(translateMe As Variant) As String
TranslateValue = WorksheetFunction.VLookup(translateMe, tblDictionary.Range("A:B"), 2, False)
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:
Dim myCell As Range
For Each myCell In tblDashboard.Range("B1:E3")
Dim valueToTranslate As Variant
valueToTranslate = tblDashboard.Cells(1, myCell.Column)
Dim translatedValue As Variant
translatedValue = TranslateValue(valueToTranslate)
Select Case translatedValue
Debug.Print "here comes length"
Debug.Print "here comes width"
Debug.Print "this is something else - "; translatedValue