42
Excel 2007’s International Options
In the Office Menu➪Excel Options dialog, the Advanced section contains Editing Options that allow the
user to specify the characters that Excel uses for the thousands and decimal separators, overriding the
Windows Regional Settings. These options can be read and changed in code, using
Application.ThousandsSeparator, Application.DecimalSeparator, and
Application.UseSystemSeparators.
Using these properties you 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, and 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, and so on), 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
=TEXTworksheet function., So as soon as the option is changed (either in code or through the UI), all
cells that use the =TEXTfunction will no longer be formatted correctly. See later in this chapter for a
workaround.
There is a big problem with this feature, in that while these options affect all of Excel’s xxxLocalprop-
erties and functions (including the Application.Internationalsettings), they are ignored by VBA.
Acouple examples highlight the scale of the problem:
❑
The VBAFormatfunction—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 your UserForms or InputBoxes using the override separators,
they will not be recognized as numbers by IsNumeric, CDbl, and so on, resulting in
TypeMismatcherrors.
The only way to work around this problem is to perform your 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:
Function WRSToOverride(ByVal sNumber As String) As String
Dim sWRS As String, sWRSThousand As String, sWRSDecimal As String
Dim sXLThousand As String, sXLDecimal As String
‘Only do for Excel 2002 and greater
If Val(Application.Version) >= 10 Then
‘Only do if the user is not using System Separators
If Not Application.UseSystemSeparators Then
‘Get the separators used by the Windows Regional Settings
sWRS = Format(1000, “#,##0.00”)
sWRSThousand = Mid(sWRS, 2, 1)
552
Chapter 25: International Issues
40
sWRSDecimal = Mid(sWRS, 6, 1)
‘Get the override separators used by Excel
sXLThousand = Application.ThousandsSeparator
sXLDecimal = Application.DecimalSeparator
‘Swap from WRS’ to Excel’s separators
sNumber = Replace(sNumber, sWRSThousand, vbTab)
sNumber = Replace(sNumber, sWRSDecimal, sXLDecimal)
sNumber = Replace(sNumber, vbTab, sXLThousand)
End If
End If
‘Return the converted string
WRSToOverride = sNumber
End Function
WRSToOverrideconverts between WRS and Excel’s number formats, and returns a string using Excel’s
Override formatting. sNumberis a string containing a WRS-formatted number:
Function OverrideToWRS(ByVal sNumber As String) As String
Dim sWRS As String, sWRSThousand As String, sWRSDecimal As String
Dim sXLThousand As String, sXLDecimal As String
‘Only do for Excel 2002 and greater
If Val(Application.Version) >= 10 Then
‘Only do if the user is not using System Separators
If Not Application.UseSystemSeparators Then
‘Get the separators used by the Windows Regional Settings
sWRS = Format$(1000, “#,##0.00”)
sWRSThousand = Mid$(sWRS, 2, 1)
sWRSDecimal = Mid$(sWRS, 6, 1)
‘Get the override separators used by Excel
sXLThousand = Application.ThousandsSeparator
sXLDecimal = Application.DecimalSeparator
‘Swap from Excel’s to WRS’ separators
sNumber = Replace(sNumber, sXLThousand, vbTab)
sNumber = Replace(sNumber, sXLDecimal, sWRSDecimal)
sNumber = Replace(sNumber, vbTab, sWRSThousand)
End If
End If
‘Return the comverted string
OverrideToWRS = sNumber
End Function
553
Chapter 25: International Issues
48
OverrideToWRSconverts between WRS and Excel’s number formats, and returns the string using WRS’
formatting. sNumberis a string containing an Excel Override formatted number.
The final problem is that when you are interacting with users, you 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 you, and your users, to contend with. You are therefore
completely reliant on the users remembering that override separators have been set, and that they may
not be the separators that the users are used to seeing (that is, according to the WRS).
I strongly recommend that your application checks if Application.UseSystemSeparatorsis False
and displays a warning message to the user, suggesting that it be turned on, so number formatting is set
using Control Panel rather than Excel’s overrides:
If Not Application.UseSystemSeparators Then
MsgBox “Please set the required number formatting using Control Panel”
Application.UseSystemSeparators = True
End If
Features That Don’t Play by the Rules
The xxxLocalfunctions discussed in the previous section were all introduced during the original move
from XLM functions to VBAin Excel 5.0. They cover most of the more common functions that a devel-
oper 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 Excel 2007 features. This table shows the methods, properties, and functions
in Excel that are sensitive to the user’s locale, but that do not behave according to the rules stated in pre-
vious sections.
Applies to
U.S. Version
Local Version
Opening a text file
OpenText
OpenText
Saving as a text file
SaveAs
SaveAs
Application
.ShowDataForm
.ShowDataForm
Worksheet, Range
.Paste, .PasteSpecial
PivotTable calculated fields and items
.Formula
Conditional formats
.Formula
QueryTables (Web Queries)
.Refresh
Worksheet functions
=TEXT
554
Chapter 25: International Issues
48
Applies to
U.S. Version
Local Version
Range
.Value, .Formula
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.
The OpenText Function
Workbooks.OpenTextis the VBAequivalent of opening a text file in Excel by using Office Menu➪
Open. It opens the text file, parses it to identify numbers, dates, Booleans, and strings, and stores the
results in worksheet cells. 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 U.S. 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 U.S.-formatted text file with any confidence that the resulting numbers were correct. Since Excel
5, you have been able to specify the date order to be recognized, on a column-by-column basis, which
works very well for numeric dates (for example, 01/02/2007).
Excel 2000 introduced the Advanced button on the Text Import Wizard, and the associated
DecimalSeparatorand ThousandsSeparatorparameters of the OpenTextmethod. These parameters
allow you to specify the separators that Excel should use to identify numbers, and they are welcome addi-
tions. It is slightly disappointing to see that you cannot specify the general date order in the same way:
Workbooks.OpenText filename:=”DATA.TXT”, _
dataType:=xlDelimited, tab:=True, _
DecimalSeparator:=”,”, ThousandsSeparator:=”.”
While Microsoft is to be congratulated for fixing the number format problems in Excel 2000, further con-
gratulations are due for fixing the problem of month and day names in Excel 2002, and for providing a
much tidier alternative for distinguishing between U.S.-formatted and locally formatted text files.
Prior to Excel 2002, the OpenTextmethod would only recognize month and day names according to the
Windows Regional Settings, and date orders had to be specified for every date field that wasn’t in MDY
order. In Excel 2002, the OpenTextmethod was given a Localparameter, with which you can specify
whether the text file being imported uses U.S. English formatting throughout, or whether it uses locally
formatted dates, numbers, and so on:
555
Chapter 25: International Issues
38
❑
If Local:=True, Excel will recognize numbers, dates, and month and day names according to
the Windows Regional Settings (and the Override DecimalSeparatorand
ThousandsSeparatorseparators, if set).
❑
If Local:=False, Excel will recognize numbers, dates, and month and day names according to
standard U.S. English settings.
In either case, the extra parameters of DecimalSeparator, ThousandsSeparator, and FieldInfocan
be used to further refine the specification (overriding the Localparameter’s defaults).
The SaveAs Function
Workbook.SaveAsis the VBAequivalent of saving a text file in Excel by using Office Menu➪Save As
and choosing a format of Text.
In all versions of Excel prior to Excel 2002, this resulted in a U.S.-formatted text file, with a DMYdate
order, English month and day names, and so on.
In Excel 2002, the SaveAsmethod was given the same Localparameter described in the OpenText
method in the previous section, resulting in a U.S.-formatted or locally formatted text file, as appropri-
ate. Note that if a cell has been given a locale-specific date format (that is, 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 U.S. or local format:
ActiveWorkbook.SaveAs “Data.Txt”, xlText, local:=True
The ShowDataForm Sub Procedure
Using ActiveSheet.ShowDataFormmeans exposing yourself to one of the most dangerous of Excel’s
international issues. ShowDataFormis the VBAequivalent of the pre-2007 Data➪Form menu item
(which is not available by default in the Excel 2007 Ribbon, but can be added to the QAT by selecting the
Form command from the All Commands list). It displays a standard dialog that allows the user to enter
and change data in an Excel list or database. When run from Excel, 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 stated previously.
When used in code, ActiveSheet.ShowDataFormdisplays dates and numbers according to U.S. for-
mats but interprets them according to WRS. Hence, if you have a date of February 10, 2007, shown
in the worksheet in the dd/mm/yyyy order of 10/02/2007, Excel will display it on the data form as
2/10/2007. If you change this to the 11th (2/11/2007), Excel will store November 2, 2007 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.
Because this is a rarely used feature, our suggestion is to leave it buried in the Ribbon command well
and write your own data-entry UserForm.
556
Chapter 25: International Issues
38
Pasting Text
When pasting text from other applications into Excel, it is parsed according to the WRS. There is no way
to tell Excel the number and date formats and language to recognize. The only workaround is to use a
DataObjectto retrieve the text from the clipboard, parse it yourself in VBA, then write the result to the
sheet. For clarity, the following example assumes that the clipboard contains a single U.S.-formatted
number and that it should be enhanced to check for U.S.-formatted dates as well:
Sub ParsePastedNumber()
Dim oDO As DataObject
Dim sText As String
‘Create a new data object
Set oDO = New DataObject
‘Read the contents of the clipboard into the DataObject
oDO.GetFromClipboard
‘Get the text from the DataObject
sText = oDO.GetText
‘If we know the text is in a US format,
‘use Val() to convert it to a number
ActiveCell.Value = Val(sText)
End Sub
PivotTable Calculated Fields and Items, and Conditional
Format and Data Validation Formulas
If you are used to using the .Formulaproperty of a range or chart series, you’ll know that it returns and
accepts formula strings that use English functions and U.S. number formats. There is an equivalent
.FormulaLocalproperty that returns and accepts formula strings as they appear on the sheet (using the
Office UI language and WRS number formats).
PivotTable calculated fields and items and conditional formats also have a .Formulaproperty, but for
these objects, it returns and accepts formula strings as they appear to the user—that is, it behaves in the
same way as the .FormulaLocalproperty of a Rangeobject. This means that to set the formula for one
of these objects, you need to construct it in the Office UI language, and according to the WRS.
Aworkaround for this is to use the cell’s own .Formulaand .FormulaLocalproperties to convert
between the formats, as shown in the following ConvertFormulaLocalefunction.
This function converts a formula string between U.S. and local formats and languages:
Function ConvertFormulaLocale(sFormula As String, bUSToLocal As Boolean) _
As String
On Error GoTo ERR_BAD_FORMULA
‘Use a cell that is likely to be empty!
557
Chapter 25: International Issues
Documents you may be interested
Documents you may be interested