Excel vba：在数据透视表中隐藏计算字段时出错

Excel vba: error hiding calculated field in Pivot table

提问by Patrick Honorez

I have written several Subs to show/hide fields in a PivotTable. Now I am trying to do the same with a calculated field, but I get an error when hiding it. I took my code from the recorder and the recorder's code also halts on the last line. I googled the error message, without serious result.

我写了几个 Subs 来显示/隐藏数据透视表中的字段。现在我正在尝试对计算字段执行相同的操作，但是在隐藏它时出现错误。我从录音机中取出了我的代码，录音机的代码也停在了最后一行。我用谷歌搜索了错误信息，没有严重的结果。

Sub PrRemove()
    'remove PR
    Dim pt As PivotTable
    Set pt = ActiveSheet.PivotTables("MyPivot")
    pt.PivotFields("MyField").Orientation = xlHidden   '<- here is the error
End Sub

The same code works fine if MyField is a normal field (not a calculated one).
I am using Excel 2007 with SP2.
Any clue ?

如果 MyField 是普通字段（不是计算字段），则相同的代码可以正常工作。
我正在使用带有 SP2 的 Excel 2007。
任何线索？

EDIT on 17 June 2010: I also tried using pt.DataFields instead of pt.PivotFields, with exactly the same behaviour. The error message says "Unable to set the orientation of the PivotField class".

2010 年 6 月 17 日编辑：我也尝试使用 pt.DataFields 而不是 pt.PivotFields，行为完全相同。错误消息显示“无法设置 PivotField 类的方向”。

采纳答案by AMissico

Well, I will give you the confirmation you need. It seems using the Orientationproperty on a "Calulated Field" just does not work, and I would have to agree this is a bug and not a common "usage" error. I was able to duplicate "hiding/showing" the field without having to remove ("Delete") the calculated field. This allows the user to physically drag the calculated field from the field list after you have progammatically "hidden" the field. This is not a bad solution because it duplicates the user-interface. (Using Excel 2003.)

好吧，我会给你你需要的确认。似乎Orientation在“计算字段”上使用该属性不起作用，我不得不同意这是一个错误，而不是常见的“使用”错误。我能够复制“隐藏/显示”字段而无需删除（“删除”）计算字段。这允许用户在您以编程方式“隐藏”字段后从字段列表中物理拖动计算字段。这不是一个糟糕的解决方案，因为它复制了用户界面。（使用 Excel 2003。）

'2009.09.25 AMJ
'work around for
'   1004, Unable to set the Orientation property of the PivotField class
'when setting orientation property to hidden of calculated field, as in
'   ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of Field1").Orientation = xlHidden

Public Sub Hide()
'hide the data without removing the calculated field
'   this allows the user to physically drag the
'       calculated field from the field list once we
'       have "hidden" it programmatically.
'   if we use the "delete" method, the field is removed
'       from the pivot table and the field list

    Dim oWS As Worksheet
    Dim oPT As PivotTable
    Dim oPF As PivotField
    Dim oPI As PivotItem

    Set oWS = ActiveSheet
    Set oPT = oWS.PivotTables(1)

    For Each oPF In oPT.DataFields
        If oPF.SourceName = "Field1" Then
            'Stop
            Exit For
        End If
    Next

    Set oPI = oPF.DataRange.Cells(1, 1).PivotItem
    'oPI.DataRange.Select
    oPI.Visible = False

End Sub

Public Sub Show()
'show just reads the pivot field to the data fields

    Dim oWS As Worksheet
    Dim oPT As PivotTable
    Dim oPF As PivotField

    Set oWS = ActiveSheet
    Set oPT = oWS.PivotTables(1)

    For Each oPF In oPT.PivotFields
        If oPF.SourceName = "Field1" Then
            'Stop
            Exit For
        End If
    Next

    oPT.AddDataField oPF

End Sub

---

---

[original answer] Most likely you cannot hide this item because it is the last visible item. Instead, try removing it.

[原始答案] 您很可能无法隐藏此项目，因为它是最后一个可见项目。相反，请尝试将其删除。

回答by adamr

after much hair pulling i have found a workaround. if you add more than one pivot field (calculated or otherwise) excel creates a grouped field called Values. you can set the orientation property of PivotField("Values") to xlHidden and it bullets both fields. So if you want to remove a calculated field, just add a non-calculated field, set PivotField("Values").orientation to xlHidden and you're done.

在拉了很多头发之后，我找到了一个解决方法。如果您添加多个数据透视字段（计算或其他方式），excel 会创建一个名为“值”的分组字段。您可以将 PivotField("Values") 的方向属性设置为 xlHidden 并且它会同时显示两个字段。因此，如果您想删除计算字段，只需添加一个非计算字段，将 PivotField("Values").orientation 设置为 xlHidden 即可。

