This sample chapter is taken from the Excel 2002 VBA Programmer's Reference and is reproduced here with the kind permission of Wrox Press. It is also included largely unchanged in the Excel 2003 VBA Programmer's Reference, as chapter 17. The text is available for download as a Word document.

22

 International Issues

If you think that your application may be used internationally, it has to work with any choice of Windows Regional Setting, on any language version of Windows, and with any language choice for the Excel user interface.

 

If you are very lucky, all your potential users will have exactly the same settings as your development machine and you don't need to worry about international issues. However, a more likely scenario is that you do not even know who all your users are going to be, let alone where in the world they will live, or the settings they will use.

 

Any bugs in your application that arise from the disregarding or ignoring of international issues will not occur on your development machine unless you explicitly test for them. However, they will be found immediately by your clients.

 

The combination of Regional Settings and Excel language is called the user's 'locale' and the aim of this chapter is to show you how to write locale-independent VBA applications. In order to do this, we include an explanation of the features in Excel that deal with locale related issues and highlight areas within Excel where locale support is absent or limited. Workarounds are provided for most of these limitations, but some are so problematic that the only solution is to not use the feature at all.

 

The Rules provided in this chapter should be included in your coding standards and used by you and your colleagues. It is easy to write locale-independent code from scratch; it is much more difficult to make existing code compatible with the many different locales in the world today.

Changing Windows Regional Settings and the Office XP UI Language

During this chapter, the potential errors will be demonstrated by using the following three locales:

 

Setting	US	UK	Norway
Decimal Separator	.	.	,
Thousand Separator	,	,	.
Date order	mm/dd/yyyy	dd/mm/yyyy	dd.mm.yyyy
Date separator	/	/	.
Example number: 1234.56	1,234.56	1,234.56	1.234,56
Example date: Feb 10, 2001	02/10/2001	10/02/2001	10.02.2001
Windows and Excel Language	English	English	Norwegian
The text for the Boolean True	True	True	Sann

 

The regional settings are changed using the Regional Settings applet (Regional Options in Windows 2000) in Windows Control Panel, while the Office XP language is changed using the "Microsoft Office Language Settings" program provided with the Office XP Language Packs. Unfortunately, the only way to change the Windows language is to install a new version from scratch.

 

When testing your application, it is a very good idea to use some fictional regional settings, such as having # for the thousand separator, ! for the decimal separator and a YMD date order. It is then very easy to determine if your application is using your settings or some internal default. For completeness, you should also have a machine in your office with a different language version of Windows than the one you normally use.

Responding to Regional Settings and the Windows Language

This section explains how to write applications that work with different regional settings and Windows language versions, which should be considered the absolute minimum requirement.

Identifying the User's Regional Settings and Windows Language

Everything you need to know about your user's Windows Regional Settings and Windows Language version is found in the Application.International property. The online help lists all of the items which can be accessed, though you are unlikely to use more than a few of them. The most notable are:

• XlCountryCode — The language version of Excel (or of the currently active Office language)

• XlCountrySetting — The Windows regional settings location.

• XlDateOrder — the choice of MDY, DMY or YMD order to display dates.

Note that there is no constant that enables us to identify which language version of Windows is installed (but we can get that information from the Windows API if required).

 

Note that "Windows Regional Settings" is abbreviated to WRS in the rest of this chapter and is also described as 'local' settings.

VBA Conversion Functions from an International Perspective

The online help files explain the use of VBA's conversion functions in terms of converting between different data types. This section explains their behavior when converting to and from strings in different locales.

Implicit Conversion

This is the most common form of type conversion used in VBA code and forces the VBA interpreter to convert the data using whichever format it thinks is most appropriate. A typical example of this code is:

 

 

When converting a number to a string, VBA in Office XP uses the WRS to supply either a date string in the user's 'ShortDate' format, the number formatted according to the WRS, or the text for True or False in the WRS language. This is fine, if you want the output as a locally formatted string. If, however, your code assumes you've got a US-formatted string, it will fail. Of course, if you develop using US formats, you won't notice the difference (though your client will).

 

There is a much bigger problem with using implicit conversion if you are writing code for multiple versions of Excel. In previous versions, the number formats used in the conversion were those appropriate for the Excel language being used at run-time (i.e. buried within the Excel object library), which may be different to both US and local formats, and were not affected by changing the WRS.

 

Be very careful with the data types returned from, and used by, Excel and VBA functions. For example, Application.GetOpenFilename returns a Variant containing the Boolean value False if the user cancels, or a String containing the text of the selected file. If you store this result in a String variable, the Boolean False will be converted to a string in the user's WRS language, and may not equal the string "False" that you may be comparing it to. To avoid these problems, use the Object Browser to check the function's return type and parameter types, then make sure to match them, or explicitly convert them to your variable's data type. Applying this recommendation gives us (at least) three solutions to using Application.GetOpenFilename:

Typical code running in Norway:

 

 

If the user cancels, GetOpenFilename returns a variable containing the Boolean value False. Excel converts it to a string to put in our variable, using the Windows Language. In Norway, the string will contain "Usann". If this is compared to the string "False", it doesn't match, so the program thinks it is a valid file name and subsequently crashes.

 

Solution 1:

 

 

Solution 2:

 

 

Solution 3:

 

 

Note that in all three cases, the key point is that we are matching the data type returned by GetOpenFilename (a Variant) with our variable.  If you use the MultiSelect:=True parameter within the GetOpenFileName function, the last of the above solutions should be used.  This is because the vaFile variable will contain an array of file names, or the Boolean False.  Attempting to compare an array with False, or trying to convert it to a string will result in a run-time error.

Date Literals

When coding in VBA, you can write dates using a format of #01/01/2001#, which is obviously Jan 1, 2001. But what is #02/01/2001#? Is it Jan 2 or Feb 1? Well, it is actually Feb 1, 2001. This is because when coding in Excel, we do so in American English, regardless of any other settings we may have, and hence we must use US-formatted date literals (i.e. mm/dd/yyyy format).  If other formats are typed in (such as #yyyy-mm-dd#) Excel will convert them to #mm/dd/yyyy# order.

 

