Microsoft Office Tutorials and References
In Depth Information
20.10.2 CustomSubtotalFunction Property
Dim ws As Worksheet
Set ws = ActiveSheet
' Note: the following code will fail if
' the rng is not a data cell.
Set rng = ws.Range("E5")
Debug.Print "Column items:"
For Each pi In rng.PivotCell.ColumnItems
Debug.Print pi.Name
Debug.Print "Row items:"
For Each pi In rng.PivotCell.RowItems
Debug.Print pi.Name
Debug.Print "Data Field:"
Debug.Print rng.PivotCell.DataField.Name
End Sub
The output of this procedure is:
Column items:
Row items:
Data Field:
Sum of Transactions
As mentioned, if we change the range to cell C5, the line:
For Each pi In rng.PivotCell.ColumnItems
will generate the unhelpful error message "Application defined or object-defined error".
20.10.2 CustomSubtotalFunction Property
As you no doubt know, a pivot table can display subtotals, as shown in Figure 20-6 , which shows
sums by city. Now, the subtotal type can be set to automatic (the default) or to one of several
different custom values: sum, count, average, and so on. When a subtotal is set to a custom type,
the PivotCell object of the cells that show the subtotal type (not the subtotal values) have type
xlPivotCellCustomSubtotal (see the discussion of the PivotCellType property). For instance, if we
changed the subtotal by city to average, then the PivotCell object for cells A9, B9, and C9 in
Figure 20-6 w ould be of type xlPivotCellCustomSubtotal.
The CustomSubtotalFunction property of the PivotCell object returns the type of aggregate
function for a custom subtotal cell. The return values are defined by the following enum.
Enum XlConsolidationFunction
xlVarP = -4165
xlVar = -4164
xlSum = -4157
xlStDevP = -4156
xlStDev = -4155
xlProduct = -4149
Search JabSto ::

Custom Search