Excel s International Options

In the Office Menu O 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 =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 workaround.

There is a big problem with this feature, in that while these options affect all of Excel's xxxLocal properties and functions (including the Application.International settings), they are ignored by VBA.

A couple 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 your UserForms or InputBoxes using the override separators, they will not be recognized as numbers by isNumeric, CDbl, and so on, resulting in TypeMismatch errors.

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)

'Get the override separators used by Excel sXLThousand = Application.ThousandsSeparator sXLDecimal = Application.DecimalSeparator

'Swap from WRS' to Excel's sNumber = Replace(sNumber, sNumber = Replace(sNumber, sNumber = Replace(sNumber, End If End If separators sWRSThousand, vbTab) sWRSDecimal, sXLDecimal) vbTab, sXLThousand)

'Return the converted string WRSToOverride = sNumber

End Function

WRSToOverride converts between WRS and Excel's number formats, and returns a string using Excel's Override formatting. sNumber is 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' sNumber = Replace(sNumber, sNumber = Replace(sNumber, sNumber = Replace(sNumber, End If End If

'Return the comverted string OverrideToWRS = sNumber separators sXLThousand, vbTab) sXLDecimal, sWRSDecimal) vbTab, sWRSThousand)

End Function

OverrideToWRS converts between WRS and Excel's number formats, and returns the string using WRS' formatting. sNumber is 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.UseSystemSeparators is 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

0 0

Post a comment