AddFieldSet Method

Microsoft Office Web Components Object Model

AddFieldSet Method

       

Adds a custom field set to a PivotTable. Once you have created a custom field set, you can use the AddCalculatedField method to define a custom field. You cannot add a custom field set to your PivotTable if the PivotTable is connected to an online analytical processing (OLAP) data source. Returns a PivotFieldSet object.

expression.AddFieldSet(Name)

expression   Required. An expression that returns a PivotView object.

Name  Required String. Specifies the name of the new field set. The name must be unique within the the PivotFieldSets collection. Must be between 1 and 24 characters in length.

Remarks

You must add a calculated field to the new field set before you add it to the current PivotTable view. 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 calculated field named "Variance" to a new field set in PivotTable1. 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