249
Creating a Report Showing Revenue by Product
When you have large numbers, displaying the thousands separator helps the person reading
the report. To set up this format in VBA code, use the following:
PT.PivotFields(“Sum of Revenue”).NumberFormat = “#,##0”
Some companies have customers who typically buy thousands or millions of dollars’ worth
of goods. You can display numbers in thousands by using a single comma after the number
format. Of course, you need to include a
K
abbreviation to indicate that the numbers are in
thousands:
PT.PivotFields(“Sum of Revenue”).NumberFormat = “#,##0,K”
Of course, local custom dictates the thousands abbreviation. If you are working for a rela-
tively young computer company where everyone uses
K
for the thousands separator, you’re
in luck because Microsoft makes it easy to use this abbreviation. However, if you work at a
100+ year-old soap company where you use
M
for thousands and
MM
for millions, you have a
few more hurdles to jump. You are required to prefix the
M
character with a backslash to
have it work:
PT.PivotFields(“Sum of Revenue”).NumberFormat = “#,##0,\M”
Alternatively, you can surround the
M
character with double quotation marks. To put double
quotation marks inside a quoted string in VBA, you must put two sequential quotation
marks. To set up a format in tenths of millions that uses the
#,##0.0,,”MM”
format, you
would use this line of code:
PT.PivotFields(“Sum of Revenue”).NumberFormat = “#,##0.0,, “”M”””
Here, the format is quotation mark, pound, comma, pound, pound, zero, period, zero,
comma, comma, quotation mark, quotation mark, M, quotation mark, quotation mark, quo-
tation mark. The three quotation marks at the end are correct. You use two quotation marks
to simulate typing one quotation mark in the custom number format box and a final quota-
tion mark to close the string in VBA.
Suppressing Subtotals for Multiple Row Fields
As soon as you have more than one row field, Excel automatically adds subtotals for all but
the innermost row field. However, you may want to suppress subtotals for any number of
reasons. Although accomplishing this task manually may be relatively simple, the VBA code
to suppress subtotals is surprisingly complex.
You must set the
Subtotals
property equal to an array of 12
False
values. Read the VBA
help for all the gory details, but it goes something like this: The first
False
turns off auto-
matic subtotals, the second
False
turns off the
Sum
subtotal, the third
False
turns off the
Count
subtotal, and so on. It is interesting that you have to turn off all 12 possible subtotals,
even though Excel displays only one subtotal. This line of code suppresses the
Product
subtotal:
PT.PivotFields(“Product”).Subtotals = Array(False, False, False, False, _
False, False, False, False, False, False, False, False)
11
12_0789736012_CH11.qxd 12/11/06 6:26 PM Page 249
Summary of Contents for VBA
Page 60: ......