237
Understanding Versions
New in Excel 2007
Although the basic concept of pivot tables is the same in Excel 2007 as it was in Excel 2003,
several new features are available in Excel 2007 pivot tables. The entire Design ribbon is
new, including the concepts of subtotals at the top, the report layout options, blank rows,
and the new PivotTable styles. Excel 2007 offers better filters than previous versions. It also
makes the expand and collapse functionality more apparent by adding buttons to the pivot
table grid. Every new feature adds one or more methods or properties to VBA.
If you are hoping to share your pivot table macro with people running prior versions of
Excel, you need to avoid these methods. Your best bet is to open an Excel 2003 workbook in
compatibility mode and record the macro while the workbook is in compatibility mode. If
you are using the macro only in Excel 2007 or later, you can use any of these new features.
Table 11.1 shows the methods that are new in Excel 2007. If you record a macro that uses
these methods, you cannot share the macro with someone using Excel 2003 or earlier.
Table 11.1 Methods New in Excel 2007
Method Description
ClearAllFilters Clears all filters in the pivot table.
ClearTable Removes all fields from the pivot table but keeps
the pivot table intact.
ConvertToFormulas Converts a pivot table to cube formulas. This
method is valid only for pivot tables based on
OLAP data sources.
DisplayAllMemberPropertiesInTooltip Equivalent to Options, Display, Show Properties
in ToolTips.
RowAxisLayout Changes the layout for all fields in the row area.
Valid values are
xlCompactRow, xlTabularRow,
or
xlOutlineRow.
SubtotalLocation Controls whether subtotals appear at the top or
bottom of each group. Valid arguments are
xlAtTop or xlAtBottom.
Table 11.2 lists the properties that are new in Excel 2007. If you record a macro that refers
to these properties, you cannot share the macro with someone using Excel 2003 or earlier.
Table 11.2 Properties New in Excel 2007
Property Description
ActiveFilters Indicates the active filters in the pivot table; this
is a read-only property.
11
continues
12_0789736012_CH11.qxd 12/11/06 6:26 PM Page 237
To Next Page
Loading...
Need help?
General