What happens if you happen to be Norwegian or British and try typing in your local date format (which you will do at some time, usually near a deadline)? If you type in a Norwegian-formatted date literal, #02.01.2001#, you get a syntax error which at least alerts you to the mistake you made. However, if you type in dates in a UK format (dd/mm/yyyy format) things get a little more interesting. VBA recognizes the date and so doesn't give an error, but 'sees' that you have the day and month the wrong way round; it swaps them for you. So, typing in dates from Jan 10, 2001 to Jan 15, 2001 results in:

 

You Typed	VBA Shows	Meaning
10/1/2001	10/1/2001	Oct 1, 2001
11/1/2001	11/1/2001	Nov 1, 2001
12/1/2001	12/1/2001	Dec 1, 2001
13/1/2001	1/13/2001	Jan 13, 2001
14/1/2001	1/14/2001	Jan 14, 2001
15/1/2001	1/15/2001	Jan 15, 2001

 

If these literals are sprinkled through your code, you will not notice the errors.

 

It is much safer to avoid using date literals and use the VBA functions DateSerial(Year, Month, Day) or DateValue(DateString), where DateString is a non-ambiguous string such as "Jan 1, 2001". Both of these functions return the corresponding Date number.

IsNumeric(), IsDate()

These two functions test if a string can be evaluated as a number or date according to the WRS and Windows language version. You should always use these functions before trying to convert a string to another data type. We don't have an IsBoolean() function, or functions to check if a string is a US-formatted number or date. Note that IsNumeric() does not recognize a % character on the end of a number and IsDate() does not recognize days of the week.

CStr()

This is the function most used by VBA in implicit data type conversions. It converts a Variant to a String, formatted according to the WRS. When converting a Date type, the 'ShortDate' format is used, as defined in the WRS. Note that when converting Booleans, the resulting text is the English "True" or "False" and is not dependent on any Windows settings. Compare this with the implicit conversion of Booleans, whereby MsgBox "I am " & True results in the True being displayed in the WRS language (i.e. "I am Sann" in Norwegian Regional Settings).

CDbl(), CSng(), CLng(), CInt(), CByte(), CCur() & CDec()

All of these can convert a string representation of a number into a numeric data type (as well as converting different numeric data types into each other). The string must be formatted according to WRS. These functions do not recognize date strings or % characters

CDate(), DateValue()

These methods can convert a string to a Date data type (CDate can also convert other data types to the Date type). The string must be formatted according to WRS and use the Windows language for month names. It does not recognize the names for the days of the week, giving a Type Mismatch error. If the year is not specified in the string, it uses the current year.

CBool()

CBool() converts a string (or a number) to a Boolean value. Contrary to all the other Cxxx() conversion functions, the string must be the English "True" or "False".

Format()

Converts a number or date to a string, using a number format supplied in code. The number format must use US symbols (m, d, s etc), but results in a string formatted according to WRS (i.e. with the correct decimal, thousand and date separators) and the WRS language (for the weekday and month names). For example, the code:

 

 

will result in "Friday 01/01/2001" in the US, but "Fredag 01.01.2001" when with Norwegian settings. If you omit the number format string, it behaves exactly the same as the CStr function (even though online help says it behaves like Str()), including the strange handling of Boolean values, where Format(True) always results in the English "True".  Note that it does not change the date order returned to agree with the WRS, so your code has to determine the date order in use before creating the number format string.

FormatCurrency(), FormatDateTime(), FormatNumber(), FormatPercent()

These functions added in Excel 2000 provide the same functionality as the Format function, but use parameters to define the specific resulting format instead of a custom format string. They correspond to standard options in Excel's Format | Cells | Number dialog, while the Format() function corresponds to the Custom option.  They have the same international behaviour as the Format() function above.

Str()

Converts a number, date or Boolean to a US-formatted string, regardless of the WRS, Windows language or Office language version. When converting a positive number, it adds a space on the left. When converting a decimal fraction, it does not add a leading zero. The following custom function is a extension of Str() which removes the leading space and adds the zero.

sNumToUS()

This function converts a number, date or Boolean variable to a US-formatted string. There is an additional parameter that can be used to return a string using Excel's DATE() function, which would typically be used when constructing .Formula strings.

 

Val()

This is the most common function that I've seen used to convert from strings to numbers. It actually only converts a US-formatted numerical string to a number. All the other string-to-number conversion functions try to convert the entire string to a number and raise an error if they can't. Val(), however, works from left to right until it finds a character that it doesn't recognize as part of a number. Many characters typically found in numbers, such as $ and commas, are enough to stop it recognizing the number. Val() does not recognize US-formatted date strings.

 

Val() also has the dubious distinction of being the only one of VBA's conversion functions to take a specific data type for its input. While all the others use Variants, Val() accepts only a String. This means that anything you pass to Val() is converted to a string (implicitly, i.e. according to the WRS and Windows language), before being evaluated according to US formats!

 

Here myDate is a Date variable containing Feb 10, 2001 and myDbl is a Double containing 1.234.

 

Expression	US	UK	Norway
Val(myDate)	2	10	10.02 (or 10.2)
Val(myDbl)	1.234	1.234	1
Val(True)	0 (=False)	0 (=False)	0 (=False)
Val("SomeText")	0	0	0
Val("6 My St.")	6	6	6

Application.Evaluate

While not normally considered to be a conversion function, Application.Evaluate is the only way to convert a US-formatted date string to a date number. The following two functions IsDateUS() and DateValueUS() are wrapper functions which use this method.

IsDateUS()

The built-in IsDate() function validates a string against the Windows Regional Settings. This function provides us with a way to check if a string contains a US-formatted date.

 

DateValueUS()

The VBA DateValue() function converts a string formatted according to the Windows Regional Settings to a Date type. This function converts a string containing a US-formatted date to a Date type. If the string can not be recognized as a US-formatted date, it returns an Error value, that can be tested for using the IsError() function.

 

Interacting with Excel

VBA and Excel are two different programs that have had very different upbringings. VBA speaks American. Excel also speaks American. However, Excel can also speak in its user's language if they have the appropriate Windows settings and Office language pack installed. On the other hand VBA knows only a little about Windows settings, even less about Office XP language packs. So, we can either do some awkward coding to teach VBA how to speak to Excel in the user's language, or we can just let them converse in American. I very much recommend the latter.

 

