Excel 2007 Power Programming with VBA (Mr. Spreadsheets Bookshelf)
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.
| 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 |
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.
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).
| 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.
| 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 ).