Class PivotTableChangeList (Excel VBA)

The class PivotTableChangeList represents the list of changes a user has made to value cells in a PivotTable report based on an OLAP data source. To use a PivotTableChangeList class variable it first needs to be instantiated, for example

Dim ptc as PivotTableChangeList
Set ptc = ActiveCell.PivotTable.ChangeList

Add

Adds a ValueChange object to the specified PivotTableChangeList collection.

The Add method enables you to add ValueChange objects that represent changes to the PivotTable report through code. Doing so will add to the UPDATE CUBE statement that Excel constructs based on this change list. Note that if the user changes the allocation settings so that not all changes have the same settings, Excel will run multiple UPDATE CUBE statements, one for each group of changes that were made while the same settings were applied.

Add (Tuple, Value, AllocationValue, AllocationMethod, AllocationWeightExpression)

Dim strTuple As String: strTuple = 
Dim dblValue As Double: dblValue = 
Dim vlc As ValueChange
Set vlc = ActiveCell.PivotTable.ChangeList.Add(Tuple:=strTuple, Value:=dblValue)

Arguments

The following arguments are required:

Tuple (String) - The MDX tuple of the value to change in the OLAP data source.

Value (Double) - The value to commit.

Optional arguments

The following arguments are optional

AllocationValue - The value to allocate when performing what-if analysis. If this parameter is not supplied, the default allocation value of the OLAP server will be used

AllocationMethod - The method to use to allocate this value when performing what-if analysis. If this parameter is not supplied, the default allocation method of the OLAP server will be used

AllocationWeightExpression - The MDX weight expression to use for this value when performing what-if analysis. If this parameter is not supplied, the default allocation weight expression of the OLAP server will be used

Count

Returns a Long value that represents the number of objects in the collection.

Dim lngCount As Long
lngCount = ActiveCell.PivotTable.ChangeList.Count

Item

Returns a single ValueChange object from the specified PivotTableChangeList collection.

Item (Index)

Index: The MDX name (tuple) or index number of the object.

Dim vlcItem As ValueChange
Set vlcItem = ActiveCell.PivotTable.ChangeList(Index:=1)