Microsoft Corporation
Updated April 7, 1997
Introduction
Working with Local Language in Your Code
Using String Tables
Using string tables in Microsoft Excel
Using string tables in Microsoft Word
Using string tables in Microsoft Access
Localizing Text at Run Time
Working with Local Formatting in Your Code
Formatting Dates
Formatting Currency
Formatting Numeric Values and Separators
Working with Character Sets
String Manipulation
Displaying and Printing Text
In previous versions of Microsoft® Office, you could use non-English keywords to reference objects if you included the proper object library with your application. In Office 97, object libraries are no longer localized; therefore, you must use English keywords to reference objects.
Note If a user opens a document in Microsoft Excel 97, Microsoft Access 97, or PowerPoint® 97 that contains localized Visual Basic® for Applications (VBA) code written in a previous version of the application, the keywords in the code are converted into English. However, localized WordBasic code cannot be converted into English VBA code. If your solution uses localized WordBasic code in localized versions of Word 6.0 or Word 96, the code will run in Word 97 if you recompile it using the attached Word typelib.
In the context of this paper, a locale is the region selected from the Regional Settings tab in the Regional Settings Properties dialog box in Control Panel. An object library is a file with an .olb extension that determines what words and symbols Visual Basic can understand—that is, the names of objects, properties, and methods you can type into a Visual Basic module.
This article addresses basic issues related to writing international code in an Office 97 Visual Basic programming environment. For a more advanced perspective on localization issues, including API functions, code-page support, and keyboard layouts, refer to Developing International Software for Windows 95 and Windows NT by Nadine Kano (ISBN 1-55615-840-8). This book is published by Microsoft Press and available wherever computer books are sold or from the Microsoft Press Web site (http://mspress.microsoft.com/).
One of the first things you should consider as you develop an international application is the way messages from your application are presented to the user. In localized (translated) versions of Microsoft Excel, Microsoft Word, Microsoft PowerPoint, and Microsoft Access, error messages appear to users in their native language. When you develop an application, you must translate your own messages.
You can translate messages into your user's native language in a number of ways. One solution is to construct a string table (a table of messages that are each translated into the language of your user) at design time and reference the cells in the table when you want to display a message. String tables are easier to maintain because they are separate from the code, but accessing cells in a table, especially if the table is large, may make your application run slower. Another solution is to assign message text from the appropriate language to variables at run time and reference the variables when you want to display a message. Assigning text at run time is faster but is more difficult to maintain because localized text is combined with programming code.
Note String tables cannot be implemented in PowerPoint because only Visual Basic code is stored in the PowerPoint project file (.ppa).
To prevent your user from altering the contents of a string table, the table should be hidden from the user. In Microsoft Excel, you can hide the table from your users by placing the table on a worksheet and simply hiding the worksheet, or you can distribute your solution as a Microsoft Excel add-in. In Word, you can hide the table by placing it in a document and saving the document as a template. Then distribute your application as a global template.
You can take advantage of the way Microsoft Excel references named ranges as you create your string table. For example, the following string table contains messages localized into English, French, and German.
Figure 1. A string table displaying localized text in a Microsoft Excel application.
To use the string table, define names for cells in the Localized Strings column. Then use the Offset method to retrieve translated text from the appropriate language column. To display a specific message from this table in English, use 1 for the columnOffset argument; for French, use 2; and for German, use 3. You can obtain the column offset value in either of the following ways:
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 the offset code to display a welcome message in the user's native language.
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.
You can create a similar string table to display localized text in a Word application. The following string table includes text translated into English, French, and German.
Figure 2. A string table displaying localized text in a Word application.
Because Word doesn't support named ranges, you should define public constants representing each of the messages you localize and use the constants as row indexes for the table.
The following constants apply to the string table above.
Public Const iWelcome As Integer =1
Public Const iCustomerName As Integer = 2
Public Const iAcctNumber As Integer = 3
Public Const iNotValidMessage As Integer = 4
Then define a column variable and assign it a value in either of the following ways:
The following code determines the correct column for the string table based on the language version of Word you're using.
Public iTheLang
Sub GetGlobaltheLang()
Select Case Application.International(wdProductLanguageID)
Case wdEnglishUS
iTheLang = 1
Case wdFrench
iTheLang = 2
Case wdGerman
iTheLang = 3
Case Else: 'US English as the default language
iTheLang = 1
End Select
End Sub
The following code uses column and row variables to display a welcome message in the user's native language.
welcomeMsg = Documents("Welcome.doc").Tables(1) _
.Cell(Row:=iWelcome, Column:=iLangCol)
MsgBox welcomeMsg
For information about using string tables with Microsoft Access, see "Detecting Language Version and Internationalizing Text" (http://www.microsoft.com/AccessDev/Articles/HowToBk.HTM). You can find this information and more in the bookMicrosoft Access 95 How-To, by Ken Getz and Paul Litwin (Waite Group Press, ISBN 1-57-169-052-2), available wherever computer books are sold.
An alternative to the string table is to localize text at run time. Use a Select Case...End Select structure to assign the appropriate translation to your message text. For example, the following code uses the International property in Word to identify the user's locale and assign the variables accordingly.
Public sWelcome As String
Public sCustName As String
Sub assignText()
Select Case Application.International(wdProductLanguageID)
Case wdEnglishUS
sWelcome = "Welcome"
sCustName = "What is your name?"
Case wdFrench
sWelcome = "Bienvennue"
sCustName = "Quel est votre nom?"
Case wdGerman
sWelcome = "Willkommen"
sCustName = "Wie heißen Sie?"
End Select
End Sub
To display the translated text to your user, refer to the appropriate variable name. The following code displays a welcome message in a dialog box in the user's native language.
Msgbox sWelcome
It is just as important to display local formatting to your user as it is to display local language. If your application uses numbers, dates, times, or currency, you must format these symbols according to language. Visual Basic helps this process by providing several functions that convert data from one type to another, using locale settings.
Whenever possible, use locale-aware functions to make your code transportable. This is especially important when you're converting strings to numbers or dates. For example, the Str and Val functions always assume that a period is the decimal separator, but CStr, CDbl, CSng, CInt, and CLng use the current operating system regional settings to determine the decimal separator.
Don't type dates as strings in your code, because date formats aren't the same in every country. Even locale-aware conversion functions can process strings in ways you might not expect. For example, the following code behaves differently in different locales.
StartDate = "2/3/97"
NewDate = CDate(StartDate)
Msgbox Format(NewDate, "Long Date")
When run in an English/United States locale, the variable NewDate contains a value equivalent to February 3, 1997; in an English/Australian locale, NewDate contains a value equivalent to March 2, 1997. This behavior is a potential source of error when you're programming, but it's very useful for processing user input from a dialog box. CDate converts the user's text into the date that the user intends.
As the programmer, you should always code dates as literals—such as #2/3/97#—so that Visual Basic recognizes the exact date you intend. For example, in the following code, NewDate contains a value equivalent to either February 3, 1997, or March 2, 1997, depending on the locale of the programmer, but not the locale of the user. The date literal is interpreted in the context of the programmer's locale. The date is the same for all users wherever the code is run.
StartDate = #2/3/97#
NewDate = CDate(StartDate)
Similarly, don't store a currency value as a string that includes a currency symbol, because the currency symbol varies according to locale. For example, the following code generates an error in every locale except locales where the dollar sign ($) is the currency symbol.
Money = "$1.22"
NewMoney = CCur(Money)
To prevent this error, store currency values as decimal numbers, as shown in the following example. (This example assumes that the period is the decimal separator in the programmer's locale, but the code runs correctly no matter what the decimal separator is in the user's locale.)
Money = 1.22
NewMoney = CCur(Money)
In the United States, the period (.) is used as the decimal separator. However, in most European countries, the comma (,) is used as the decimal separator. Similarly, in the United States, a comma is used as the thousands separator to isolate groups of three digits to the left of the decimal separator. In several European countries, a period or a space is used for this purpose.
In Visual Basic, the Str and Val functions always assume a period is the decimal separator. In a majority of locales, this assumption is not valid. Instead, use the CStr, CDbl, CSng, CInt, and CLng functions to provide international conversions from any other data type to the data type you need. These functions use the system locale to determine the decimal separator. For more information about using these functions, refer to Visual Basic Help.
Characters from various languages are encoded in a table of characters, called a code page. A code page allows an operating system to provide support for character sets and keyboard layouts used in different countries. The script for each language is represented by its own code page. Each foreign language edition of Windows uses a different structure to support a particular character set. For example:
Single-byte (SBCS): Supports 256 characters, each identified by a unique 1-byte character value. This type of code is used for all European editions (Latin and non-Latin languages). A bi-directional (BiDi) character set is a single-byte code page that allows for bi-directional text. This type is used by Arabic and Hebrew editions.
Double-byte (DBCS): Supports thousands of characters, each identified by a unique 2-byte character value. This type of code page is used by all Far Eastern languages (such as Japanese and Chinese Traditional).
Unicode: All commonly used text characters are represented in this 2-byte character set. Unicode is a standard of encoding characters independent of the languages that use them, thus simplifying localization.
Users can exchange U.S. and Far East Office 97 documents if they install the appropriate NLS and font files from the \Valupack\fareast directory on the Office 97 CD. These files display Far East characters, read document properties, and read Office 95 documents. For more information about exchanging U.S. and Far East documents, consult the Value Pack Help file.
Note DBCS is a different character set from Unicode. Both ANSI characters and DBCS characters are converted to Unicode and Unicode characters are converted to ANSI characters or DBCS characters whenever the conversion is needed.
In locales where DBCS is used, a file may include both double-byte and single-byte characters. Because a DBCS character is represented by two bytes, your Visual Basic code must avoid splitting the bytes. In the following example, assume TESTFILE is a text file containing DBCS characters.
Open "TESTFILE" For Input As #1 ' Open file for input.
' Read all characters in the file.
Do While Not EOF(1)
MyChar = Input(1, #1) ' Read a character.
' Perform an operation using Mychar.
Loop
Close #1 ' Close file.
When you read a fixed length of bytes from a binary file, use a Byte array instead of a String variable to avoid the Unicode/ANSI conversion.
Dim MyByteString(0 to 4) As Byte
Get #1,, MyByteString
When you use a String variable with Input or InputB to read bytes from a binary file, Unicode conversion occurs and the result is not correct.
Names of files and directories may also include DBCS characters.
A DBCS-enabled application should display DBCS characters on DBCS versions of Windows. When you use a font designed for SBCS characters only, DBCS characters may not display correctly on DBCS versions of Windows. If you do not have any DBCS-enabled fonts or do not know which font is appropriate for the target environment, you can use the System or FixedSys fonts because these fonts reside on every environment. Note that System and FixedSys fonts have few variations of their sizes. Similar considerations exist when printing DBCS characters from your application.
The information contained in this document represents the current view of Microsoft Corporation on the issues discussed as of the date of publication. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information presented after the date of publication.