Run the subroutine from the Macros dialog box to apply PivotStyleMedium23
formatting, as shown in Figure 6-26.
Figure 6-26. PivotStyleMedium23 formatting applied
Summary
PivotTables in Excel 2007 provide users with a very easy-to-use interface with which they
can analyze and summarize large amounts of data. In this chapter, we took a look at code
generated by Excel??™s Macro Recorder to get a feel for the PivotTable and PivotCache objects??™
properties and methods. We then saw how we could modify that code to make it more flexible
and dynamic.
Excel 2007 PivotTable reports are not linked to their data, but use the PivotCache object
to store a pointer to the data. When the data on a worksheet changes, the PivotTable does not
automatically update with those changes, especially if new data is appended. We created a
method to let the user refresh the PivotTable if the worksheet data on which it was based was
appended to.
Finally, we looked at some of the formatting options available to us using VBA code.We
were able to fix some of Excel 2007??™s default formatting behaviors, such as its use of the general
number format, generic summary field names, and its handling of blank rows. We also
saw how applying styles can dress up a PivotTable report.
CHAPTER 6 n PIVOTTABLES 247
Debugging and Error Handling
Debugging technique is an often overlooked part of a developer??™s set of skills. In this chapter,
we are going to explore some debugging techniques that will save you time when troubleshooting
your code, and make you a more efficient coder.
Pages:
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238