vba 遍历 PivotItems：运行时错误 91

Loop through PivotItems: runtime error 91

提问by Kablam

I have a dataset in a worksheet that can be different every time. I am creating a pivottable from that data, but it is possible that one of the PivotItems is not there. For example:

我在工作表中有一个数据集，每次都可能不同。我正在根据该数据创建一个数据透视表，但其中一个 PivotItems 可能不存在。例如：

.PivotItems("Administratie").Visible = False

If that specific value is not in my dataset, the VBA script fails, saying that it can't define the item in the specified Field. (error 1004)

如果该特定值不在我的数据集中，则 VBA 脚本将失败，表示它无法在指定字段中定义项目。（错误 1004）

So I thought a loop might work. I have the following:

所以我认为循环可能会起作用。我有以下几点：

Dim pvtField As PivotField
Dim pvtItem As PivotItem
Dim pvtItems As PivotItems

For Each pvtItem In pvtField.pvtItems
        pvtItem.Visible = False
Next

But that gives me an 91 error at the For Each pvtItem line:

但这在 For Each pvtItem 行给了我一个 91 错误：

Object variable or With block variable not set

I thought I declared the variables well enough, but I am most likely missing something obvious...

我以为我已经很好地声明了变量，但我很可能遗漏了一些明显的东西......

回答by Kablam

I've got it! :D

我懂了！:D

Dim Table As PivotTable
Dim FoundCell As Object
Dim All As Range
Dim PvI As PivotItem

    Set All = Worksheets("Analyse").Range("A7:AZ10000")
    Set Table = Worksheets("Analyse").PivotTables("tablename")
    For Each PvI In Table.PivotFields("fieldname").PivotItems
        Set FoundCell = All.Find(PvI.Name)
        If FoundCell <> "itemname" Then
            PvI.Visible = False
        End If
    Next

woohoo

呜呼

Thanks to MrExcel, the answer wasthere after all, though deeply buried.

多亏了 MrExcel，答案终于在那里，尽管被深深地埋葬了。

回答by Kablam

I had same error message too when trying to set pivotitem visible true and false .. this had worked previously, but wasn't working any more ... i was comparing two values, and had not explicitly changed string to integer for comparison .. doing this made error disappear..

当我尝试将 pivotitem 设置为可见的 true 和 false 时，我也有同样的错误消息..这以前工作过，但不再工作......我正在比较两个值，并且没有明确地将字符串更改为整数进行比较..这样做使错误消失..

.. so if you get this message, check if any values being compared to make the item visible or not are being compared properly .. otherwise pivotitem is null and it can't make that visible or not.

.. 因此，如果您收到此消息，请检查是否有任何值进行比较以使该项目可见或不正确进行比较 .. 否则，pivotitem 为 null 并且无法使其可见或不可见。

回答by Kablam

I had an error that said "unable to set the visible property of the pivot item class" at this line:

我在这一行有一个错误，提示“无法设置数据透视项类的可见属性”：

For Each pi In pt.PivotFields("Fecha").PivotItems
    If pi.Name = ffan Then
        pi.Visible = True
    Else
        pi.Visible = False '<------------------------
    End If
Next pi

Then i read on internet that I had to sort manual and then clear the cache. i did that but the error still appeared..... then i read that it was because i had date on that pivot field so i change it firs my colum to general number then the date i wanted to set visible i change it to general number too. then no problem!!!!!!!!!!!!!!.... here it is.... i hope this can be helpfull because i was desperate!!!

然后我在互联网上读到我必须对手册进行排序，然后清除缓存。我这样做了，但错误仍然出现.....然后我读到这是因为我在那个枢轴字段上有日期所以我将它更改为我的列到通用编号然后我想设置为可见的日期我将其更改为通用号也是。那么没问题!!!!!!!!!!!!!!...这里是....我希望这会有所帮助，因为我很绝望！！！

Dim an As Variant
an = UserForm8.Label1.Caption 'this label contains the date i want to see its the pivot item i want to see of my pivot fiel that is "Date"
Dim fan
fan = Format(an, "d m yyyy")  
Dim ffan
ffan = Format(fan, "general number")

Sheets("Datos refrigerante").Activate 'this is the sheet that has the data of the pivottable
Dim rango1 As Range
Range("B1").Select
Range(Selection, Selection.End(xlDown)).Select

Set rango1 = Selection
ActiveSheet.Cells(1, 1).Select
rango1.Select

Selection.NumberFormat = "General" 'I change the format of the column that has all my dates

'clear the cache
Dim pt As PivotTable
Dim ws As Worksheet
Dim pc As PivotCache

