Format calculated item in vba sub

[yodayoda]

I have an excel vba script that builds a pivot table. In it I create three calculated items. The first is defined as "a-b", the second is "b-c". They get formatted as dollars according to the following: PT.PivotFields("Sum of Amount").NumberFormat = "$#,##0_);[Red]($#,##0)". I have now added a third one that is "a/b" but can't figure out how to format it as PT.PivotFields("Sum of Amount").NumberFormat = "%;[Red](%)" while still keeping the first two as dollars. Boiled down, I'm trying to get the first two calculated items looking like dollars and the third looking like percentage. Thanks for any guidance in this.

 

[xenou]

Hi:
I am not an expert in Pivot Table scripts but I suspect you could record a macro where you change the field formatting and look at that macro to see what the relevant VBA properties and methods are - then edit as needed.

Regards.

 

[yodayoda]

I tried that and it just tells me what the format should look like. Here's the section of code where I'm hung up.

PT.PivotFields("Version").CalculatedItems.Add "InvStrat-BudBuild", "= InvStrat- BudBuild"

PT.PivotFields("Version").CalculatedItems.Add "OrigPlan-BudBuild", "= OrigPlan-BudBuild"

PT.PivotFields("Version").CalculatedItems.Add "Bud/LRP", "= BudBuild/InvStrat"

PT.PivotFields("Sum of Amount").NumberFormat = "$#,##0_);[Red]($#,##0)"

All three calculated items are manipulating the data field "Sum of Amount". So the two instances get formatted as dollars using the last statement above. Since the third item is also "Sum of Amount" data, if I rewrite the last statement to be PT.PivotFields("Sum of Amount").NumberFormat = "0%;[Red](0%)", then the first two items also become percentages. Thus my dilemma - how to get the first two as $$ and the third one as %. Thanks.

 

[xenou]

Ahh...good old calculated Items.

I am not sure if this is possible if these items are all in the same column (sum of amount). You might need to bit the bullet and format as "#,###.00" - so percents are decimals less than one.

You can try getting this into a table format rather than a report format...that could give you more options for formatting columns of data. This may not be possible in your situation.

Another solution is to custom format the cells, not the pivot fields -- see below.

It might also be possible to have two sum of amount columns to work on...again, maybe this is not feasible in your situation.

Sometimes Pivot Table are good for the crunching and the final report format for presentation is outside the Pivot Table, or on another sheet.

Basically, as far as pivot tables go, if you can do it in Excel you can do it in Excel VBA. So if you can get this to work in a native interface, you could macro the same steps. There won't be any special properties or methods you can use in VBA to do what you can't do in native Excel.

Sample sub:
Format cells (using regular cell properties, not pivot table properties. I've been lazy with the formats as anything not a number or a percent is hidden...you might need to clean this up. Who knows what it would do to text. And, of course, anything that looks like a percent will be formatted as one. Also, sometimes to select pivot table cells you need to "navigate into" the table with arrow keys and the shift key.

Sub myFormats()
Dim c As Range
For Each c In Selection
    If c.Value < 1 And c.Value > 0 Then
        c.NumberFormat = "0.00%;;"
    Else: c.NumberFormat = "$#,##0_);[Red]($#,##0);"
    End If
Next c
End Sub