Page 386 - Microsoft Office Excel 2003 Programming Inside Out

Part 5: Manipulating Excel Objects
Microsoft Office Excel 2003 Programming Inside Out

Sub PivotHourTo4()
On Error Goto NotEnough
With ActiveSheet.PivotTables("PivotTable8").PivotFields("Hour")
.Orientation = xlRowField
.Position = 4
End With
NotEnough: MsgBox ("There are fewer than three fields in the Row area.")
End Sub

Figure 16-9. Putting the Hour field in the fourth position in the Row area generates this result.

Chapter 16
Inside Out
An Interesting Occurrence

For this macro to work correctly, there must be three fields in the Row area. The order of the
fields matters in that it affects data presentation, but the order doesn’t matter when it
comes to the procedure functioning properly. The presence or absence of the On Error
statement, however, makes an interesting difference in how Excel handles the result of the
instruction to place the Hour field at the fourth position in the Row area. If you leave out the
On Error statement and there are fewer than three fields in the Row area, Excel won’t be
able to find the fourth position in the Row area, and you’ll get this error: “Run-time error
‘1004’: Unable to set the Position property of the PivotField class.” However, if you include
the On Error statement, which directs the program to jump to the line with the NotEnough:
label if an error occurs, Excel will interpret the instruction to move the Hour field to the
fourth position in the Row area as an instruction to move the Hour field to the last position
in the Row area. Maybe this is a reward for good programming behavior.

360