Pivot Table Calculated Fields and Items and Conditional Format and Data Validation 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 U.S. number formats. There is an equivalent .FormulaLocal property 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 .Formula property, 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 .FormulaLocal property of a Range object. 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.

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

This function converts a formula string between U.S. and local formats and languages:

Function ConvertFormulaLocale(sFormula As String, bUSToLocal As Boolean) _ As String


'Use a cell that is likely to be empty!

'This should be changed to suit your own situation With ThisWorkbook.Worksheets(1).Range("IU1") If bUSToLocal Then

.Formula = sFormula

ConvertFormulaLocale = .FormulaLocal Else

.FormulaLocal = sFormula ConvertFormulaLocale = .Formula End If

.ClearContents End With


End Function sFormula is the text of the formula to convert from, and bUSToLocal should be set to True to convert U.S. to local, and False to convert local to U.S.

0 0

Post a comment