![background image](http://html1.mh-extra.com/html/amazon/vba/vba_manual_2919200001.webp)
I N T H I S C H A P T E R
Using VBA to Create Pivot
Tables
11
Introducing VBA
Version 5 of Excel introduced a powerful new
macro language called Visual Basic for Applications
(VBA). Every copy of Excel shipped since 1993 has
had a copy of the powerful VBA language hiding
behind the worksheets. VBA allows you to perform
steps that you normally perform in Excel, but to
perform them very very quickly and flawlessly. I’ve
seen a VBA program take a process that would take
days each month and turn it into a single button
click and a minute of processing time.
Don’t be intimidated by VBA. The VBA macro
recorder tool will get you 90% of the way to a use-
ful macro and I will get you the rest of the way
there using examples in this chapter.
Every example in this chapter is available for down-
load from http://www.mrexcel.com/
pivot2007data.html/.
Enabling VBA in Your Copy of Excel
By default, VBA is disabled in Office 2007. Before
you can start using VBA, you need to enable
macros in the Trust Center. From the Office icon
menu, choose Excel Options, Trust Center, Trust
Center Settings, Macro Settings.
Choose one of the options below.
Disable all macros with notification—this set-
ting is equivalent to medium macro security in
Excel 2003. When you open a workbook that
contains macros, a message will appear alerting
that there are macros in the workbook. If you
expect macros to be in the workbook, you sim-
ply click Options, Enable to allow the macros
to run. This is the safest setting, as it forces
you to explicitly enable macros in each work-
book.
Introducing VBA . . . . . . . . . . . . . . . . . . . . . . . .231
Learning Tricks of the Trade . . . . . . . . . . . . . .234
Understanding Versions . . . . . . . . . . . . . . . . .236
Building a Pivot Table in Excel VBA . . . . . . . .239
Creating a Report Showing Revenue
by Product . . . . . . . . . . . . . . . . . . . . . . . . . . . .246
Handling Additional Annoyances When
Creating Your Final Report . . . . . . . . . . . . . . .250
Addressing Issues with Two or More
Data Fields . . . . . . . . . . . . . . . . . . . . . . . . . . . .257
Summarizing Date Fields with Grouping . . .263
Using Advanced Pivot Table Techniques . . . .267
Controlling the Sort Order Manually . . . . . . .276
Using Sum, Average, Count, Min, Max,
and More . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .276
Creating Report Percentages . . . . . . . . . . . . .277
Using New Pivot Table Features in
Excel 2007 . . . . . . . . . . . . . . . . . . . . . . . . . . . . .279
Next Steps . . . . . . . . . . . . . . . . . . . . . . . . . . . . .289
12_0789736012_CH11.qxd 12/11/06 6:26 PM Page 231
Summary of Contents for VBA
Page 60: ......