118.

Refining PivotTables

Once you've created a PivotTable with rows and columns in the proper orientation, you can make many refinements to the table. You can manipulate specific details within a field, change the appearance of the data, or even modify the worksheet ranges that contain portions of the PivotTable.

Manipulate Pivot Items

The unique values that appear in a PivotField are called items. You can manipulate individual items within a PivotField.

1. Type Set myItem = myField.PivotItems("WA") and then press the Enter key to assign the pivot item for Washington State to a variable.

2. Now that myItem refers to an individual pivot item, you can manipulate that item using its properties. Type myItem.Position = 1 and then press the Enter key. A pivot item has a Position property, just as a PivotField does.

   

3. `Type myItem.Name = "Washington" and then press the Enter key.

   

   The name of the pivot item displays in the PivotTable. If you don't like the way the database designer abbreviated state names, you can fix the problem in the PivotTable. Of course, sometimes it's better to leave conventions alone. Fortunately, the PivotItem object remembers for you what its original name was.

4. Type myItem.Name = myItem.SourceName and then press the Enter key.

   The name changes back to the original. For obvious reasons, the SourceName property is read-only.

   

5. Perhaps changing the spelling of the state name isn't enough. Perhaps you don't like Washington state (in which case, I really do feel sorry for you) and want to eliminate it entirely. Type myItem.Visible = False and then press the Enter key.

   

6. Perhaps, however, you suddenly realize how foolish you are not to like Washington. Fortunately, you can put it back the same way you got rid of it. Type myItem.Visible = True and then press the Enter key.

7. Another useful thing you can do with a pivot item is to hide or show the detail to the right of a field. Try hiding the detail for the Retail channel. Type Set myItem = myTable.PivotFields("Channel").PivotItems("Retail") and then press the Enter key.

   This action assigns the pivot item to the myItem variable.

8. Type myItem.ShowDetail = False and then press the Enter key.

   All the states for the Retail channel collapse into a single row.

   

Manipulating pivot items isn't generally as dramatic as manipulating PivotFields, but you can use the Position, Name, SourceName, Visible, and ShowDetail properties to refine the effect of the PivotTable.

Manipulate Data Fields

Data fields do the real dirty work of the PivotTable. This is where the numbers get worked over. Data fields are like other PivotFields in many ways, but they do have a few unique twists. You can see how data fields are different from other fields when you add a second data field.

1. Type Set myField = myTable.PivotFields("Net") and then press the Enter key to assign the Net field to the myField variable.

2. Type myField.Orientation = xlDataField and then press the Enter key to add a second data field.

   

   As soon as you have two data fields in the PivotTable, you get a new field button, labeled Data. The Data field is not a field from the database. It's a temporary field that allows you to manipulate multiple data fields. The Data field begins as a row field, but you can change it into a column field.

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

   This action assigns the temporary Data field to a variable. The statement works only if you have more than one data field.

4. Type myField.Orientation = xlColumnField and then press the Enter key.

   

   When you made the State field into a row field, a button labeled State appeared on the PivotTable. The same was true for the other row, column, and page fields. But when you made Units and Net into data fields, you didn't see buttons labeled Units and Net. Rather, you saw the labels Sum of UNITS and Sum of NET. These summary fields are new, derived fields that have been added to the PivotTable. To refer to one of these fields, you must use the new name.

5. Type Set myField = myTable.PivotFields("Sum of NET") and then press the Enter key.

6. Type myField.Orientation = xlHidden and then press the Enter key.

   The Sum of NET column disappears-along with the Data button-because there's now only one data field. To create a data field, you change the orientation of the database field. To remove a data field, you change the orientation of the derived field.

   

7. Type Set myField = myTable.PivotFields("Sum of UNITS") and then press the Enter key to assign the remaining data field to a variable.

8. The default calculation for a number field is to sum the values. The Function property of a data field allows you to change the way the PivotTable aggregates the data. Type myField.Function = xlAverage and then press the Enter key.

   The values change to averages, and the label changes to Average of UNITS.

   

9. If you don't want the label switching around on you, you can use the Name property to control it yourself. Type myField.Name = "Avg Units" and then press the Enter key. The label changes to Avg Units.

   

   Tip 

   Once you replace the default name for the derived data field, Excel won't automatically change the name, even if you change the Function property. To have Excel automatically adjust the name, change the Name property to what the automatic name would be for the current function. For example, if the data field currently displays averages for the Units field, change the name to Average of Units.

   When you assign xlDataField to a field's Orientation property, you don't actually change the Orientation property for that field; rather, you create a new, derived field that has xlDataField as its Orientation property. These derived fields allow you to create multiple data fields from a single source field. Then you can set one derived data field to show sums, another derived field to show averages, and so forth.

The umbrella Data field, which exists only when the PivotTable has more than one data field, acts like an ordinary PivotField except that it can be assigned only to the row or column orientation.

Find PivotTable Ranges

A PivotTable resides on a worksheet. It doesn't use ordinary worksheet formulas to perform its calculations, but it does take up worksheet cells. If you want to apply a special format to a specific part of a PivotTable, or if you want to add formulas to cells outside the PivotTable that align with cells in the PivotTable, you need to know which cells contain which parts of the PivotTable. Fortunately, all the objects relating to PivotTables have properties to help you find the cells that contain the various parts of the PivotTable.

1. In the Immediate window, type myTable.DataBodyRange.Select and then press the Enter key.

   Excel selects the range containing the body of the data-that is, the DataBodyRange. When you type the period after the word myTable, Visual Basic displays the list of methods and properties. Several of the properties have names with the suffix Range-for example, ColumnRange, DataBodyRange, DataLabelRange, and PageRange. All these properties that end in Range return a range object of some kind.

   

2. You can also go the other way; you can find a PivotTable element that resides in a particular cell in Excel. Type Range("D4").Select and then press the Enter key to select cell D4.

   

3. Type Set myItem = ActiveCell.PivotItem and press the Enter key.

   The Low item from the Price field is assigned to the variable. Several of the properties in the Auto List begin with Pivot . Each of these properties returns the appropriate object from the PivotTable that happens to fall on the range.

4. Type myItem.DataRange.Select and then press the Enter key to select the data cells 'owned' by the Low Price item.

5. Type ActiveWorkbook.Close False and press Enter to close the  Orders.dbf file.

   

When you see a property for a PivotTable object with the suffix Range, you know that it returns a Range object of some kind. When you see a property for a Range object with the prefix Pivot , you know that it returns an object that's in that cell.

Save Your Work

You've done a lot of exploring in the Immediate window. When you quit Excel, everything you've done will evaporate. You can save your explorations from the Immediate window by copying them into the MakePivot macro.

1. Press F8 to finish the macro.

2. Press Ctrl+A to select the entire contents of the Immediate window.

3. Press Ctrl+C to copy the contents of the Immediate window.

4. Click in the MakePivot macro, at the beginning of the End Sub statement. Press Ctrl+V to paste the contents of the Immediate window.

   The new lines are not indented the way proper statements in a macro should be.

   

5. Click in the middle of the first line that needs indenting, scroll to the bottom, and press and hold the Shift key as you click in the middle of the last line that needs indenting. Then press the Tab key to indent all the lines at once.

   Save button

   

6. Save the Chapter06 workbook by clicking the Save button in the Visual Basic Editor. Close the  Orders.dbf workbook without saving changes.

7. With the insertion point anywhere in the MakePivot macro, press F8 repeatedly to repeat (and review!) everything you did in this chapter.

   Tip 

   In this exploration, you assigned several different objects in turn to each of the object variables. You might find your macros easier to read if you create a unique variable with a descriptive name each time you need to assign an object to a variable.

Here, for your reference, is the entire macro that you created in this chapter:

Sub MakePivot()     Dim myCache As PivotCache     Dim myTable As PivotTable     Dim myField As PivotField     Dim myItem As PivotItem     Workbooks.Open "Orders.dbf"     Set myCache = ActiveWorkbook.PivotCaches.Add( _         xlDatabase, "Database")     Worksheets.Add     Set myTable = myCache.CreatePivotTable(Cells(1))     Set myField = myTable.PivotFields("Units")     myField.Orientation = xlDataField     myField.NumberFormat = "#,##0"     Set myField = myTable.PivotFields("State")     myField.Orientation = xlRowField     myField.Orientation = xlColumnField     myField.Orientation = xlPageField     myField.CurrentPage = "WA"     myField.CurrentPage = "CA"     myField.Orientation = xlHidden     myTable.AddFields "Category", "State", "Channel"     myTable.AddFields Array("State","Channel"), "Price", "Date"     myField.Position = 2         Set myItem = myField.PivotItems("WA")     myItem.Position = 1     myItem.Name = "Washington"     myItem.Name = myItem.SourceName     myItem.Visible = False     myItem.Visible = True     Set myItem = _         myTable.PivotFields("Channel").PivotItems("Retail")     myItem.ShowDetail = False     Set myField = myTable.PivotFields("Net")     myField.Orientation = xlDataField     Set myField = myTable.PivotFields("Data")     myField.Orientation = xlColumnField     Set myField = myTable.PivotFields("Sum of NET")     myField.Orientation = xlHidden     Set myField = myTable.PivotFields("Sum of UNITS")     myField.Function = xlAverage     myField.Name = "Avg Units"          myTable.DataBodyRange.Select     Range("D4").Select     Set myItem = ActiveCell.PivotItem     myItem.DataRange.Select     ActiveWorkbook.Close False End Sub

This macro might not do much useful work, but now you understand how PivotTables work and how you can manipulate them using Visual Basic.