92.

Building PivotTables

In Excel, each object class has its own list of methods and properties that you can use to manipulate objects belonging to that class. Many objects belong to collections or link to other objects.

Learn About PivotTable Objects in Help

The Microsoft Excel Visual Basic Reference portion of online Help contains an overview graphic that shows how Excel objects relate to each other.

1. On the Microsoft Visual Basic toolbar, click the Visual Basic Editor button to open Visual Basic. In the Ask A Question box, type Excel Objects and press Enter. In the list of suggested topics, click Microsoft Excel Objects.

   

   This figure shows the first part of the Excel object model. Each blue box represents an object class. Each yellow box represents two object classes: the collection class and the object class. The objects on the right belong directly to the Application object. The left side of the tree shows the Workbooks (Workbook) pair of objects, along with all the objects that belong to a Workbook. One pair of objects that belongs to a Workbook is the PivotCaches (PivotCache) pair of objects. A PivotCache object is an invisible location for storing the values that go into a PivotTable report.

2. Click the PivotCaches (PivotCache) box.

   This takes you to the topic for the PivotCaches collection object. The topic shows the local context of the object, along with a brief description. At the top of the topic-and this is true of the topic for every object class-are links to pop-up windows containing the properties and methods for the object.

   

3. Click Methods, and click Add Method. Then expand the topic Add Method As It Applies To The PivotCaches Object. Each collection has a version of the Add method. The Add method for the PivotCaches collection requires two arguments that define the source for the data. The method then returns a reference to a PivotCache object.

   

   Back

   

4. Click the Back button twice to get back to the Microsoft Excel Objects topic.

   A PivotTable does not belong to a Workbook, so it doesn't appear in the first part of the Excel object model. A PivotTable belongs to a Worksheet. Worksheet objects take a whole page of their own.

5. Click the red triangle to the right of the Worksheets (Worksheet) box.

   The objects on the right belong directly to a Worksheet. The objects on the left belong to a Range object.

   

6. Scroll down to see the PivotTables (PivotTable) box and the objects that belong to it.

   The most important PivotTable objects are PivotField objects (which are in the box with PivotFields), and PivotItem objects. You can see that a PivotTable object also points to the PivotCache object that contains data for that report.

   

7. Close the Help window.

In this chapter, you will work with PivotCache, PivotTable, PivotField, and PivotItem objects.

Create a Basic PivotTable

The first step in creating a PivotTable is creating a cache. As you saw in the object model, a PivotCache object belongs to the workbook, not to a worksheet or PivotTable. You can have multiple reports linked to a single cache. You can put multiple reports on a single worksheet or in separate worksheets. Basing multiple reports on a single cache reduces the amount of memory Excel needs when working with large data sources.

1. In the Visual Basic Editor, click the Run Macro button, type MakePivot in the Macro Name box, and then click Create.

   A module opens with a new, empty macro.

2. Insert the following variable declaration statements at the beginning of the macro:

   Dim myCache as PivotCache Dim myTable As PivotTable Dim myField As PivotField Dim myItem As PivotItem

   Assigning objects to these variables enables Visual Basic to display Auto Lists that help you see methods and properties.

3. Press F8 twice to step to the End Sub statement. From the View menu, click the Immediate Window command. Move and size the Immediate window so that you can see the Excel window in the background.

4. In the Immediate window, type Workbooks.Open "Orders.dbf" and then press the Enter key to open the database workbook.

   In a few seconds, the database appears. (The formatting of the first column is not important.) The active sheet in the database workbook is a worksheet that contains a range named Database. The Worksheet object's PivotTableWizard can create a PivotTable from a range named Database.

   

   Tip 

   If the  Orders.dbf file doesn't open, click the Open toolbar button, change to the folder containing the practice files for this book, and then click Cancel before executing the statement.

5. In the Immediate window, type Set myCache = ActiveWorkbook.PivotCaches.Add(xlDatabase, "Database") and then press the Enter key.

   Nothing seems to happen, but you have just created a PivotCache object. In this statement, the ActiveWorkbook property returns a reference to a Workbook object. The PivotCaches property returns a reference to the (initially empty) PivotCaches collection of that workbook. The Add method adds a new PivotCache object to the collection, using xlDatabase as the source type (the word Database refers to the list in an Excel worksheet, not to an external database), and "Database" as the name of the range that contains the list. The Add method returns a reference to the newly created PivotCache object, which the Set command assigns to the myCache variable. A PivotCache object has a method that can create a PivotTable report.

6. In the MakePivot macro, click anywhere in the word PivotCache and press F1. In the Context Help dialog box, leave the PivotCache object selected (the other option selects the PivotCache property topic), and click Help.

   

7. Click the Methods keyword, and click CreatePivotTable Method.

   This method has one required argument-a Range object that can serve as the target location for the report. The method then returns a reference to a PivotTable object, which you can assign to a variable for later use.

   

8. Close Help. In the Immediate window, type Worksheets.Add and press Enter.

   This creates a new worksheet for the report.

9. Type Set myTable = myCache.CreatePivotTable(Cells(1)) and press Enter.

   This creates the simplest possible shell of a PivotTable report. (If the PivotTable Field List window appears, close it. You will be using Visual Basic to create and modify the PivotTable report.)

   

