Class ValueChange (Excel VBA)

The class ValueChange represents a value that has been changed in a PivotTable report that is based on an OLAP data source. To use a ValueChange class variable it first needs to be instantiated, for example

Dim vlc as ValueChange
Set vlc = ActiveCell.PivotTable.ChangeList(Index:=1)

For Each

Here is an example of processing the ValueChange items in a collection.

Dim vlcChangeList As ValueChange
For Each vlcChangeList In ActiveCell.PivotTable.ChangeList
	
Next vlcChangeList

AllocationMethod

Returns the method to use to allocate this value when performing what-if analysis. Possible return values are xlEqualAllocation - Use equal allocation, xlWeightedAllocation - Use weighted allocation.

The AllocationMethod property corresponds to the Allocation Method setting in the What-If Analysis Settings dialog box for a PivotTable report based on an OLAP data source as it was set at the time that this change was originally applied. If the specified ValueChange object was created by using the Add method of the PivotTableChangeList collection, and the corresponding AllocationMethod parameter was not supplied, the default allocation method of the OLAP server is returned.

Dim xamAllocationMethod As XlAllocationMethod
xamAllocationMethod = ActiveCell.PivotTable.ChangeList(1).AllocationMethod

AllocationValue

Returns the value to allocate when performing what-if analysis. Possible return values are xlAllocateIncrement - Increment based on the old value, xlAllocateValue - The value entered divided by the number of allocations.

The AllocationValue property corresponds to the Value to Allocate setting in the What-If Analysis Settings dialog box for a PivotTable report based on an OLAP data source as it was set at the time that this change was originally applied. If the specified ValueChange object was created by using the Add method of the PivotTableChangeList collection, and the corresponding AllocationValue parameter was not supplied, the default allocation value of the OLAP server is returned.

Dim xavAllocationValue As XlAllocationValue
xavAllocationValue = ActiveCell.PivotTable.ChangeList(1).AllocationValue

AllocationWeightExpression

Returns the MDX weight expression to use for this value when performing what-if analysis.

The AllocationWeightExpression property corresponds to the Weight Expression setting in the What-If Analysis Settings dialog box for a PivotTable report based on an OLAP data source as it was set at the time that this change was originally applied. If the specified ValueChange object was created by using the Add method of the PivotTableChangeList collection, and the corresponding AllocationWeightExpression parameter was not supplied, the default weight expression of the OLAP server is returned.

Dim strAllocationWeightExpression As String
strAllocationWeightExpression = ActiveCell.PivotTable.ChangeList(1).AllocationWeightExpression

Delete

Deletes the specified ValueChange object from the PivotTableChangeList collection.

ActiveCell.PivotTable.ChangeList(1).Delete

Order

Returns a value that indicates the order in which this change was performed relative to other changes in the PivotTableChangeList collection.

The value of the Order property is automatically assigned by Excel based on the order that the user applied the changes to value cells in the PivotTable report. If multiple changes were applied in one operation, Excel will arbitrarily assign the order within that set of changes.

Dim lngOrder As Long
lngOrder = ActiveCell.PivotTable.ChangeList(1).Order

PivotCell

Returns a PivotCell object that represents the cell (tuple) that was changed.

When the value of the VisibleInPivotTable property of the specified ValueChange object is True, the PivotCell property returns a PivotCell object for the cell (tuple) that was changed. When the value of the VisibleInPivotTable property of the specified ValueChange object is False, the PivotCell property returns NULL.

Dim pvtlcelPivotCell As PivotCell
Set pvtlcelPivotCell = ActiveCell.PivotTable.ChangeList(1).PivotCell

Tuple

Returns the MDX tuple of the value that was changed in the OLAP data source.

Dim strTuple As String
strTuple = ActiveCell.PivotTable.ChangeList(1).Tuple

Value

Returns the value that the user entered in the cell or that the formula in the cell was evaluated to when the UPDATE CUBE statement was last run against the OLAP data source.

Dim dblValue As Double
dblValue = ActiveCell.PivotTable.ChangeList(1).Value

VisibleInPivotTable

Returns whether the cell (tuple) is currently visible in the PivotTable report.

Returns True if the cell (tuple) is currently visible in the PivotTable report; otherwise, False.

Dim booVisibleInPivotTable As Boolean
booVisibleInPivotTable = ActiveCell.PivotTable.ChangeList(1).VisibleInPivotTable