Creating an International Application

The final compatibility concern deals with language issues and international settings. Excel is available in many different language versions. The following statement displays the country code for the version of Excel:

 MsgBox Application.International(xlCountryCode) 

The United States/English version of Excel has a country code of 1 . Other country codes are listed in Table 26-1.

Table 26-1: EXCEL COUNTRY CODES
Open table as spreadsheet

Country	Country Code
English	1
Russian	7
Greek	30
Dutch	31
French	33
Spanish	34
Hungarian	36
Italian	39
Czech	42
Danish	45
Swedish	46
Norwegian	47
Polish	48
German	49
Portuguese (Brazil)	55
Thai	66
Japanese	81
Korean	82
Vietnamese	84
Simplified Chinese	86
Turkish	90
Indian	91
Urdu	92
Portuguese	351
Finnish	358
Traditional Chinese	886
Arabic	966
Hebrew	972
Farsi	982

If your application will be used by those who speak another language, you need to ensure that the proper language is used in your dialog boxes. Also, you need to identify the user 's decimal and thousands separator characters. In the United States, these are almost always a period and a comma, respectively. However, users in other countries might have their systems set up to use other characters . Yet another issue is date and time formats: The United States is one of the few countries that use the (illogical) month/day/year format.

If you're developing an application that will be used only by people with your company, you probably won't need to be concerned with international compatibility. But, if your company has offices throughout the world, or if you plan to distribute your application outside your country, you need to address a number of issues to ensure that your application will work properly. I discuss these issues in the following sections.

Multilanguage applications

An obvious consideration involves the language that is used in your application. For example, if you use one or more dialog boxes, you probably want the text to appear in the language of the user. Fortunately, this is not too difficult ( assuming , of course, that you can translate your text or know someone who can).

CD-ROM  

The companion CD-ROM contains an example that demonstrates how to allow the user to choose from three languages in a dialog box: English, Spanish, or German. The filename is  multilingual wizard.xlsm .

The first step of the multilingual wizard (found on the CD) contains three OptionButtons that enable the user to select a language. The text for the three languages is stored in a worksheet.

Figure 26-2 shows the UserForm displaying text in all three languages.

Figure 26-2: The Wizard Demo in English, Spanish, and German.

VBA language considerations

In general, you need not be concerned with the language in which you write your VBA code. Excel uses two object libraries: the Excel object library and the VBA object library. When you install Excel, it registers the English language version of these object libraries as the default libraries. (This is true regardless of the language version of Excel.)

Using local properties

If your code will display worksheet information, such as a formula or a range address, you probably want to use the local language. For example, the following statement displays the formula in cell A1:

 MsgBox Range("A1").Formula 

For international applications, a better approach is to use the FormulaLocal property rather than the Formula property:

 MsgBox Range("A1").FormulaLocal 

Several other properties also have local versions. These are shown in Table 26-2 (refer to the Help system for specific details).

Table 26-2: PROPERTIES THAT HAVE LOCAL VERSIONS
Open table as spreadsheet

Property	Local Version	Return Contents
Address	AddressLocal	An address
Category	CategoryLocal	A function category
Formula	FormulaLocal	A formula
FormulaR1C1	FormulaR1C1Local	A formula, using R1C1 notation
Name	NameLocal	A name
NumberFormat	NumberFormatLocal	A number format
RefersTo	RefersToLocal	A reference
RefersToR1C1	RefersToR1C1Local	A reference, using R1C1 notation

Identifying system settings

Generally, you cannot assume that the end user's system is set up like the system on which you develop your application. For international applications, you need to be aware of the following settings:

• Decimal separator: The character used to separate the decimal portion of a value.

• Thousands separator: The character used to delineate every three digits in a value.

• List separator: The character used to separate items in a list.

You can determine the current separator settings by accessing the International property of the Application object. For example, the following statement displays the decimal separator, which won't always be a period:

 MsgBox Application.International(xlDecimalSeparator) 

The 45 international settings that you can access with the International property are listed in Table 26-3.

