Date PivotItem Visible Property throws error

[Jon von der Heyden]

Hi All

This one has me stumped. The code below works for all pivot fields that are NOT date fields. As soon as I loop through the PivotItems in the date fields I get a Type Mismatch error on the highlighted line. This error will occur for any date item so fails on the first iteration.

Private Sub lbxChosenFields_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    Dim lngPosition As Long
    Dim rngField As Range
    Dim pvi As PivotItem
    Dim vararrHiddenValues As Variant
    Dim lngListItem As Long
    
    With Me.lbxChosenFields
        If .ListIndex = -1 Then Exit Sub
        mstrFIELDNAME = .List(.ListIndex)
    End With
    
    With mpvtREPORT.PivotFields(mstrFIELDNAME)
        lngPosition = .Position
        .Position = 1
        For Each pvi In .PivotItems
            [B][COLOR="Red"]If pvi.Visible = False Then[/COLOR][/B]
                If IsEmpty(vararrHiddenValues) Then
                    ReDim vararrHiddenValues(1)
                    vararrHiddenValues(1) = pvi.Value
                Else
                    ReDim Preserve vararrHiddenValues(UBound(vararrHiddenValues) + 1)
                    vararrHiddenValues(UBound(vararrHiddenValues)) = pvi.Value
                End If
                pvi.Visible = True
            End If
        Next pvi
    End With
    'more code here
End Sub

What's confusing is that the property clearly states True when I look in the Locals window.

Further, in debug mode and in the immediate window the following returns Error 2042, but does not throw Type Mismatch:

?mpvtreport.pivotfields("date").pivotitems(1).visible

Richard Schollar has already pointed out that I can just format the field as a double and then process that, but I would really like to know the cause of this issue because changing to double means I have to adapt alot of other code too.

 

[RoryA]

I think it's a bug - I've seen it reported in a few posts now (though I can't recreate it)
Like the new title by the way!

 

[Jon von der Heyden]

Thanks Rory. So it'll have to be the long complicated horrible fix then.

I think Richard's title should be changed to 'Can't do it myself so I hired someone better than me'.

 

[Richard Schollar]

Somebody mention me? Remind me again who gave you the fix to the problem in this thread...

 

[RoryA]

I think Richard's title should be changed to 'Can't do it myself so I hired someone better than me'.

The last three words are implied anyway, no?

 

[Richard Schollar]

OK hands up those on this thread who don't owe Richard an MSDN sub???

 

[RoryA]

I don't. You gave it to me, so, in the immortal words of Bros, I owe you nothing (mmm nothin', nothin' at aaaallll.)

 

[Richard Schollar]

In the words of EMF, You're Unbelievable!!

 

[RoryA]

Given your avatar, there must be something appropriate in 'Dark Side of the Moon'...

 

[Richard Schollar]

Wake up smell the roses!