Excel 2010 Pivot Table

I am working with an existing Pivot Table in Excel 2010.  Is there a way to identify if any of the fields are calculated fields.  And also view what is being calculated within that field?  Thanks for any info on this.
LVL 3
fjkaykr11Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongCommented:
you probably can access to DataFields object of PivotTable to get those calculated fields.

like:
Sub test()
    Dim pT As PivotTable
    Dim f As PivotField
    Set pT = ActiveSheet.PivotTables("PivotTable1")
    For Each f In pT.DataFields
        Debug.Print f.Name & ": isCalculatedField = " & isCalculatedField(f.Function)
    Next
End Sub

Function isCalculatedField(v As Long) As Boolean
    'Should always return True?
    Select Case v
    Case XlConsolidationFunction.xlAverage:
        isCalculatedField = True
    Case XlConsolidationFunction.xlCount:
        isCalculatedField = True
    Case XlConsolidationFunction.xlCountNums:
        isCalculatedField = True
    Case XlConsolidationFunction.xlMax:
        isCalculatedField = True
    Case XlConsolidationFunction.xlMin:
        isCalculatedField = True
    Case XlConsolidationFunction.xlProduct:
        isCalculatedField = True
    Case XlConsolidationFunction.xlStDev:
        isCalculatedField = True
    Case XlConsolidationFunction.xlStDevP:
        isCalculatedField = True
    Case XlConsolidationFunction.xlSum:
        isCalculatedField = True
    Case XlConsolidationFunction.xlUnknown:
        isCalculatedField = True
    Case XlConsolidationFunction.xlVar:
        isCalculatedField = True
    Case XlConsolidationFunction.xlVarP:
        isCalculatedField = True
    Case Else:
        isCalculatedField = False
    End Select
End Function

Open in new window

0
fjkaykr11Author Commented:
Thanks for the reply and info.   Can you give me an idea on the steps to do this (do I need to run the code above?
0
Ryan ChongCommented:
>>Can you give me an idea on the steps to do this (do I need to run the code above?
in what scenario you wish to run the code above? When the spreadsheet was opened? or a button's click etc?

in general, we can create a Module and put the scripts above accordingly. What we need to do is to call the Subroutine test() whenever you need.
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

fjkaykr11Author Commented:
thanks for the update.   I never worked with code like this before but certainly worth trying.
When I call the Subroutine test(), does it output info on the calculated fields in the Pivot Table?  Thanks again.
0
Ryan ChongCommented:
When I call the Subroutine test(), does it output info on the calculated fields in the Pivot Table?
Yes, in a debug windows as you can see from this statement:

>>Debug.Print f.Name & ": isCalculatedField = " & isCalculatedField(f.Function)

>>For Each f In pT.DataFields
This statement looping the DataFields objects in the PivotTable object.

Check this article out on how to add the Module (VBA codes) into your Excel:

Where do I paste the code that I find on the internet
http://www.rondebruin.nl/win/code.htm

Attached a sample for your ref.
28737203.xlsm
0
Saurabh Singh TeotiaCommented:
The quickest way to find this whether the field is calculated or not is to select the pivot table...it will show the design option in it..

Now in  Options go to-->Field Items & Sets-->In their click on List Formulas..

Now as soon you click on list formulas it will automatically create a sheet in this listing all the formulas of the calculated field which is their in your pivot table...

Enclosed is the screenshot of what i mean...

List formulas
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
fjkaykr11Author Commented:
Thanks for all the help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.