Working with Local Language Text in Your Code

Visual Basic includes many features to help your code run properly in other locales, but it cannot automatically translate text from one language to another. For example, you might want your application to include menu text, dialog text, alert messages, and so on. At first, it might seem that you must type this text directly into your code, which prevents the code from becoming a truly transportable application. However, you can create a string table to replace specific quoted text with variables and references to worksheet names. The cells your worksheet names refer to can vary depending on the locale in which your code is running. In this way, you can display words and phrases that change depending on your user's locale.

For example, the following worksheet includes a string table for three languages.

To use the string table, define names for cells in the Localized Strings column. You can use the Offset method to get the text in the other language columns. To work in English, use 0 (zero) for the columnOffset argument; to work in French, use 1 for the columnOffset argument; and to work in German, use 2 for the columnOffset argument. You can obtain the column offset value in any of the following ways:

• Use the International property of the Application object to determine the locale version of Microsoft Excel (xlCountryCode) or the current country setting of the operating system (xlCountrySetting). Make a mathematical adjustment to convert the setting to a column offset value, and then define a Public variable accordingly. (For more information about public scope, see Chapter 2, "Variables, Constants, and Data Types.")
• Display a dialog box asking the user for a language or country name. Then define a public variable, and assign it an offset value according to the user's input.
• Define a public constant that the user sets.

The following code determines an offset code based on the language version of Microsoft Excel you're using.

Public GlobalOffsetCode

Sub GetGlobalOffsetCode()
Select Case Application.International(xlCountryCode)
    Case 1:                            'US English
        GlobalOffsetCode = 1
    Case 33:                            'French
        GlobalOffsetCode = 2
    Case 49:                            'German
        GlobalOffsetCode = 3
    Case Else:                        'US English as the default language
        GlobalOffsetCode = 1
End Select
End Sub

The following code uses this offset code to display an appropriate message box to welcome the user.

WelcomeMsg = ThisWorkbook.Worksheets("LocalizationTable").Range _
    ("Welcome").Offset(0, GlobalOffsetCode).Value
MsgBox WelcomeMsg

You can use a similar technique to change the text, positions, and sizes of items in dialog boxes, making your code truly transportable.