nobody said it would be pretty...

没有人说它会很漂亮...

回答by user4709164

With ActiveSheet.PivotTables("PivottableName").PivotFields("Values")
    .PivotItems("CalcFieldName").Visible = False
End With

回答by Laurent Bosc

I wanted to easily remove data fields (calculated fields or not), like it would be done manually.

我想轻松删除数据字段（计算字段与否），就像手动完成一样。

And I finally found this solution (Excel 2010) :

我终于找到了这个解决方案（Excel 2010）：

Set pt = ActiveSheet.PivotTables("mypivottable")
For Each pi In pt.DataPivotField.PivotItems
    pi.Visible = False
Next

回答by Colin

Here is a little workaround I discovered today, again not very elegant but at least it doesn't need much code, it will hide ALL the fields and you will need to reshow the ones you want after:

这是我今天发现的一个小解决方法，同样不是很优雅，但至少它不需要太多代码，它将隐藏所有字段，您将需要重新显示您想要的字段：

objTable.DataPivotField.Orientation = xlHidden

You may run into an error if excel for some reason thinks the datapivotfield is empty, but to fix this just add in another field as a datafield right before the above statement. Also make sure its the letter l not the number 1 in xlHidden vba's default font has them looking very very similar.

如果 excel 出于某种原因认为 datapivotfield 为空，您可能会遇到错误，但要解决此问题，只需在上述语句之前添加另一个字段作为数据字段即可。还要确保它的字母 l 不是 xlHidden vba 的默认字体中的数字 1，它们看起来非常相似。

Happy Coding

快乐编码

回答by MarcinL

It seems that to hide a calculated field you need to first hide a pivot field called "Values".

似乎要隐藏计算字段，您需要先隐藏一个名为“值”的数据透视字段。

PivotTable(1).PivotFields("Values").Orientation = xlHidden
For Each PF In PT.DataFields
    PF.Orientation = xlHidden
Next PF

I'm assuming that field only seem to exist if you've got two or more fields in your xlDataField position.

我假设该字段仅在您的 xlDataField 位置中有两个或更多字段时才存在。

Thanks Alinboss for pointing me in the right direction. I was sure I tried your method before and failed - turns out the order is important!

感谢 Alinboss 为我指明了正确的方向。我确定我之前尝试过你的方法但失败了 - 结果顺序很重要！

P.s. Your code still does not work with only one calculated data field

Ps 您的代码仍然无法仅使用一个计算数据字段

回答by DaveParillo

Have you changed the name of the calculated field? Was it originally 'Sum of MyField'? Try looking at the SourceName property and if it's different using that.

您是否更改了计算字段的名称？它最初是“Sum of MyField”吗？尝试查看 SourceName 属性，如果使用它不同。

Have you tried pt.CalculatedFields("MyField").Orientation = xlHidden?

你试过pt.CalculatedFields("MyField").Orientation = xlHidden吗？

回答by DaveParillo

I don't think this is an excel bug, I think it's a 'feature'. ;-)

我不认为这是一个 excel 错误，我认为这是一个“功能”。;-)

Re: @AMissico, there is no problem in excel hiding all of the fields in a pivot table, but he may be talking about items - you can't hide the last item in a pivot field.

回复：@AMissico，excel 中隐藏数据透视表中的所有字段没有问题，但他可能在谈论项目 - 您不能隐藏数据透视字段中的最后一个项目。

This is the code I routinely use to do what you are trying to do. These macros were developed on Excel 2002 & 2003. I don't hide CalculatedFields, I delete them.

这是我经常用来做你想做的事情的代码。这些宏是在 Excel 2002 和 2003 上开发的。我不会隐藏计算字段，而是删除它们。

' Hide all fields.
' @param ThePivotTable to operate upon.
Sub HidePivotFields(ByVal ThePivotTable As PivotTable)
    Dim pField As PivotField
    For Each pField In ThePivotTable.CalculatedFields
        pField.Delete
    Next pField
    For Each pField In ThePivotTable.PivotFields
        pField.Orientation = xlHidden
    Next pField

    Set pField = Nothing
End Sub

' Removes FieldName data from ThePivotTable
Sub HideField(ByVal ThePivotTable As PivotTable, _
              ByVal FieldName As String)
    If FieldExists(ThePivotTable, FieldName) = True And _
       CalculatedFieldExists(ThePivotTable, FieldName) = False Then
        ThePivotTable.PivotFields(FieldName).Orientation = xlHidden
    End If
