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: