Why is this PivotItem.Visible call throwing a TypeMismatch error?

Question

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.

Answer 1

After researching this for a bit, everything I have found is pointing at this being a bug in the VBA library code. However, I did seem to (accidently) stumble across a method that appears to fix the error, that I did not see mentioned in any other threads / forums about this error.

The pivot table I was working with basically looked like this:

and I was trying to have a macro hide the highlighted sections. Note that the error was only being thrown for the first highlighted section, which contained blank dates / data.

I was using this code:

Function RemovePivotTableBlanks(ByRef pt As PivotTable, ByVal field As String)
    Dim pi As PivotItem

    For Each pi In pt.PivotFields(field).PivotItems
        If Left(pi.Value, 1) = "<" Or Left(pi.Value, 1) = ">" Or pi.Value = "(blank)" Then

            'this throws an error, but only for the first PivotItem
            If pi.Visible = True Then
                pi.Visible = False
            End If
        End If
    Next pi
End Function

Also note that the .Visible property still showed the correct value in the Locals window.

For some reason, if I assigned the .Value to a string, and back to the PivotItem object, the error went away:

Function RemovePivotTableBlanks(ByRef pt As PivotTable, ByVal field As String)
Application.ScreenUpdating = False
Application.EnableEvents = False
    Dim pi As PivotItem
    Dim str As String

    For Each pi In pt.PivotFields(field).PivotItems
        If Left(pi.Value, 1) = "<" Or Left(pi.Value, 1) = ">" Or pi.Value = "(blank)" Then

            str = pi.Value
            pi.Value = str

            'no error for the first PivotItem anymore
            If pi.Visible = True Then
                pi.Visible = False
            End If
        End If
    Next pi
Application.EnableEvents = True
Application.ScreenUpdating = True
End Function

Answer 2

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.

Answer 3

I wrote a post some time back on this at http://dailydoseofexcel.com/archives/2013/11/09/a-date-with-pivotitems/

What's weird is that this seems to affect some machines and not others, and also some versions and not others.

I wrote a small test routine that sets up a pivot with two items in it - one a date, and one a number - and that then tries to set the .visible status to true...first with the number format set to a date format, and then with the number format set to 'General'

On my system, I get an error on 2010 when the format is set to General, but no error when set to a date format. But strangely when I run the same code on Excel 2013, I get no error whatsoever. So it appears to be fixed.

A commenter at the Daily Dose post said it wasn't an issue for him in either version.

So maybe it's a weird combo of version and region.

Here's my test code, if anyone else is interested in seeing what it does on their box:

Sub Test()

Dim pf As PivotField
Dim pc As PivotCache
Dim pt As PivotTable
Dim pi As PivotItem
Dim rng As Range

[A1].Value = "Data"
[A2].Value = "=TODAY()"
[A3].Value = "=VALUE(TODAY())"

Set rng = Cells(ActiveSheet.UsedRange.Row, ActiveSheet.UsedRange.Columns.Count + ActiveSheet.UsedRange.Column + 1)

Set pc = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=[A1].CurrentRegion)
Set pt = pc.CreatePivotTable(TableDestination:=rng)
Set pf = ActiveSheet.PivotTables(1).PivotFields(1)


pf.NumberFormat = "d/mm/yyyy"
For Each pi In pf.PivotItems
pi.Visible = True
Next pi

pf.NumberFormat = "General"
For Each pi In pf.PivotItems
pi.Visible = True 'Code errors out for me here using Excel 2010
Next pi

End Sub

Answer 4

just ran into this issue, seemed that the multiple ways to format a date is really the bigger problem when working with pivot tables. if the format is a true dd/mm/yyyy (or mm/dd/yyyy) it seems fine, but shortening to yy gave me issues. changing the pi.value to string really did solve the issue with mismatch type error, however by going to pivot table field list, clicking on my date row pivot item and field settings there's a number format button where you can change the format of the pivot item like you would change the format of a cell. so however the dates are arranged and carried through the spreadsheet, as they come into the pivot table i was able to edit the format to "mmm d" and my problem was solved.