Table 26-3: CONSTANTS FOR THE INTERNATIONAL PROPERTY
Open table as spreadsheet

Constant	What It Returns
xlCountryCode	Country version of Microsoft Excel.
xlCountrySetting	Current country setting in the Windows Control Panel.
xlDecimalSeparator	Decimal separator.
xlThousandsSeparator	Thousands separator.
xlListSeparator	List separator.
xlUpperCaseRowLetter	Uppercase row letter (for R1C1-style references).
xlUpperCaseColumnLetter	Uppercase column letter.
xlLowerCaseRowLetter	Lowercase row letter.
xlLowerCaseColumnLetter	Lowercase column letter.
xlLeftBracket	Character used instead of the left bracket ([) in R1C1-style relative references.
xlRightBracket	Character used instead of the right bracket (]) in R1C1-style references.
xlLeftBrace	Character used instead of the left brace ({) in array literals.
xlRightBrace	Character used instead of the right brace (}) in array literals.
xlColumnSeparator	Character used to separate columns in array literals.
xlRowSeparator	Character used to separate rows in array literals.
xlAlternateArraySeparator	Alternate array item separator to be used if the current array separator is the same as the decimal separator.
xlDateSeparator	Date separator (/).
xlTimeSeparator	Time separator (:).
xlYearCode	Year symbol in number formats (y).
xlMonthCode	Month symbol (m).
xlDayCode	Day symbol (d).
xlHourCode	Hour symbol (h).
xlMinuteCode	Minute symbol (m).
xlSecondCode	Second symbol (s).
xlCurrencyCode	Currency symbol.
xlGeneralFormatName	Name of the General number format.
xlCurrencyDigits	Number of decimal digits to be used in currency formats.
xlCurrencyNegative	A value that represents the currency format for negative currency values.
xlNoncurrencyDigits	Number of decimal digits to be used in noncurrency formats.
xlMonthNameChars	Always returns three characters for backward-compatibility; abbreviated month names are read from Microsoft Windows and can be any length.
xlWeekdayNameChars	Always returns three characters for backward-compatibility; abbreviated weekday names are read from Microsoft Windows and can be any length.
xlDateOrder	An integer that represents the order of date elements.
xl24HourClock True	if the system is using 24-hour time; False if the system is using 12-hour time.
xlNonEnglishFunctions True	if the system is not displaying functions in English.
xlMetric True	if the system is using the metric system; False if the system is using the English measurement system.
xlCurrencySpaceBefore True	if a space is added before the currency symbol.
xlCurrencyBefore True	if the currency symbol precedes the currency values; False if it follows them.
xlCurrencyMinusSign True	if the system is using a minus sign for negative numbers ; False if the system is using parentheses.
xlCurrencyTrailingZeros True	if trailing zeros are displayed for zero currency values.
xlCurrencyLeadingZeros True	if leading zeros are displayed for zero currency values.
xlMonthLeadingZero True	if a leading zero is displayed in months (when months are displayed as numbers).
xlDayLeadingZero True	if a leading zero is displayed in days.
xl4DigitYears True	if the system is using four-digit years ; False if the system is using two-digit years.
xlMDY True	if the date order is month-day-year for dates displayed in the long form; False if the date order is day/month/year.
xlTimeLeadingZero True	if a leading zero is displayed in times.

Date and time settings

If your application writes formatted dates and will be used in other countries, you might want to make sure that the date is in a format familiar to the user. The best approach is to specify a date by using VBA's DateSerial function and let Excel take care of the formatting details (it will use the user's short date format).

The following procedure uses the DateSerial function to assign a date to the StartDate variable. This date is then written to cell A1 with the local short date format.

 Sub WriteDate()     Dim StartDate As Date     StartDate = DateSerial(2007, 4, 15)     Range("A1") = StartDate End Sub 

If you need to do any other formatting for the date, you can write code to do so after the date has been entered into the cell. Excel provides several named date and time formats, plus quite a few named number formats. These are all described in the online help (search for named date/time formats or named numeric formats ).