Programming Excel with VBA and .NET

Name

calculatedfields.Add(Name, Formula, [UseStandardFormula])

Synopsis

Adds a calculated pivot field to the pivot table’s fields list and returns the PivotField object.

Argument	Settings
Name	The name of the pivot field to create.
Formula	The Excel formula for the calculation.
UseStandardFormula	True evaluates field names using U.S. English settings; False evaluates names using the user’s locale settings. Default is False.

The Formula argument omits the equals sign (=) and can’t include cell references. The lack of cell references means you have to calculate relative values in code if you want to use them in a calculated field. The following code finds the minimum value of SalesRank and then uses that value to create a RelativeRank calculated field:

Sub NewCalcField( ) Dim pt As PivotTable, pfProduct As PivotField, _ pfCalc As PivotField, min As Single ' Uncomment this line to create pivot table. 'CreatePivotTable ' Get pivot table. Set pt = Worksheets("BookSales").PivotTables(1) ' Show detail for Product name field. Set pfProduct = pt.PivotFields("ProductName") pfProduct.Orientation = xlRowField pfProduct.LabelRange.ShowDetail = True ' Find the minimum sales rank. min = WorksheetFunction.min(pt.DataFields(1).DataRange) 'Debug.Print "Min rank: " & min ' Delete field if it exists, ignore error if it doesn't. On Error Resume Next pt.PivotFields("RelativeRank").Delete On Error GoTo 0 ' Create calculated pivot field. Set pfCalc = pt.CalculatedFields.Add("RelativeRank", _ ...