You've now used Microsoft Visual Basic for Applications commands to create a PivotCache and a PivotTable based on that cache.

Manipulate Pivot Fields

You did create a PivotTable, but it's not particularly informative. You still need to use the PivotTable to display summarized values from the source list. The source for a PivotTable report usually consists of a list with a lot of rows and a few columns. Each column is called a field, and the label at the top of the column is the name of the field. The list in  Orders.dbf has seven fields. Date, State, Channel, Price, and Category are fields that contain words. Units and Net are fields that contain numbers. A PivotTable typically summarizes the number fields, sorting and grouping them by the fields that contain words.

1. In the Immediate window, type Set myField = myTable.PivotFields("Units") and then press the Enter key.

   This action assigns the Units pivot field to a variable but does not put it onto the report.

2. Type myField.Orientation = xlDataField and then press the Enter key.

   The label 'Sum of UNITS' and a number appear in the body of the PivotTable. Because the Units field contains values, the PivotTable adds all the numbers in the Units column.

   

   The PivotFields collection contains one item for each of the seven fields in the database. You refer to a single item from the collection in the standard way-by name or by number. In this case, it's easier to remember the name of the Units field than it is to recall where it happens to fall in the database. Assigning xlDataField to the Orientation property summarizes the data in that field.

3. Now format the Units total. Type myField.NumberFormat = "#,##0" and then press the Enter key.

   The number in the PivotTable looks much better with a comma. NumberFormat is a property of a PivotField object. It works in the same way as the NumberFormat property of a Range object.

   

4. Type Set myField = myTable.PivotFields("State") and then press the Enter key.

   This action assigns the State pivot field to the variable.

5. Type myField.Orientation = xlRowField and then press the Enter key.

   Row headings appear, adding appropriate subtotals to the grand total that was already there. The gray box containing the word State is called a field button. It serves as a visible heading for the pivot field.

   Tip 

   The CurrentPage property works only with page fields.

   

6. By now, you can probably guess how to turn the State field items into column headings. Type myField.Orientation = xlColumnField and then press the Enter key.

   The state codes move from the side to the top of Sheet1. The field button moves above the state codes.

   

7. Row and column fields group the data in the PivotTable. In the same way that you change pages in a magazine to select which part you want to see, you can filter the data in a PivotTable by using a page field. Type myField.Orientation = xlPageField and then press the Enter key. The State field button moves up to the upper left corner of the worksheet.

8. To filter the data (by state, for example), assign a state code to the CurrentPage property of the State page field. Type myField.CurrentPage = "WA" and press the Enter key. Then type myField.CurrentPage = "CA" and press the Enter key. The numbers change as you filter by different states.

   

9. To remove a field from one of the visible areas of the PivotTable, assign it the hidden orientation. Type myField.Orientation = xlHidden and then press the Enter key.

   The State field button disappears. The PivotTable still contains a pivot field named State, but the field button is no longer visible. The Orientation property of PivotFields is what makes the PivotTable 'pivot.'

   

Make Multiple Changes to a PivotTable

The PivotTable object also has a shortcut method that can assign several fields to the different PivotTable areas all at once. You can find the method by looking at the PivotTable object in Help.

1. In the MakePivot macro, click in the word PivotTable and press F1. Leave the PivotTable object selected, and click Show. In the Methods list, click AddFields Method.

   The topic shows that you can use this method to add row, column, and page fields all at once.

   

2. Close the Help window.

3. In the Immediate window, type myTable.AddFields "Category", "State", "Channel" and then press the Enter key.

   The Category field becomes the row field, the State field becomes the column field, and the Channel field becomes the page field. The arguments of the AddFields method always appear in Row, Column, Page order.

   

   The Help topic indicates that each argument can consist of either a field name or an array of field names. You use an array when you want to add more than one field to an orientation.

4. To add more than one field to an orientation, you need to use multiple field names as a single argument. The Array function allows you to treat multiple field names as a single argument. Type myTable.AddFields Array("State","Channel"), "Price", "Date" and then press the Enter key.

   Both State and Channel become row fields, Price becomes the column field, and Date becomes the page field.

   

5. When you have more than one field in a given area, you can swap the order of the fields using the Position property. The State field is still assigned to the myField variable. Type myField.Position = 2 and then press the Enter key to swap the order of the State and Channel fields.

   

In summary, use the AddFields method of the PivotTable object to make major changes to a PivotTable; use the Orientation, Position, and CurrentPage properties of the PivotField objects to fine-tune the table.

Important 

In addition to the PivotFields collection, the PivotTable object has subcollections that contain only PivotFields of a particular orientation. For example, the RowFields collection contains only the fields whose Orientation property is set to xlRowField. The subcollections are RowFields, ColumnFields, PageFields, DataFields, and HiddenFields. These collections don't have corresponding object classes. A member of the RowFields collection is still a PivotField object, not a RowField object. You never need to use any of these subcollections, but you might find them convenient. For example, if you know that there's only one row field, you can refer to it as RowFields(1), without worrying about its name or what its number is in the entire PivotFields collection.