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 suppose 'eclipse' is an appropriate track; 'breathe' less so....

 

[Tom Moorehead]

Tom says:
It would appear that a bug was introduced in the 2007 version of Excel that causes the error, due to the silly way dates are handled by Excel. Normally, dates that are recognised as such are treated as numbers. However, in pivot tables, unless you specify that the

[FONT=courier new]PivotField([I][B]DateVariableName[/B][/I]).NumberFormat = "[/FONT]<dateformat>[FONT=courier new][I][B]some date format[/B][/I]"[/FONT]

the date will sometimes appear as a date, but show in the PivotItem.Name as "<date in="" american="" format="">American date format as string", even if you use a local format setting, which somehow prevents the use of position and visible attributes.
E.g. I have experienced a similar problem where:
pvt.PivotField("StartDate").PivotItems(2).Name = "1/21/2014"
pvt.PivotField("StartDate").PivotItems(2).Value = "21/01/2014 ' yet
pvt.PivotField("StartDate").PivotItems(2).Visible = Error 2042

So you need to have

[FONT=courier new]mpvtreport.pivotfields("date").NumberFormat="mm/dd/yy" [/FONT]'or 
[FONT=courier new]mpvtreport.pivotfields("date").NumberFormat="d-mmm-yyyy"[/FONT]

(or similar), in order to use .position or .visible

Hope this helps!</date></dateformat>