Features That Dont 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 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 previous 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

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.

0 0

Post a comment