title: Excel PivotField Orientation is wrong?
body: <p>I'm using Excel 2010 and writing a VBA function to automatically format various metrics in a pivot table as it is changed by the user (so that formatting is preserved when they add/remove fields).  I'm looping through the PivotFields collection to do this.  In order to change the format the PivotField needs to be visible in the pivot table, otherwise it throws an error.  So I figured I could just check the Orientation property of the PivotField and only handle those set to an Orientation of xlDataField.</p>
  
  <p>However, when I run the function, all data fields are showing an Orientation of xlHidden, even if they are visible in the PivotTable.</p>
  
  <p>I'm just doing a simple function like this</p>
  
  <pre><code>Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
  
      Dim pt As PivotField
  
      For Each pt In ActiveSheet.PivotTables("PivotTable1").PivotFields
  
          If VBA.InStr(1, pt.Caption, "COMP", vbTextCompare) And pt.Orientation = xlDataField Then
              pt.Function = xlAverage
              pt.NumberFormat = "0.00%"
          End If
  
      Next pt
  
  End Sub
  </code></pre>
  
  <p>I've even checking all PivotFields by using the following:</p>
  
  <pre><code>Debug.Print pt.Caption &amp; " " &amp; pt.Orientation
  </code></pre>
  
  <p>Anything not in the Page, Row or Column fields shows as Hidden.  Nothing appears as a Data field.  Am I missing something?  It doesn't matter what I have set as Data fields, nothing is tagged as xlDataField.  Should I try doing this another way?  I've tried using the PivotFields.Hidden property but I get errors trying to access it.</p>