Unfortunately, most of the newer features in Excel are not multilingual. Some only speak American, while others only speak in the user's language. We can use the American-only features if we understand their limitations; the others are best avoided. All of them are documented later in the chapter.

Sending Data to Excel

By far the best way to get numbers, dates, Booleans and strings into Excel cells is to do so in their native format. Hence, the following code works perfectly, regardless of locale:

 

 

There is a boundary layer between VBA and Excel. When VBA passes a variable through the boundary, Excel does its best to interpret it according to its own rules. If the VBA and Excel data types are mutually compatible the variable passes straight through unhindered. The problems start when Excel forces us to pass it numbers, dates or Booleans within strings, or when we choose to do so ourselves. The answer to the latter situation is easy — don't do it! Whenever you have a string representation of some other data type, if it is possible, always explicitly convert it to the data type you want Excel to store, before passing it to Excel.

 

Excel requires string input in the following circumstances:

• Setting the formula for a cell, chart series, conditional format or pivot table calculated field.

• Specifying the RefersTo formula for a defined name.

• Specifying an AutoFilter criteria.

• Passing a formula to ExecuteExcel4Macro.

• Setting the number format of a cell, style, chart axis, pivot table field.

• The number format used in the VBA Format() function.

In these cases, we have to ensure that the string that VBA sends to Excel is in US-formatted text — i.e. we use English language formulas and US regional settings. If the string is built within the code, we must be very careful to explicitly convert all our variables to US-formatted strings.

 

Take this simple example:

 

 

We are setting a cell's formula based on a parameter supplied from another routine. Note that the formula is being constructed in the code and we are using US language and regional settings (i.e. the English IF() and using a comma for the list separator). When used with different values for dLimit in different locales, we get the following results:

 

dLimit	US	UK	Norway
100	Works fine	Works fine	Works fine
100.23	Works fine	Works fine	Run-Time Error 1004

 

It fails when run in Norway with any non-integer value for dLimit. This is because we are implicitly converting the variable to a string, which you'll recall uses the Windows Regional Settings number formats. The resulting string that we're passing to Excel is:

 

 

This fails because the IF() function does not have four parameters. If we change the function to read:

 

 

The function will work correctly, as Str() forces a conversion to a US-formatted string.

If we try the same routine with a Date instead of a Double, we come across another problem. The text that is passed to Excel (e.g. for Feb 13, 2001) is:

 

 

While this is a valid formula, Excel interprets the date as a set of divisions, so the formula is equivalent to:

 

 

This is unlikely to ever be true! To avoid this, we have to convert the Date data type to a Double, and from that to a string:

 

 

The function is then the correct (but less readable):

 

 

To maintain readability, we should convert dates to Excel's DATE() function, to give:

 

 

This is also achieved by the sNumToUS() function presented earlier on in the chapter, when the bUseDateFunction parameter is set to True:

 

 

If you call the revised SetLimit procedure with a value of 100.23 and look at the cell that the formula was put into, you'll see that Excel has converted the US string into the local language and regional settings. In Norway, for example, the cell actually shows:

 

 

This translation also applies to number formats. Whenever we set a number format within VBA, we can give Excel a format string which uses US characters (such as 'd' for day, 'm' for month and 'y' for year). When applied to the cell (or style or chart axis), or used in the Format() function, Excel translates these characters to the local versions. For example, the following code results in a number format of dd/mm/åååå when we check it using Format, Cells, Number in Norwegian Windows:

 

 

This ability of Excel to translate US strings into the local language and formats makes it easy for developers to create locale-independent applications. All we have to do is code in American and ensure that we explicitly convert our variables to US-formatted strings before passing them to Excel.

Reading Data from Excel

When reading a cell's value, using its .Value property, the data type which Excel provides to VBA is determined by a combination of the cell's value and its formatting. For example, the number 3000 could reach VBA as a Double, a Currency or a Date (March 18, 1908). The only international issue that concerns us here, is if the cell's value is read directly into a string variable — the conversion will be done implicitly and you may not get what you expect (particularly if the cell contains a Boolean value).

 

As well as when sending data to Excel, the translation between US and local functions and formats occurs when reading data from Excel. This means that a cell's .Formula or .NumberFormat property is given to us in English and with US number and date formatting, regardless of the user's choice of language or regional settings.

 

While for most applications, it is much simpler to read and write using US formulas and formats, we will sometimes need to read exactly what the user is seeing (i.e. in their choice of language and regional settings). This is done by using the xxxLocal versions of many properties, which return (and interpret) strings according to the user's settings. They are typically used when displaying a formula or number format on a UserForm and are discussed in the following section.

The Rules for Working with Excel

1.      Pass values to Excel in their natural format if possible (i.e. don't convert dates / numbers / Booleans to strings if you don't have to). If you have strings, convert them yourself before passing them to Excel.

2.      When you have to convert numbers and dates to strings for passing to Excel (such as in criteria for AutoFilter or .Formula strings), always explicitly convert the data to a US-formatted string, using Trim(Str(MyNumber)), or the sNumToUS() function shown earlier, for all number and date types. Excel will then use it correctly and convert it to the local number/date formats.

