AddCalculatedField Method

Adds a calculated field to a PivotTable. A calculated field can be used like any other field for sorting, filtering, or grouping. You must use  the AddFieldSet method to create a custom field set before you add a calculated field. Returns a PivotField object.

expression   Required. An expression that returns a PivotFieldSet object that was created by the AddFieldSet method.

Name  Required String. The name of the calculated field. The name must be unique within the field set.

Caption  Required String. The caption displayed for the calculated field in the PivotTable user interface. Although this argument is required, you can assign a blank string to the caption.

DataField  Required String. The name of the new field that is created in the underlying recordset for the PivotTable.

Expression  Required String. The expression used to calculate the items in the new field. The expression must be compatible with the Jet expression service.

Remarks

Custom field sets can contain only one calculated field. Adding a second calculated field to a custom field set results in a run-time error.

Example

This example adds a new field set named "Variance" to PivotTable1. Within the new field set, a calculated field with the caption "Budget Variance" is created. The calculated field is then inserted into the PivotTable view.

Sub TestAddFieldSet()

    Dim vwView
    Dim fsNewFieldSet

    Set vwView = PivotTable1.ActiveView

    ' Add a custom field set to the PivotTable.
    Set fsNewFieldSet = vwView.AddFieldSet("Variance")

    ' Add a calculated total to the newly created field set.
    fsNewFieldSet.AddCalculatedField "Variance", _

    "Budget Variance", "fldVariance", _

    "Budget / Actual"

    ' Insert the calculated field into the data axis.
    vwView.DataAxis.InsertFieldSet fsNewFieldSet

End Sub