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

Posted on October 8, 2019 by

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)
End Function

Public Function TranslateValue(translateMe As Variant) As String

TranslateValue = WorksheetFunction.VLookup(translateMe, tblDictionary.Range("A:B"), 2, False)

End Function

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:

Sub Main()

    Dim myCell As Range
    For Each myCell In tblDashboard.Range("B1:E3")
        
        myCell.Select
        Stop
        
        Dim valueToTranslate As Variant
        valueToTranslate = tblDashboard.Cells(1, myCell.Column)
        
        Dim translatedValue As Variant
        translatedValue = TranslateValue(valueToTranslate)
                
        Debug.Print translatedValue
        
        Select Case translatedValue
            Case "length":
                Debug.Print "here comes length"
            Case tblDictionary.Range("B3").Value:
                Debug.Print "here comes width"
            Case Else:
                Debug.Print "this is something else - "; translatedValue
        End Select
    Next myCell
        
End Sub

Sub Main()

Dim myCell As Range

For Each myCell In tblDashboard.Range("B1:E3")

myCell.Select

Stop

Dim valueToTranslate As Variant

valueToTranslate = tblDashboard.Cells(1, myCell.Column)

Dim translatedValue As Variant

translatedValue = TranslateValue(valueToTranslate)

Debug.Print translatedValue

Select Case translatedValue

Case "length":

Debug.Print "here comes length"

Case tblDictionary.Range("B3").Value:

Debug.Print "here comes width"

Case Else:

Debug.Print "this is something else - "; translatedValue

End Select

Next myCell

End Sub

Cheers!

Tagged with: encoding, non latin variables, VBA

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

Related posts: