Custom SubtotalFunction 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 would 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

xlMin = -4139 xlMax = -4136 xlCountNums = -4113 xlCount = -4112 xlAverage = -4106 xlUnknown = 1000 End Enum

Note that if the PivotCell object is not of type xlPivotCellCustomSubtotal then any reference to the CustomSubtotalFunction property generates an error, so it is important to check the PivotcellType property (discussed below) before using the CustomSubtotalFunction property!

0 0

Post a comment