'change the settings
For Each ws In ActiveWorkbook.Worksheets
    For Each pt In ws.PivotTables
        pt.PivotCache.MissingItemsLimit = xlMissingItemsNone
    Next pt
Next ws

'refresh all the pivot caches
For Each pc In ActiveWorkbook.PivotCaches
  On Error Resume Next
  pc.Refresh
Next pc

'now select the pivot item i want
Dim pi As PivotItem

Set pt = Sheets("TD Refrigerante").PivotTables("PivotTable2")

'Sets Pivot Table to Manual Sort so you can manipulate PivotItems in PivotField
pt.PivotFields("Fecha").AutoSort xlManual, "Fecha"

'Speeds up code dramatically
pt.ManualUpdate = True

For Each pi In pt.PivotFields("Fecha").PivotItems
    If pi.Name = ffan Then
        pi.Visible = True
    Else
        pi.Visible = False
    End If
Next pi

pt.ManualUpdate = False
pt.PivotFields("Fecha").AutoSort xlAscending, "Fecha"

回答by Patrick Cuff

Try something like this:

尝试这样的事情：

Public Function Test()
    On Error GoTo Test_EH

    Dim pvtField As PivotField
    Dim pvtItem As PivotItem
    Dim pvtItems As PivotItems

    ' Change "Pivot" to the name of the worksheet that has the pivot table.
    ' Change "PivotTable1" to the name of the pivot table; right-click on the
    ' pivot table, and select Table Options... from the context menu to get the name.
    For Each pvtField In Worksheets("Pivot").PivotTables("PivotTable1").PivotFields
        Debug.Print "Pivot Field: " & pvtField.Name
        For Each pvtItem In pvtField.VisibleItems
            pvtItem.Visible = False
        Next
    Next

Exit Function

Test_EH:
    Debug.Print pvtItem.Name & " error(" & Err.Number & "): " & Err.Description
    Resume Next

End Function

If you want a function to just test for the existence of a pivot item, you can use something like this:

如果你想要一个函数来测试一个数据透视项的存在，你可以使用这样的东西：

Public Function PivotItemPresent(sName As String) As Boolean
    On Error GoTo PivotItemPresent_EH

    PivotItemPresent = False

    For Each pvtField In Worksheets("Pivot").PivotTables("PivotTable1").PivotFields
        For Each pvtItem In pvtField.VisibleItems
            If pvtItem.Name = sName Then
                PivotItemPresent = True
                Exit Function
            End If
        Next
    Next

    Exit Function

PivotItemPresent_EH:
    Debug.Print "Error(" & Err.Number & "): " & Err.Description
    Exit Function

End Function

You can call this from your code like this:

您可以像这样从代码中调用它：

If PivotItemPresent("name_of_the_thing") Then
    ' Do something
End If

回答by Kablam

For Each pvtField In Worksheets("my_sheet").PivotTables("my_table").PivotFields
    For Each pvtItem In pvtField.PivotItems
        Debug.Print vbTab & pvtItem.Name & ".Visible = " & pvtItem.Visible
        /*.PivotItems(pvtItem).Visible = False*/ 
    Next
Next
.PivotItems("certain_Item").Visible = True

That doesn't work still... all the variables are still visible. No error is shown, it compiles yet the values are still there.
The commented line I added there was my own "invention" but it's is not valid.

这仍然不起作用......所有变量仍然可见。没有显示错误，它编译但值仍然存在。
我在那里添加的注释行是我自己的“发明”，但它无效。

Edit: Quicky question: Can I use an IF statement to check if a certain PivotItem is actually in the PivotTable Data? Something like

编辑：快速问题：我可以使用 IF 语句来检查某个数据透视表是否确实在数据透视表数据中？就像是

If PivotItem("name_of_the_thing") = present Then {
    do_something()
}

回答by Patrick Cuff

You can't say ".PivotItems(pvtItem).Visible" outside a "With" block. Say "pvtField.PivotItems(pvtItem.Name).Visible = False" instead.

你不能.PivotItems(pvtItem).Visible在 " With" 块之外说 " " 。pvtField.PivotItems(pvtItem.Name).Visible = False改为“ ”。

I also edited my original answer to include error handling for when Excel can't set the Visible property. This happens because the Pivot table needs at least one row field, one column field and one data item, so the last of each of these can't be made invisible.

我还编辑了我的原始答案，以包含 Excel 无法设置 Visible 属性时的错误处理。发生这种情况是因为数据透视表至少需要一个行字段、一个列字段和一个数据项，因此不能将其中的最后一个设置为不可见。

You 'll also get the 1004 errror when trying to access a pivot item that is already invisible; I think you can ignore those.

