为什么这个PivotItem.Visible调用抛出一个TypeMismatch错误？ [英] Why is this PivotItem.Visible call throwing a TypeMismatch error?

问题描述

这是令我们感到困惑的。我有一个标准的透视表，其中包含报表过滤器，允许多个项目选择。我可以在报表过滤器中获取所选项目：

  Dim pi As PivotItem 
对于每个pi在数据透视表MyPivot）。PivotFields（MyField）。PivotItems 
如果pi.Visible Then 
 Debug.Print pi.Value 
 End If 
 Next 
  

简单。我的collegue有一个标准的透视表，其上有一个报告过滤器，允许多个项目选择。他尝试使用相同的代码

在报表过滤器中获取所选项目：

  Dim pi As PivotItem 
对于每个pi在数据透视表（HisPivot）。PivotFields（HisField）。PivotItems 
如果pi.Visible then 
 Debug.Print pi.Value 
 End If 
下一个
  

并在 pi.Visible 。我们知道，可见是 pi 中的一个属性，就像在输入 pi之后一样。 intellisense将显示所有的 PivotItem 属性和方法（如您所料）。我们知道pi包含有效的PivotItem，因为调用 pi.Value 正确打印值（删除如果 / End If 语句只是让它打印值，无论打印列表中的每个项目）。他的报告过滤器没有什么特殊 - 它不是计算的字段或任何类似的。大多数其他属性 PivotItem 也失败。

有谁知道为什么 PivotItem 会显示这个行为？ MSDN参考似乎相当不足。

解决方案

我的同事在这篇文章：

对于您的信息和其他有这个问题的人 - 我已经破解并完成了这么简单我发现通过明确地将PivotField的NUMBER格式显示为DATE格式（而不是GENERAL），然后.PivotItem.Visible = True指令在2007年下正确执行，甚至英国dd / mm / yyy日期格式。但是如果不是.PivotItems（i）.Visible然后仍然咳嗽类型不匹配错误。将PivotField上的DATE格式更改为dd mmm yyy（不改变语言环境）可使代码在2007年正常运行。

注意：设置NUMBER格式的如果数据透视表的源区域是数据列表，则透视字段似乎仅可用。如果数据源只是一个单元格范围，那么字段设置diaglogue上的NUMBER按钮不存在。

看来，Excel 2003会正确地合并一个包含英国日期为正确的日期，因此按预期执行VBA代码，而Excel 2007/2010不会导致后果类型不匹配错误。这些版本需要帮助，明确地使PivotField NUMBER格式DATE具有在几个月和几天之间无歧义的图片。

简而言之，如果您有此问题，您可以格式化PivotField作为DATE，代码将正常工作。

除了其他任何内容之外，您提供的这个代码在美国WRS下工作正常的洞察力是需要开启者在VBA代码之外找到一个解决方案。为此我非常感谢 - 谢谢！

我不会假装理解大多数，或者为什么格式应该对PivotItem属性有任何影响，但它已经有效。我们的解决方案与详细的答案略有不同：他的报告过滤器的日期格式为 * dd / mm / yyyy 。将其更改为 dd / mm / yyyy 可以解决问题。完全令人困惑。

This is baffling us. I have a standard pivot table with a report filter on it that allows multiple selection of items. I can get the selected items in the report filter with:

Dim pi As PivotItem
For Each pi In PivotTables("MyPivot").PivotFields("MyField").PivotItems
    If pi.Visible Then
        Debug.Print pi.Value
    End If
Next

Simple. My collegue has a standard pivot table with a report filter on it that allows multiple selection of items. He tries getting selected items in the report filter with the same code:

Dim pi As PivotItem
For Each pi In PivotTables("HisPivot").PivotFields("HisField").PivotItems
    If pi.Visible Then
        Debug.Print pi.Value
    End If
Next

And gets a Type Mismatch error on pi.Visible. We know that Visible is a property in pi, as after typing pi. the intellisense appears with all the PivotItem properties and methods (as you'd expect). We know that pi contains a valid PivotItem, as calling pi.Value prints the value correctly (removing the If/End If statements to just let it print the value regardless will print every item in the list). There is nothing 'special' about his report filter - it isn't a calculated field or anything like that. Most other properties of PivotItem also fail.

Does anyone know why PivotItem would show this behaviour? The MSDN reference seems rather inadequate.

解决方案

My collegue found the answer after much Googling in this post:

For your information and for others with this problem - I have cracked it and done so simply!

I have found that by explicitly making the NUMBER format of the PivotField a DATE format (rather than GENERAL) then the .PivotItem.Visible = True instruction performs correctly under 2007, even with UK dd/mm/yyy date formats. However If Not .PivotItems(i).Visible Then still coughs with the type mismatch error. Changing the DATE format on the PivotField to dd mmm yyy (without also changing locale) makes the code work correctly under 2007.

NB: The option to set the NUMBER format of a Pivot Field appears to be only available if the source area of the Pivot Table is a DATA LIST. If the data source is simply a cell range, then the NUMBER button on the field setting diaglogue is absent.

It would appear that Excel 2003 correctly coalesces a GENERAL formatted cell containing a UK date to the 'correct date' and thus execute the VBA code as expected, whereas Excel 2007 / 2010 does not with the consequential type mismatch error. These versions need help by expressly making the PivotField NUMBER format DATE having a picture which is unambigious between months and days.

In short if you have this problem and you can format the PivotField as DATE the code will then work correctly.

More than anything else, the insight you provided that this code works fine under USA WRS was the eye-opener required to go look elsewhere beyond the VBA code for a solution. For that I am most grateful - THANK YOU!

I'm not going to pretend to understand most of that or why the format should have any effect at all on PivotItem properties, but it has worked. Our solution was slightly different to the answer detailed there: his report filter had the date format *dd/mm/yyyy. Changing it to dd/mm/yyyy fixed the issue. Totally baffling.

这篇关于为什么这个PivotItem.Visible调用抛出一个TypeMismatch错误？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！