3.      Avoid using Date literals (e.g. #1/3/2001#) in your code. It is better to use the VBA DateSerial(), or the Excel DATE() functions, which are not ambiguous.

4.      If possible, use the date number instead of a string representation of a date. Numbers are much less prone to ambiguity (though not immune).

5.      When writing formulas in code to be put into a cell (using the .Formula property), create the string using English functions. Excel will translate them to the local Office language for you.

6.      When setting number formats or using the Format() function, use US formatting characters, e.g. ActiveCell.NumberFormat = "dd mmm yyyy". Excel will translate these to the local number format for you.

7.      When reading information from a worksheet, using .Formula, .NumberFormat etc, Excel will supply it using English formulas and US format codes, regardless of the local Excel language.

Interacting with your Users

The golden rule when displaying data to your users, or getting data from them, is to always respect their choice of Windows Regional Settings and Office UI Language. They should not be forced to enter numbers dates, formulas and/or number formats according to US settings, just because it's easier for you to develop.

Paper Sizes

One of the most annoying things for a user is to print a report from your application, then discover that their printer does not recognise the paper sizes used in your templates. If you use templates for your reports, you should always change the paper size to the user's default size. This can easily be determined by creating a new workbook and reading off the paper size from the PageSetup object.

 

Excel 2002 added the Application.MapPaperSize property, to automatically switch between the standard paper sizes of different countries (e.g. Letter in the US <-> A4 in the UK).  If this is property is set to True, Excel 2002 should take care of paper sizes for you.

Displaying Data

Excel does a very good job of displaying worksheets according to the user's selection of regional settings and language. When displaying data in UserForms or dialog sheets, however, we have to do all the formatting ourselves.

 

As discussed above, Excel converts number and dates to strings according to the WRS by default. This means that we can write code like:

 

 

and be safe in the knowledge that Excel will display it correctly. There are two problems with this approach:

 

1.      Dates will get the default 'ShortDate' format, which may not include 4 digits for the year and will not include a time component. To force a 4-digit year and include a time, use the sFormatDate() function shown later. It may be better, though, to use a less ambiguous date format on UserForms, such as the 'mmm dd, yyyy' format used throughout this book.

2.      Versions of Excel prior to Excel 97 did not use the Windows Regional Settings for their default formats. If you are creating applications for use in older versions of Excel, you can't rely on the correct behavior.

 

The solution is simple – just use the Format() function. This tells VBA to convert the number to a locally-formatted string and works in all versions of Excel from 5.0:

 

Interpreting Data

Your users will want to type in dates and numbers according to their choice of regional settings and your code must validate those entries accordingly and maybe display meaningful error messages back to the user. This means that you have to use the Cxxx() conversion functions and the IsNumeric() and IsDate() validation functions. Unfortunately, these functions all have their problems (such as not recognising the % sign at the end of a number) which require some working around. An easy solution is to the use bWinToNum() and bWinToDate() functions shown at the end of this chapter to perform the validation, conversion and error prompting for you. The validation code for a UserForm will typically be done in the OK button's Click event, and be something like:

 

The xxxLocal Properties

Up until now, we have said that you have to interact with Excel using English language functions and the default US formats. Now we present an alternative situation where your code interacts with the user in his or her own language using the appropriate regional settings. How then, can your program take something typed in by the user (such as a number format or formula) and send it straight to Excel, or display an Excel formula in a message box in the user's own language?

 

Microsoft have anticipated this requirement and have provided us with local versions of most of the functions we need. They have the same name as their US equivalent, with the word "Local" on the end (such as FormulaLocal, NumberFormatLocal etc). When we use these functions, Excel does not perform any language or format coercion for us. The text we read and write is exactly how it appears to the user. Nearly all of the functions that return strings or have string arguments have local equivalents. The following table lists them all and the objects to which they apply:

 

Applies To	These versions of the functions use and return strings according to US number and date formats and English text	These versions of the functions use and return locally-formatted strings and in the language used for the Office UI (or Windows version – see later)
Number/string conversion	Str()	CStr()
Number/string conversion	Val()	CDbl() etc.
Name, Style, Command Bar	.Name	.NameLocal
Range, Chart Series	.Formula	.FormulaLocal
Range, Chart Series	.FormulaR1C1	.FormulaR1C1Local
Range, Style, Chart Data Label, Chart Axes Label	.NumberFormat	.NumberFormatLocal
Range	.Address	.AddressLocal
Range	.AddressR1C1	.AddressR1C1Local
Defined Name	.RefersTo	.RefersToLocal
Defined Name	.RefersToR1C1	.RefersToR1C1Local
Defined Name	.Category	.CategoryLocal

The Rules for Working with your Users

1.      When converting a number or date to a text string for displaying to your users, or setting it as the .Caption or .Text properties of controls, explicitly convert numbers and dates to text according to the WRS, using Format(myNum), or CStr(MyNum) .

2.      When converting dates to strings, Excel does not rearrange the date part order, so Format(MyDate, "dd/mm/yyyy") will always give a DMY date order (but will show the correct date separator). Use Application.International(xlDateOrder) to determine the correct date order — as used in the sFormatDate() function shown at the end of this chapter, or use one of the standard date formats (e.g. ShortDate)

3.      If possible, use locale-independent date formats, such as Format(MyDate, "mmm dd, yyyy"). Excel will display month names according to the user's WRS language.

4.      When evaluating date or number strings which have been entered by the user, use CDate() or CDbl(), to convert the string to a date/number. These will use the WRS to interpret the string. Note that CDbl() does not handle the % character if the user has put one at the end of the number.

5.      Always validate numbers and dates entered by the user before trying to convert them. See the bWinToNum() and bWinToDate() functions at the end of this chapter for an example.

6.      When displaying information about Excel objects, use the xxxLocal properties (where they exist) to display it in your user's language and formats.

7.      Use the xxxLocal properties when setting the properties of Excel objects with text provided by the user (which we must assume is in their native language and format).

Excel 2002's International Options

In the Tools > Options dialog, a new 'International' tab has been added in Excel 2002.  This tab allows the user to specify the characters that Excel uses for the thousand and decimal separators, overriding the Windows Regional Settings.  These options can be read and changed in code, using Application.ThousandSeparator, Application.DecimalSeparator and Application.UseSystemSeparators.

 

Using these new properties we could, for example, print, save (as text) or publish a workbook using local number formats, change the separators being used, print, save (as text) or publish another version for a different target country, then change them back to their original settings.  It is a great pity, though, that Microsoft didn't add the ability to override the rest of the Windows Regional Settings attributes (such as date order, date separator, whether to use (10) or -10 etc.) and it's an omission that makes this feature virtually useless in practice.

 

One problem with using this feature is that it does not change the number format strings used in the =TEXT() worksheet function, so as soon as the option is changed (either in code or through the UI), all cells that use the =TEXT() function will no longer be formatted correctly.  See later in this chapter for a work-around to this problem.

 

The addition of this feature has a big downside for us as developers, though.  The problem is that while these options affect all of Excel's 'xxxLocal' properties and functions (including the Application.International settings), they are ignored by VBA!

 

A few examples highlight the scale of the problem:

 

The VBA Format() function - used almost every time a number is displayed to the user - ignores these options, resulting in text formatted according to the Windows Regional Settings, not those used by Excel.

If the user types numbers into our userforms or inputboxes using the override separators, they will not be recognised as numbers by IsNumeric, CDbl() etc., giving us TypeMismatch errors.

 

The only way to work around this problem is to perform our own switching between WRS and Override separators before displaying numbers to the users and immediately after receiving numbers from them, using the following two functions:

 

 

 

The final problem is that when we are interacting with the user, we should be doing so using the number formats that they are familiar with.  By adding the ability to override the Windows Regional Settings, Excel is introducing a third set of separators for us, and our users, to contend with.  We are therefore completely reliant on the user remembering that override separators have been set, and that they may not be the separators that they are used to seeing (i.e. according to the WRS).

 

Features that don't play by the Rules

The xxxLocal functions discussed in the previous section were all introduced during the original move from XLM functions to VBA in Excel 5.0. They cover most of the more common functions that a developer is likely to use. There were, however a number of significant omissions in the original conversion and new features have been added to Excel since then with almost complete disregard for international issues.

 

This section guides you through the maze of inconsistency, poor design and omission that you'll find hidden within the following of Excel 2002's features. This table shows the methods, properties and functions in Excel which are sensitive to the user's locale, but which do not behave according to the Rules we have stated above:

 

Applies To	US Version	Local Version
Opening a text file	OpenText()	OpenText()
Saving as a text file	SaveAs()	SaveAs()
Application	.ShowDataForm	.ShowDataForm
Worksheet / Range		.Paste / .PasteSpecial
Pivot Table calculated fields and items	.Formula
Conditional formats		.Formula
QueryTables (Web Queries)		.Refresh
Worksheet functions		=TEXT()
Range	.Value
Range	.FormulaArray
Range	.AutoFilter	.AutoFilter
Range		.AdvancedFilter
Application	.Evaluate
Application	.ConvertFormula
Application	.ExecuteExcel4Macro

 

Fortunately, workarounds are available for most of these issues. There are a few, however, that should be completely avoided.

OpenText()

Workbooks.OpenText is the VBA equivalent of opening a text file in Excel by using File | Open. It opens the text file, parses it to identify numbers, dates, booleans and strings and stores the results in worksheet cells. It is discussed in more detail elsewhere in the book. Of relevance to this chapter is the method Excel uses to parse the data file (and how it has changed over the past few versions). In Excel 5, the text file was parsed according to your Windows Regional Settings when opened from the user interface, but according to US formats when opened in code. In Excel 97, this was changed to always use these settings from both the UI and code. Unfortunately, this meant that there was no way to open a US-formatted text file with any confidence that the resulting numbers were correct. Since Excel 5, we have been able to specify the date order to be recognised, on a column-by-column basis, which works very well for numeric dates (e.g. 01/02/2001).

 

Excel 2000 introduced the Advanced button on the Text Import Wizard, and the associated DecimalSeparator and ThousandSeparator parameters of the OpenText() method. These allow us to specify the separators that Excel should use to identify numbers and are welcome additions. It is slightly disappointing to see that we can not specify the general date order in the same way.

 

While Microsoft are to be congratulated for fixing the number format problems in Excel 2000, further congratulations are due for fixing the problem of month and day names in Excel 2002, and for providing a much tidier alternative for distinguishing between US-formatted and locally-formatted text files.

 

Prior to Excel 2002, the OpenText method would only recognize month and day names according the Windows Regional Settings and date orders had to be specified for every date field that weren't in MDY order.  In Excel 2002, the OpenText method has a new Local parameter, with which we can specify whether the text file being imported uses US English formatting throughout, or whether it uses locally-formatted dates, numbers etc.

• If Local:=True, Excel will recognize numbers, dates and month/day names according to the Windows Regional Settings (and the Override decimal and thousand  separators, if set)

• If Local:=False, Excel will recognize numbers, dates and month/day name according to standard US English settings.

In either case, the extra parameters of DecimalSeparator, ThousandSeparator and FieldInfo can be used to further refine the specification (overriding the Local parameter's defaults)

SaveAs()

Workbook.SaveAs is the VBA equivalent of saving a text file in Excel by using File | Save As and choosing a format of Text.

 

In all versions of Excel prior to Excel 2002, this resulted in a US-formatted text file, with a DMY date order and English month and day names etc.

 

In Excel 2002, the SaveAs method has the same local parameter described in the OpenText method above, resulting in a US-formatted or locally-formatted text file, as appropriate.  Note that if a cell has been given a locale-specific date format (i.e. the number format begins with a locale-specifier, such as [$-814] for Norwegian), that formatting will be retained in the text file, regardless of whether it is saved in US or local format.

 

ShowDataForm()

Using ActiveSheet.ShowDataForm is exposing yourself to one of the most dangerous of Excel's international issues. ShowDataForm is the VBA equivalent of the Data | Form menu item. It displays a standard dialog which allows the user to enter and change data in an Excel list/database. When run by clicking the Data | Form menu, the dates and numbers are displayed according to the WRS and changes made by the user are interpreted according to the WRS, which fully complies with the user-interaction rules above.

 

When run from code, using ActiveSheet.ShowDataForm, Excel displays dates and numbers according to US formats but interprets them according to WRS. Hence, if you have a date of Feb 10, 2001, shown in the worksheet in the dd/mm/yyyy order of 10/02/2001, Excel will display it on the data form as 2/10/2001. If you change this to the 11^th (2/11/2001), Excel will store Nov 2, 2001 in the sheet! Similarly, if you are using Norwegian number formats, a number of 1-decimal-234 will be displayed on the form as 1.234. Change that to read 1.235 and Excel stores 1235, one thousand times too big!

 

Fortunately there is an easy workaround for this if your routine only has to work with versions of Excel since Excel 97. Instead of using ShowDataForm, you can select the first cell in the range, then execute the Data | Form menu item itself:

 

 

The following RunMenu routine executes a given menu item, as if it had been clicked by the user. In this case, the data form behaves correctly.

RunMenu()

This routine will run a menu item, given its CommandBar.Control ID (e.g. 860 is the ID for the Data | Form menu item).

 

Pasting Text

When pasting text from other applications into Excel, it is parsed according to the WRS. We have no way to tell Excel the number and date formats and language to recognise. The only workaround is to use a DataObject to retrieve the text from the clipboard, parse it yourself in VBA, then write the result to the sheet.  The following example assumes that the clipboard contains a single US-formatted number:

 

PivotTable Calculated Fields and Items and Conditional Format Formulas

If you are used to using the .Formula property of a range or chart series, you'll know that it returns and accepts formula strings that use English functions and US number formats. There is an equivalent .FormulaLocal property which returns and accepts formula strings as they appear on the sheet (i.e. using the Office UI language and WRS number formats).

Pivot table calculated fields and items and conditional formats also have a .Formula property, but for these objects, it returns and accepts formula strings as they appear to the user, i.e. it behaves in the same way as the .FormulaLocal property of a Range object. This means that to set the formula for one of these objects, we need to construct it in the Office UI language, and according to the WRS!

 

A workaround for this is to use the cell's own .Formula and .FormulaLocal properties to convert between the formats, as shown in the ConvertFormulaLocale() function below.

ConvertFormulaLocale()

This function converts a formula string between US and local formats and languages.

 

Web Queries

While the concept behind Web Queries is an excellent one, they have been implemented with complete disregard to international issues. When the text of the web page is parsed by Excel, all the numbers and dates are interpreted according to your Windows Regional Settings. This means that if a European web page is opened in the US, or a US page is opened in Europe, it is likely that the numbers will be wrong. For example, if the web page contains the text 1.1, it will appear as 1^st Jan on a computer running Norwegian Windows.

 

The WebDisableDateRecognition option for the QueryTable can be used to prevent numbers being recognized as dates and setting Excel's override number and decimal separators can ensure that numbers are recognized correctly, if the web page is displayed in a known format

 

Web queries must be used with great care in a multinational application, using the following approach:

• Set Application.UseSystemSeparators to False,

• Set Application.DecimalSeparator and Application.ThousandSeparator to those used on the web page

• Perform the Query, ensuring WebDisableDateRecognition is set to True

• Reset Application.DecimalSeparator, Application.ThousandSeparator and Application.UseSystemSeparators to their original values.

=TEXT() Worksheet Function

The TEXT() worksheet function converts a number to a string, according to a specified format. The format string has to use formatting characters defined by the Windows Regional Settings (or Excel's International Options override). Hence, if you use =TEXT(NOW(),"dd/mm/yyyy"), you will get "01/02/yyyy" on Norwegian Windows, since Excel will only recognise 'å' as the Norwegian number-format character used for years. Excel does not translate the number-format characters when it opens the file on a different platform. A workaround for this is to create a defined name that reads the number format from a specific cell, then use that definition within the TEXT() function. For example, if you format cell A1 with the date format to use throughout your sheet, you can click on Insert | Name | Define and define a name as:

 

     Name:      DateFormat

     Refers To: =GET.CELL(7,$A$1)

 

Then use =TEXT(Now(),DateFormat) elsewhere in the sheet. The GET.CELL() function is an Excel 4 macro function — which Excel lets us use within define names, though not on the worksheet. This is equivalent to, but much more powerful than the =CELL() worksheet function. The 7 in the example tells GET.CELL() to return the number-format string for the cell.

 

The XLM functions are documented in the XLMACR8.HLP file, available from Microsoft's web site at http://support.microsoft.com/?kbid=143466

Range.Value and Range.FormulaArray

These two properties of a range only break the rules by not having local equivalents. The strings passed to (and returned by) them are in US format. Use the ConvertFormulaLocale() function shown above to convert between US and local versions of formulas.

Range.AutoFilter

The AutoFilter method of a Range object is a very curious beast. We are forced to pass it strings for its filter criteria and hence must be aware of its string handling behaviour. The criteria string consists of an operator (=, >, <, >= etc.) followed by a value. If no operator is specified, the "=" operator is assumed. The key issue is that when using the "=" operator, AutoFilter performs a textual match, while using any other operator results in a match by value. This gives us problems when trying to locate exact matches for dates and numbers. If we use "=", Excel matches on the text that is displayed in the cell, i.e. the formatted number. As the text displayed in a cell will change with different regional settings and Windows language version, it is impossible for us to create a criteria string that will locate an exact match in all locales.

 

There is a workaround for this problem. When using any of the other filter criteria, Excel plays by the rules and interprets the criteria string according to US formats. Hence, a search criterion of ">=02/01/2001" will find all dates on or after 1^st Feb, 2001, in all locales. We can use this to match an exact date by using two AutoFilter criteria. The following code will give an exact match on 1^st Feb, 2001 and will work in any locale:

 

 

Range.AdvancedFilter

The AdvancedFilter method does play by the rules, but in a way that may be undesirable. The criteria used for filtering are entered on the worksheet in the criteria range. In a similar way to AutoFilter, the criteria string includes an operator and a value. Note that when using the "=" operator, AdvancedFilter correctly matches by value and hence differs from AutoFilter in this respect. As this is entirely within the Excel domain, the string must be formatted according to the Windows Regional Settings to work, which gives us a problem when matching on dates and numbers. An advanced filter search criterion of ">1.234" will find all numbers greater then 1.234 in the US, but all numbers greater than 1234 when run in Norway. A criterion of ">02/03/2001" will find all dates after 3^rd Feb in the US, but after 2^nd March in Europe. The only workarounds are to populate the criteria strings from code, before running the AdvancedFilter method, or to use a calculated criteria string, using the =TEXT() trick mentioned above. Instead of a criterion of ">=02/03/2001", to find all dates on or after 3^rd Feb, 2001, we could use the formula:

 

 

Here DateFormat is the defined name introduced above that returns a local date format. If the date is an integer (i.e. does not contain a time component), we could also just use the criteria string ">=36194" and hope that the user realizes that 36194 is actually 3^rd Feb, 2001.

Application.Evaluate, Application.ConvertFormula and Application.ExecuteExcel4Macro

These functions all play by the rules, in that we must use US-formatted strings. They do not, however, have local equivalents. To evaluate a formula that the user may have typed into a UserForm (or convert it between using relative to absolute cell ranges), we need to convert it to US before passing it to Application.Evaluate or Application.ConvertFormula.

The Application.ExecuteExcel4Macro function is used to execute XLM-style functions. One of the most common uses of it is to call the XLM PAGE.SETUP() function, which is much faster than the VBA equivalent. This takes many parameters, including strings, numbers and booleans. Be very careful to explicitly convert all these parameters to US-formatted strings and avoid the temptation to shorten the code by omitting the Str() around each one.

Responding to Office XP Language Settings

One of the major advances starting with the release of Office 200 is that there is a single set of executables, with a set of plug‑in language packs (whereas in prior versions, each language was a different executable, with its own set of bugs). This makes it very easy for a user of Office to have their own choice of language for the user interface, help files etc. In fact, if a number of people share the same computer, each person can run the Office applications in a different language.

 

As developers of Excel applications, we must respect the user's language selection and do as much as we can to present our own user interface in their choice of language.

 

Where does the text come from?

There are three factors that together determine the text seen by the Office user:

Regional Settings Location

The Regional Settings location is chosen on the first tab (called Regional Settings) of the Control Panel Regional Settings applet and defines:

• The day and month names shown in Excel cells for long date formats,

• The day and month names returned by the VBA Format() function,

• The month names recognized by the VBA CDate() function and when typing dates into Excel directly,

• The month names recognized by the Text Import Wizard and the VBA OpenText() method (when the Local parameter is True),

• The number format characters used in the =TEXT() worksheet function,

• The text resulting from the implicit conversion of Boolean values to strings, that is:  "I am " & True.

Office UI Language Settings

The Office User Interface language can be selected by using the "Microsoft Office Language Settings" applet, installed with Office XP and defines:

• The text displayed on Excel's menus and dialog boxes,

• The text for the standard buttons on Excel's message boxes,

• The text for Excel's built-in worksheet functions,

• The text displayed in Excel's cells for Boolean values,

• The text for Boolean values recognized by the Text Import Wizard, the VBA OpenText() method and when typing directly into Excel,

• The default names for worksheets in a new workbook,

• The local names for command bars.

Language Version of Windows

By this, I mean the basic language version of Windows itself. This choice defines:

• The text for the standard buttons in the VBA MsgBox function (i.e. when using the vbMsgBoxStyles constants). Hence, while the text of the buttons on Excel's built-in messages respond to the Office UI language, the text of the buttons on our own messages respond to the Windows language. Note that the only way to discover the Windows language is with a Windows API call.

 

There are some things in Office XP which are 100% (US) English, and don't respond to any changes in Windows language, regional settings or Office UI language, namely:

• The text resulting from the explicit conversion of Boolean values to strings, i.e. all of Str(True), CStr(True) and Format(True) result in "True". Hence, the only way to convert a Boolean variable to the same string that Excel displays for it, is to enter it into a cell, then read the cell's .FormulaLocal property!

• The text of Boolean strings recognized by CBool().

Identifying the Office UI Language Settings

The first step to creating a multilingual application is to identify the user's settings. We can identify the language chosen in Windows Regional Settings by using Application.International(xlCountrySetting), which returns a number that corresponds approximately to the country codes used by the telephone system (e.g. 1 is the USA, 44 is the UK, 47 is Norway etc). We can also use Application.International(xlCountryCode) to retrieve the user interface language using the same numbering system. This method has worked well in previous versions of Excel, where there were only 30 or so languages from which to choose your copy of Office.

 

Beginning with Office 2000, things have changed a little. By moving all the language configuration into separate language packs, Microsoft can support many more languages with relative ease. If you use the Object Browser to look at the msoLanguageID constants defined in the Office object library, you'll see that there are over 180 languages and dialects listed!

 

We can use the following code to find out the exact Office UI language, then decide whether we can display our application in that language, a similar language, or revert to a default language (as shown in the following section):

Creating a Multilingual Application

How far to go?

When developing a multilingual application, you have to balance a number of factors, including:

• The time and cost spent developing the application,

• The time and cost spent translating the application,

• The time and cost spent testing the translated application,

• The increased sales from having a translated version,

• Improved ease-of-use, and hence reduced support costs,

• The requirement for multi-lingual support,

• Should you create language-specific versions, or use add-on language packs?

 

You also have to decide how much of the application to translate, and which languages to support:

• Translate nothing,

• Translate only the packaging and promotional documentation,

• Enable the code to work in a multilingual environment (e.g. month names etc),

• Translate the user interface (menus, dialogs, screens and messages),

• Translate the help files, examples and tutorials,

• Customize the application for each location (e.g. to use local data feeds),

• Support left-to-right languages only,

• Support right-to-left languages (and hence redesign your UserForms),

• Support Double-Byte-Character-Set languages (e.g. Japanese).

 

The decision on how far to go will depend to a large extent on your users, your budget, and the availability of translators.

A Suggested Approach

It is doubtful that creating a single Excel application to support all 180+ Office languages will make economic sense, but the time spent in making your application support a few of the more common languages will often be a wise investment. This will, of course, depend on your users and whether support for a new language is preferable to new features!

 

The approach that I take is to write the application to support multiple languages and provide the user with the ability to switch between the installed languages or conform to their choice of Office UI Language. I develop the application in English, then have it translated into one or two other languages depending on my target users. I will only translate it into other languages if there is sufficient demand.

How to Store String Resources

When creating multilingual applications, we cannot hard-code any text strings that will be displayed to the user; we must look them up in a string resource. The easiest form of string resource is a simple worksheet table. Give all your text items a unique identifier and store them in a worksheet, one row per identifier and one column for each supported language. You can then look up the ID and return the string in the appropriate language using a simple VLOOKUP() function. You will need to do the same for all your menu items, worksheet contents and UserForm controls. The following code is a simple example, which assumes you have a worksheet called shLanguage that contains a lookup table which has been given a name of rgTranslation. It also assumes you have a public variable to identify which column to read the text from. The variable would typically be set in an Options type screen. Note that the code shown below is not particularly fast and is shown as an example. A faster (and more complex) routine would read the entire column of IDs and selected language texts into two static VBA arrays, then work from those; only reading in a new array when the language selection was changed.

 

 

Many of your messages will be constructed at runtime. For example, you may have code to check that a number is within certain boundaries:

 

 

This would mean that we have to store two text strings with different IDs in our resource sheet, which is both inefficient and much harder to translate. In the example given, we would probably not have a separate translation string for the full stop. Hence, the maximum value would always come at the end of the sentence, which may not be appropriate for many languages. A better approach is to store the combined string with placeholders for the two numbers, and substitute the numbers at runtime (using the custom ReplaceHolders() function, shown at the end of the chapter.

:

 

 

The translator (who may not understand your program) can construct a correct sentence, inserting the values at the appropriate points.

Working in a Multilingual Environment

Allow Extra Space

It is a fact that most other languages use longer words than the English equivalents. When designing our UserForms and worksheets, we must allow extra room for the non-English text to fit in the controls and cells. A good rule-of-thumb is to make your controls 1.5 times the width of the English text.

Using Excel's Objects

The names that Excel gives to its objects when they are created often depend on the user's choice of Office UI Language. For example, when creating a blank workbook using Workbooks.Add, it will not always be called "Bookn", and the first worksheet in it will not always be called "Sheet1". With the German UI, for example, they are called "Mappen" and "Tabelle1" respectively. Instead of referring to these objects by name, you should create an object reference as they are created, then use that object elsewhere in your code:

 

 

Working with CommandBarControls can also be problematic. For example, you may want to add a custom menu item to the bottom of the Tools menu of the worksheet menu bar. In an English-only environment, you may write something like:

 

 

This code will fail if your user has a UI language other than English. While Excel recognises English names for command bars themselves, it does not recognise English names for the controls placed on them. In this example, the "Tools" dropdown menu is not recognised. The solution is to identify CommandBar.Controls by their ID and use FindControl to locate them. 30007 is the ID of the Tools popup menu:

 

Sub AddHelloButton()

   Dim cbTools As CommandBarPopup

   Dim cbCtl As CommandBarButton

 

 

   Set cbCtl = cbTools.CommandBar.Controls.Add(msoControlButton)

 

   cbCtl.Caption = "Hello"

   cbCtl.OnAction = "MyRoutine"

End Sub

 

There is an additional problem with CommandBar names in some locales and object libraries (e.g. the Dutch VBE Commandbars), in that the CommandBar name (which should always be the same US English string) has been erroneously localized.  The only sure method of working with commandbars is to avoid using any names in code, using FindControl extensively instead.  This approach is somewhat complicated, though, as the same control can occur on many commandbars and FindControl may not return the control that you want.  Most developers use the English CommandBar names.

 

Using SendKeys

In the best of cases, the use of SendKeys should be avoided if at all possible. It is most often used to send key combinations to Excel, in order to activate a menu item or navigate a dialog box. It works by matching the menu item or dialog control accelerator keys, in the same way that you can use Alt+key combinations to navigate Excel using the keyboard. When used in a non-English version of Excel, it is highly unlikely that the key combinations in the SendKeys string will match up with the menus and dialogs, having potentially disastrous results.

 

For example, SendKeys "%DB" will bring up the Subtotals dialog in English Excel, but will Quit Excel when run with the German UI. Instead of using SendKeys to trigger menu items, you should use the RunMenu routine presented earlier in this chapter to execute a menu item by its CommandBarControl ID.

The Rules for Developing a Multilingual Application

1.      Decide early in the analysis phase the level of multilingual support that you are going to provide, then stick to it.

2.      Do not include any text strings within your code. Always look them up in a table.

3.      Never construct sentences by concatenating separate text strings, as the foreign language version is unlikely to use the same word order. Instead use place-holders in your text and replace the place-holder at runtime.

4.      When constructing UserForms, always make the controls bigger than you need for the English text; most other languages use longer words.

5.      Do not try to guess the name that Excel gives to objects that you create in code. For example, when creating a new workbook, the first sheet will not always be "Sheet1".

6.      Do not refer to command bar controls by their caption. While you can refer to command bars themselves by their English name, you must refer to the menu items by their ID (for built-in items) or tag (for custom items).

7.      Do not use SendKeys

Some Helpful Functions

In addition to some of the custom functions already presented, such as RunMenu and IsDateUS, here are some more functions that I have found to be very useful when creating multinational applications. Note that the code has been written to be compatible with all versions of Excel from 5.0 to 2002 and hence avoids the use of newer VBA constructs (such as giving optional parameters specific data types).

bWinToNum()

This method checks if a string contains a number formatted according to the Windows Regional Settings and converts it to a Double. The function returns True or False to indicate the success of the validation, and optionally displays an error message to the user. It is best used as a wrapper function when validating numbers entered by a user, as shown in the 'Interacting with your users' section above.  Note that if the user has used Excel's International Options to override the WRS decimal and thousands separator, the OverrideToWRS function must be used to ensure we send a WRS-formatted string to this function.

 

bWinToDate()

This provides the same functionality as bWinToNum(), but for dates instead of numbers.

 

sFormatDate()

This function formats a date according to the Windows Regional Settings, using a 4-digit year and optionally including a time string in the result.

 

ReplaceHolders()

This function replaces the placeholders in a string with values provided to it.

 

Summary

It is possible to create an Excel application that will work on every installation of Excel in the world and support all 180-plus Office languages, but it is unlikely to be economically viable.

 

If you have a limited set of users and you are able to dictate their Language and Windows Regional Settings, you can create your application without worrying about international issues. Even if this is the case, you should get into the habit of creating locale-independent code. The requirement for locale-independence should be included in your analysis, design and coding standards. It is much, much easier and cheaper to write locale-independent code at the onset than to rework an existing application.

 

At a minimum, your application should work regardless of the user's choice of Windows Regional Settings or Windows or Office UI Language or whether they have set non-standard thousand and decimal separators using Tools > Options > International. You should be able to achieve this by following the rules listed in this chapter.

 

The following Excel features don't play by the rules and have to be treated very carefully:

• OpenText

• SaveAs to a text file

• ShowDataForm

• Pasting text from other applications

• The .Formula property in all its guises

• <range>.Value

• <range>.FormulaArray

• <range>.AutoFilter

• <range>.AdvancedFilter

• The =TEXT() worksheet function

• Application.Evaluate

• Application.ConvertFormula

• Application.ExecuteExcel4Macro

• Web Queries

 

There are also some features in Excel that you may have to avoid completely:

• SendKeys

• Using True and False in imported text files