Microsoft Office Tutorials and References
In Depth Information
Understanding PivotCaches
PivotCharts are great — they are equipped with Field buttons so you can choose which items in
which fields you want to see. Whatever field setting you select on a PivotChart will make the same
change to its PivotTable. The following macro will toggle between showing and hiding the Field
buttons on your PivotChart:
Sub ShowHidePivotChartFieldButtons()
ActiveSheet.ChartObjects(1).Activate
With ActiveChart
.HasPivotFields = Not .HasPivotFields
End With
Range(“A1”).Select
End Sub
undErsTAnding piVoTcAcHEs
A PivotCache is an object that you do not see, as it is working behind the scenes when a new
PivotTable is created directly from the source data. The PivotCache is a container that holds a static
copy of the source data in memory.
PivotTables do not summarize data directly from the source data, but rather
from the PivotCache that memorized a snapshot of the data. That is why, in
the native Excel environment not enhanced with VBA, if you change a piece
of existing data in the source data range, the PivotTable report does not
reflect that change until you refresh the PivotTable.
Figure 15-19 shows the Refresh menu item when you right-click a cell that is
part of a PivotTable. The Refresh button actually refreshes the PivotCache.
The PivotCache, though not seen, maintains the source data beforehand in a
static go-to container. Keeping the data in PivotCache memory makes
pivoting and recalculations a snap, but the downside is extra workbook size and
less memory for other tasks.
figurE 15-19
When you create a PivotTable manually, Excel does not bother you with the PivotCache details. If
you were to create a PivotTable in VBA, you’d need to address the PivotCache issue in code. Suppose
you are creating a new PivotTable based on the original source data that has been shown in this
lesson. Your first step would be to program VBA to tell Excel four pieces of information:
1.
You want to add a PivotCache to the workbook.
2.
The location of the source data.
3.
Based on items 1 and 2, create the PivotTable.
4.
Specify where the PivotTable will be placed.
 
Search JabSto ::




Custom Search