End Sub

' Returns True if FieldName exists in ThePivotTable
'
' @param ThePivotTable to operate upon.
' @param FieldName the name of the specific pivot field.
Function FieldExists(ByVal ThePivotTable As PivotTable, _
                     ByVal FieldName As String) As Boolean
    Dim pField As PivotField

    For Each pField In ThePivotTable.PivotFields
        If pField.SourceName = FieldName Then
            FieldExists = True
            Exit For
        End If
    Next pField

    Set pField = Nothing
End Function

' Checks if the field FieldName is currently a member of the
' CalculatedFields Collection in ThePivotTable.
' @return True if a CalculatedField has a SourceName matching the FieldName
' @return False otherwise
Function CalculatedFieldExists(ByVal ThePivotTable As PivotTable, _
                               ByVal FieldName As String) As Boolean
    Dim pField As PivotField

    CalculatedFieldExists = False

    For Each pField In ThePivotTable.CalculatedFields
        If pField.SourceName = FieldName Then
            CalculatedFieldExists = True
        End If
    Next pField
    Set pField = Nothing
End Function

' Returns a Pivot Field reference by searching through the source names.
'
' This function is a guard against the user having changed a field name on me.
' @param ThePivotTable to operate upon.
' @param FieldName the name of the specific pivot field.
Function GetField(ByVal ThePivotTable As PivotTable, _
                  ByVal FieldName As String) As PivotField
    Dim pField As PivotField

    For Each pField In ThePivotTable.PivotFields
        If pField.Name <> "Data" Then
            If pField.SourceName = FieldName Then
                Set GetField = pField
                Exit For
            End If
        End If
    Next pField

    Set pField = Nothing
End Function

' Counts the number of currently visible pivot items in a field.
' @param ThePivotItems the collection of pivot itemns in a field.
' @return the count of the visible items.
Function PivotItemCount(ByVal ThePivotItems As PivotItems) As Long
    Dim pItem As PivotItem
    Dim c As Long

    For Each pItem In ThePivotItems
        If pItem.Visible = True Then c = c + 1
    Next pItem
    PivotItemCount = c
    Set pItem = Nothing
End Function

' Hides a single pivot item in a pivot field, unless it's the last one.
' @param FieldName pivot field containing the pivot item.
' @param ItemName pivot item to hide.
Sub HidePivotItem(ByVal ThePivotTable As PivotTable, _
                  ByVal FieldName As String, _
                  ByVal ItemName As String)
    Dim pField As PivotField

    Set pField = GetField(ThePivotTable, FieldName)
    If Not pField Is Nothing Then
        If PivotItemCount(pField.PivotItems) > 1 Then
            pField.PivotItems(ItemName).Visible = False
        End If
    End If

    Set pField = Nothing
End Sub

回答by DaveParillo

I am having the exact same problem as you. It looks like I'm going to have to delete the calculated field and readd it rather than hiding/showing it.

我和你有完全一样的问题。看起来我将不得不删除计算字段并读取它而不是隐藏/显示它。

回答by Jeremy B

I accidentally discovered a workaround to this the first time I attempted to hide a calculated field, so thought I would share it here:

我第一次尝试隐藏计算字段时偶然发现了一个解决方法，所以我想在这里分享它：

Instead of modifying the orientation property, you can instead instruct the code to select the cell in the pivot table that contains the title of the calculated field you want to hide, and then delete the selection. This works as long as you have another datafield already in the table. Example below:

您可以指示代码选择数据透视表中包含要隐藏的计算字段的标题的单元格，而不是修改方向属性，然后删除该选择。只要您在表中已经有另一个数据字段，这就会起作用。下面的例子：

Scenario: Pivot table covers the range A1:G10. Calculated field "Margin" is already in the table, and you want to add the data field "Sales" and remove the "Margin" calc field.

场景：数据透视表涵盖范围 A1:G10。计算字段“Margin”已在表中，您要添加数据字段“Sales”并删除“Margin”计算字段。

Code to execute:

要执行的代码：

'Add Sales data field
 ActiveSheet.PivotTables(Pname).AddDataField ActiveSheet.PivotTables( _
    Pname).PivotFields("SALES"), "Sum of SALES", xlSum

 'At this point, the datafield titles are in vertically adjacent rows, named "Sum
 'of Margin" and "Sum of Sales" at locations B3 and B4 respectively.

 'Remove the "Sum of Margin" calculated field
  Range("B3").Delete

Not sure why this works, but I'm glad we at least have this to work with!

不知道为什么会这样，但我很高兴我们至少可以使用它！