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.AddCalculatedField(Name, Caption, DataField, Expression)

*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

Applies to | PivotFieldSet Object

See Also | AddCalculatedTotal Method | AddFieldSet Method | DeleteField Method