Pivot Field Function Code

[Todd Huttenstine]

hey guys

Below is a the code I use to calculate (summarize) a
particular pivot field in my pivot table:

With ActiveSheet.PivotTables("PivotTable1").PivotFields
("% Overdue")
.Orientation = xlDataField
.Caption = "Percent Overdue"
.Position = 1
.Function = xlAverage
End With

As you can see the .Function tells the field how to
calculate.

Is it possible to put a custom formula in there such as:
..Function = (Total28- 'On Time28')/ Total28


When I do this VBA highlights it in red. Is there a way
to do this?



Thank you
Todd Huttenstine

 

[Frank Kabel]

Hi Todd
AFAIK this is not possible (you can't use custom function if you use
the wizard)

 

[Todd Huttenstine]

What happens if I dont use the wizard?

Can I specifiy in the code I have the special formula I
want to use?

I need it to use the formula:
(Total28- 'On Time28')/ Total28

 

[Frank Kabel]

Hi
if nearly all cases VBA does NOT provide more features than the GUI. So
in this case as you can't do this manually also VBA does not support
this feature (using custom formulas in the pivot table). One workaround
could be to use a helper column in your source data in include this
helper column in your pivot table as data item (depend on the type of
your formula)