尝试访问已经不可见的数据透视项时，您还会收到 1004 错误；我认为你可以忽略这些。

回答by Kablam

When I implement the code posted by Patrick, an -

当我实现帕特里克发布的代码时，一个 -

Unable to set the visible property of the PivotItem class

无法设置 PivotItem 类的可见属性

- error is thrown.

- 抛出错误。

Microsoft admits there's a bug: M$ help
But just hiding the line... is not an option ofcourse.

微软承认有一个错误：M$ help
但是只是隐藏这条线......当然不是一种选择。

回答by Kablam

The error is thrown at the end of the loop.
I combined both answers from Patrick into the following:

在循环结束时抛出错误。
我将帕特里克的两个答案合并为以下内容：

With ActiveSheet.PivotTables("Table").PivotFields("Field")

    Dim pvtField As Excel.PivotField
    Dim pvtItem As Excel.PivotItem
    Dim pvtItems As Excel.PivotItems

    For Each pvtField In Worksheets("Sheet").PivotTables("Table").PivotFields
        For Each pvtItem In pvtField.PivotItems
            If pvtItem.Name = "ItemTitle" Then
                pvtField.PivotItems("ItemTitle").Visible = True
            Else
                pvtField.PivotItems(pvtItem.Name).Visible = False
            End If
        Next
    Next
End With

If the Item matches a particular string, that Item is set True. Else; Item set False. At the False condition the error is thrown.
I know there is exactly one match for the True condition. Though when I 'F8' my way through the macro, the True condition is never entered...

如果 Item 匹配特定字符串，则该 Item 设置为 True。别的; 项目设置为 False。在 False 条件下，将抛出错误。
我知道 True 条件只有一个匹配项。尽管当我通过宏“F8”时，从未输入过 True 条件......

And that explains the error, everything is set False. (thanks Patrick!)

这解释了错误，一切都设置为 False。（感谢帕特里克！）

Leads me to the question... what exactly ISa PivotItem?

让我想到了一个问题...... PivotItem究竟是什么？

Idea of the thing:
It solves (or should solve) the following: a set of Data with a variable size where, for this specific table, one column is of interest. From that column I need the count of a value and have that put in a table. There are some conditions to the table, and a combination with another column is needed as well, so PivotTable is the best solution.
The problem is: in some datasets that one specific value does not appear. The values that DO appear are different every time.

事物的想法：
它解决（或应该解决）以下问题：一组大小可变的数据，对于这个特定的表，其中一列是有意义的。从该列中，我需要一个值的计数并将其放入表格中。表格有一些条件，还需要与另一列组合，所以数据透视表是最好的解决方案。
问题是：在某些数据集中，没有出现一个特定值。DO 出现的值每次都不同。

回答by Patrick Cuff

The PivotItems are the individual values in a field (column, row, data). I think of them as the "buckets" that hold all the individual data items you want to aggregate.

PivotItems 是字段（列、行、数据）中的单个值。我认为它们是包含您想要聚合的所有单个数据项的“存储桶”。

Rather than go through all the pivot table fields (column, row, and data), you can just go through the fields you're interested in. For example, this code will show only the specified pivot items for the specified field:

您无需浏览所有数据透视表字段（列、行和数据），而只需浏览您感兴趣的字段。例如，此代码将仅显示指定字段的指定数据透视项：

Public Sub ShowInPivot(Field As String, Item As String)
    On Error GoTo ShowInPivot_EH

    Dim pvtField As PivotField
    Dim pvtItem As PivotItem
    Dim pvtItems As PivotItems

    For Each pvtItem In Worksheets("Pivot").PivotTables("PivotTable1").PivotFields(Field).PivotItems
        If pvtItem.Name = Item Then
            pvtItem.Visible = True
        Else
            pvtItem.Visible = False
        End If
    Next

    Exit Sub

ShowInPivot_EH:
    Debug.Print "Error(" & Err.Number & "): " & Err.Description
    Exit Sub

End Sub

Suppose I have a pivot table showing the count of issues per customer release and where they were detected in our SDLC. "Customer" and "Release" are column fields and "Phase" is a row field. If I wanted to limit the pivot table to counting issues for CustomerA, Release 1.2 during QA I could use the sub above like this:

假设我有一个数据透视表，显示每个客户发布的问题数量以及在我们的 SDLC 中检测到这些问题的位置。“客户”和“发布”是列字段，“阶段”是行字段。如果我想在 QA 期间将数据透视表限制为计算 CustomerA 1.2 版的问题，我可以像这样使用上面的子：

ShowInPivot "Customer", "CustomerA"
ShowInPivot "Release", "1.2"
ShowInPivot "Phase", "QA"