Microsoft Office Tutorials and References
In Depth Information
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:
.HasPivotFields = Not .HasPivotFields
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.
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:
You want to add a PivotCache to the workbook.
The location of the source data.
Based on items 1 and 2, create the PivotTable.
Specify where the PivotTable will be placed.