Getting “Type Mismatch” with VBA PivotTables and Filter Dates

[Chunk222]

Goal: To filter each "pre-structured" pivot table with different ranges of dates, (the ranges are all continuous), the pivot tables are connected to a Tabular OLAP Cube


Script Description : In the script, (full script at the bottom of the post) I loop through the Years and then the Quarters (called Trimesters here), Months and finally the Dates as needed (the dates are calculated via a worksheet and called using a custom function), the idea here is to simulate what a user does with his mouse.


So at the moment with the below Sub, for a given PivotTable, in order to select for example all dates starting from the 1st of January 2017 up until Sunday the 8th of April included I will select :

1. 2017
2. then the first Quarter of 2018 (called "T1-JFM" below)
3. then nothing for Months
4. then all the dates of April up until the 8th included

Problem :
Once I hit the Sub that concerns the Dates ie: Cycle_Date, I get an error on the line:

ActiveSheet.PivotTables("PivotTable3").PivotFields(DIM_DATE_PTF).VisibleItemsList = Array(DIM_ARRAY)

The type of error message encountered on that line is "Run-time error ' 13': Type mismatch"


that line of code is the line that actually applies the Date filters, (prior to applying the filters the variable DIM_ARRAY is an Array that "accumulates" the strings used in the filter).


Already attempted :

1. The full Macro at the bottom of the post (The Dim(s) are at the top and Functions at the bottom)

2. .PivotFilters.Add2

   method but it doesn't seem to work seen as it is a

   xlPageField

   field
3. Playing around with the quotation marks for DIM_ARRAY (both """" and Chr(32) )in multiple shapes and forms
4. Taking off the array() function --> ActiveSheet.PivotTables("PivotTable3").PivotFields(DIM_DATE_PTF).VisibleIte msList = DIM_ARRAY
5. I also tried to apply the filter at each iteration, that works, but only the last "date" is kept, the previous ones are erased so i cannot add dates

BIG CLUE: Hardcoding the value of DIM-ARRAY works... so for some reason


THIS DOESN'T WORK--->

Private Sub Cycle_Date(YEAR_i_max As String, TRIMESTRE_i As String, MONTH_i As String, MONTH_i_max As Integer)




DATE_i_min = StdFilter(SheetName, "DATE_i_min")
DATE_i_max = StdFilter(SheetName, "DATE_i_max")


    If DATE_i_min = DATE_i_max Then ' Fork-out scenario
        
        i = 1
        
        DATE_i = WorksheetFunction.Proper(WorksheetFunction.Text(DateSerial(YEAR_i_max, MONTH_i_max, i), "YYYY-MM-DDTHH:MM:SS"))
        
        DIM_ARRAY = DIM_DATE_SUB & DIM_DATE_MOD_YEAR & ".&[" & YEAR_i_max & "]" & ".&[" & TRIMESTRE_i & "]" & ".&[" & MONTH_i & "]" & ".&[" & DATE_i & "]"
        
        ActiveSheet.PivotTables("PivotTable3").PivotFields(DIM_DATE_PTF).VisibleItemsList = Array(DIM_ARRAY)
        
        Exit Sub
        
    End If


    For i = DATE_i_min To DATE_i_max ' Loop through


        DATE_i = Format(DateSerial(YEAR_i_max, MONTH_i_max, i), "YYYY-MM-DDTHH:MM:SS")
    
        DIM_ARRAY_elmt = DIM_DATE_SUB & DIM_DATE_MOD_YEAR & ".&[" & YEAR_i_max & "]" & ".&[" & TRIMESTRE_i & "]" & ".&[" & MONTH_i & "]" & ".&[" & DATE_i & "]"
    
        If i = DATE_i_min Then
        
                DIM_ARRAY = DIM_ARRAY_elmt


            ElseIf i = DATE_i_max Then
            
                DIM_ARRAY = DIM_ARRAY & DIM_ARRAY_elmt
                
            ElseIf DIM_ARRAY <> "" Then
                
                DIM_ARRAY = DIM_ARRAY & DIM_ARRAY_elmt


        End If


    Next


    Debug.Print "Dates -->"; DIM_ARRAY
    
    ActiveSheet.PivotTables("PivotTable3").PivotFields(DIM_DATE_PTF).VisibleItemsList = Array(DIM_ARRAY)
    
End Sub

but replacing DIM_ARRAY with its value, this DOES WORK !! --->
(here I replaced DIM_ARRAY with exactly what is evaluated by Debug.Print DIM_ARRAY)

Private Sub Cycle_Date(YEAR_i_max As String, TRIMESTRE_i As String, MONTH_i As String, MONTH_i_max As Integer)




DATE_i_min = StdFilter(SheetName, "DATE_i_min")
DATE_i_max = StdFilter(SheetName, "DATE_i_max")


    If DATE_i_min = DATE_i_max Then ' Fork-out scenario
        
        i = 1
        
        DATE_i = WorksheetFunction.Proper(WorksheetFunction.Text(DateSerial(YEAR_i_max, MONTH_i_max, i), "YYYY-MM-DDTHH:MM:SS"))
        
        DIM_ARRAY = DIM_DATE_SUB & DIM_DATE_MOD_YEAR & ".&[" & YEAR_i_max & "]" & ".&[" & TRIMESTRE_i & "]" & ".&[" & MONTH_i & "]" & ".&[" & DATE_i & "]"
        
        ActiveSheet.PivotTables("PivotTable3").PivotFields(DIM_DATE_PTF).VisibleItemsList = Array(DIM_ARRAY)
        
        Exit Sub
        
    End If


    For i = DATE_i_min To DATE_i_max ' Loop through


        DATE_i = Format(DateSerial(YEAR_i_max, MONTH_i_max, i), "YYYY-MM-DDTHH:MM:SS")
    
        DIM_ARRAY_elmt = DIM_DATE_SUB & DIM_DATE_MOD_YEAR & ".&[" & YEAR_i_max & "]" & ".&[" & TRIMESTRE_i & "]" & ".&[" & MONTH_i & "]" & ".&[" & DATE_i & "]"
    
        If i = DATE_i_min Then
        
                DIM_ARRAY = """" & DIM_ARRAY_elmt & """"


            ElseIf i = DATE_i_max Then
            
                DIM_ARRAY = DIM_ARRAY & "," & """" & DIM_ARRAY_elmt & """"
                
            ElseIf DIM_ARRAY <> "" Then
                
                DIM_ARRAY = DIM_ARRAY & "," & """" & DIM_ARRAY_elmt & """"


        End If


    Next


    Debug.Print DIM_ARRAY
    
    ActiveSheet.PivotTables("PivotTable3").PivotFields(DIM_DATE_PTF).VisibleItemsList = Array( _
    "[DIM_DATE_CREATION].[CALENDRIER_CREATION].[ANNEE].&[2018].&[T2 - AMJ].&[Avril].&[2018-04-01T00:00:00]", _
    "[DIM_DATE_CREATION].[CALENDRIER_CREATION].[ANNEE].&[2018].&[T2 - AMJ].&[Avril].&[2018-04-02T00:00:00]", _
    "[DIM_DATE_CREATION].[CALENDRIER_CREATION].[ANNEE].&[2018].&[T2 - AMJ].&[Avril].&[2018-04-03T00:00:00]", _
    "[DIM_DATE_CREATION].[CALENDRIER_CREATION].[ANNEE].&[2018].&[T2 - AMJ].&[Avril].&[2018-04-04T00:00:00]", _
    "[DIM_DATE_CREATION].[CALENDRIER_CREATION].[ANNEE].&[2018].&[T2 - AMJ].&[Avril].&[2018-04-05T00:00:00]", _
    "[DIM_DATE_CREATION].[CALENDRIER_CREATION].[ANNEE].&[2018].&[T2 - AMJ].&[Avril].&[2018-04-06T00:00:00]", _
    "[DIM_DATE_CREATION].[CALENDRIER_CREATION].[ANNEE].&[2018].&[T2 - AMJ].&[Avril].&[2018-04-07T00:00:00]", _
    "[DIM_DATE_CREATION].[CALENDRIER_CREATION].[ANNEE].&[2018].&[T2 - AMJ].&[Avril].&[2018-04-08T00:00:00]")
    
End Sub

Any ideas how I can resolve this? if you need additional information please don't hesitate


I'm on Excel VBA (MS0 365 - version 1708)


Full Macro below:

Option Explicit


' "_i" is for the iterating variables
' "min" and "max" are for the minimum and maximum bounds that define the iteration range
' "TRIMESTRE" is just another word for "QUARTER"


Dim DIM_DATE_MOD_YEAR As String ' for storing OLAP syntax
Dim DIM_DATE_MOD_TRIMESTRE As String ' for storing OLAP syntax
Dim DIM_DATE_MOD_MONTH As String ' for storing OLAP syntax
Dim DIM_DATE_MOD_DATE As String ' for storing OLAP syntax


Dim DIM_DATE_CREATION_BASE As String 'changes for each PivotTable "structure" but here we are using just one type of "structure" and just filtering the dates differently
Dim DIM_DATE_SUB As String 'changes for each Sub only, so only as we "loop" and/or "fork" and/or "fork/past" throughout the relevant Years Quarters Months and Dates
Dim DIM_DATE_PTF As String 'is used for the PivotTableField when the filters are applied


Dim DIM_ARRAY_elmt As String ' "feeds" DIM_ARRAY
Dim DIM_ARRAY As String ' is used as the string applied to the pivottable filters and is fed/stacked by DIM_ARRAY_elmt
Dim DIM_TEST As String


Dim i As Integer ' standard iterator


Dim YEAR_i As Integer
Dim YEAR_i_min As String
Dim YEAR_i_max As String


Dim TRIMESTRE_i As String
Dim TRIMESTRE_i_max As String
Dim TRIMESTRE_i_min As String


Dim MONTH_i As String
Dim MONTH_i_max As Integer
Dim MONTH_i_min As Integer


Dim DATE_i As Variant
Dim DATE_i_min As Integer
Dim DATE_i_max As Integer


Dim ws As Variant
Dim SheetNames As Variant
Dim SheetName As String
'Space






Sub Launch_Update()


'Application.ScreenUpdating = False


    Call Date_Filters


'Application.ScreenUpdating = True


End Sub




Private Sub Date_Filters()
   
        SheetNames = Array("NOW", "A-0 || J-7", "A-1 || à Date Equiv.", "A-1 || J-7 Atterissage") 'list of relevant sheets
        
        'setting variables:
        DIM_DATE_CREATION_BASE = "[DIM_DATE_CREATION].[CALENDRIER_CREATION]"
        
        DIM_DATE_SUB = DIM_DATE_CREATION_BASE & DIM_DATE_MOD_YEAR
        
        i = 0
        
    For Each ws In SheetNames
    
        Sheets(ws).Select
        SheetName = ActiveSheet.Name
        
            DIM_DATE_MOD_YEAR = ".[ANNEE]"
            DIM_DATE_PTF = DIM_DATE_CREATION_BASE & DIM_DATE_MOD_YEAR
            
        Call Cycle_Year
            
            i = 0
            DIM_DATE_MOD_TRIMESTRE = ".[TRIMESTRE]"
            DIM_DATE_PTF = DIM_DATE_CREATION_BASE & DIM_DATE_MOD_TRIMESTRE
            
        Call Cycle_Trimestre(YEAR_i_max)
            
            i = 0
            DIM_DATE_MOD_MONTH = ".[MOIS]"
            DIM_DATE_PTF = DIM_DATE_CREATION_BASE & DIM_DATE_MOD_MONTH
        
        Call Cycle_Month(YEAR_i_max, TRIMESTRE_i)
            
            i = 0
            DIM_DATE_MOD_DATE = ".[DATE]"
            DIM_DATE_PTF = DIM_DATE_CREATION_BASE & DIM_DATE_MOD_DATE
        
        Call Cycle_Date(YEAR_i_max, TRIMESTRE_i, MONTH_i, MONTH_i_max)
        
    Next ws
    
    MsgBox "Date Filter Sub has ended"


End Sub


Private Sub Cycle_Year()


YEAR_i_min = StdFilter(SheetName, "YEAR_i_min")
YEAR_i_max = StdFilter(SheetName, "YEAR_i_max")


    If YEAR_i_min = YEAR_i_max Then ' Fork-out scenario
        
        ActiveSheet.PivotTables("PivotTable3").PivotFields(DIM_DATE_PTF).VisibleItemsList = Array("")
        
        Exit Sub
        
    End If




    For YEAR_i = YEAR_i_min To YEAR_i_max - 1 ' Loop through


        DIM_ARRAY_elmt = DIM_DATE_PTF & ".&[" & YEAR_i & "]"


            If YEAR_i = YEAR_i_min Then
                DIM_ARRAY = DIM_ARRAY_elmt
  
            ElseIf YEAR_i = YEAR_i_max - 1 Then
                DIM_ARRAY = DIM_ARRAY & "," & DIM_ARRAY_elmt
            
            ElseIf DIM_ARRAY <> "" Then
                DIM_ARRAY = DIM_ARRAY & "," & DIM_ARRAY_elmt
                    
            End If


    Next




Debug.Print "Years -->"; DIM_ARRAY


    ActiveSheet.PivotTables("PivotTable3").PivotFields(DIM_DATE_PTF).VisibleItemsList = Array(DIM_ARRAY)


End Sub


Private Sub Cycle_Trimestre(YEAR_i_max As String)




TRIMESTRE_i_min = StdFilter(SheetName, "TRIMESTRE_i_min")
TRIMESTRE_i_max = StdFilter(SheetName, "TRIMESTRE_i_max")


    If TRIMESTRE_i_min = TRIMESTRE_i_max Then ' Fork-out scenario
        
        ActiveSheet.PivotTables("PivotTable3").PivotFields(DIM_DATE_PTF).VisibleItemsList = Array("")
        
        Exit Sub
        
    End If




    For i = TRIMESTRE_i_min To TRIMESTRE_i_max ' Loop through
    
        If i = 1 Then
            TRIMESTRE_i = "T1 - JFM"
        
            ElseIf i = 2 Then
                TRIMESTRE_i = "T2 - AMJ"
                
            ElseIf i = 3 Then
                TRIMESTRE_i = "T3 - JAS"
                
            ElseIf i = 4 Then
                TRIMESTRE_i = "T4 - OND"
        
        End If


    
    DIM_ARRAY_elmt = DIM_DATE_SUB & DIM_DATE_MOD_YEAR & ".&[" & YEAR_i_max & "]" & ".&[" & TRIMESTRE_i & "]"
    
    
        If i = TRIMESTRE_i_min Then
            DIM_ARRAY = DIM_ARRAY_elmt
          
            ElseIf i = TRIMESTRE_i_max Then
                GoTo ApplyFilter1
                            
            ElseIf DIM_ARRAY <> "" Then
                DIM_ARRAY = DIM_ARRAY & "," & DIM_ARRAY_elmt
                    
        End If


    Next




ApplyFilter1:
    
    Debug.Print "Trimesters -->"; DIM_ARRAY
    
    ActiveSheet.PivotTables("PivotTable3").PivotFields(DIM_DATE_PTF).VisibleItemsList = Array(DIM_ARRAY)


End Sub
Private Sub Cycle_Month(YEAR_i_max As String, TRIMESTRE_i As String)




MONTH_i_min = StdFilter(SheetName, "MONTH_i_min")
MONTH_i_max = StdFilter(SheetName, "MONTH_i_max")


    If MONTH_i_min = MONTH_i_max Then ' Fork-out scenario
    
        ActiveSheet.PivotTables("PivotTable3").PivotFields(DIM_DATE_PTF).VisibleItemsList = Array("")
        
        MONTH_i = WorksheetFunction.Proper(WorksheetFunction.Text(DateSerial(2018, MONTH_i_min, 1), "[$-40C]MMMM"))
        
        Exit Sub
        
    End If


    For i = MONTH_i_min To MONTH_i_max ' Loop through


    MONTH_i = WorksheetFunction.Proper(WorksheetFunction.Text(DateSerial(2018, i, 1), "[$-40C]MMMM"))
    
    DIM_ARRAY_elmt = DIM_DATE_SUB & DIM_DATE_MOD_YEAR & ".&[" & YEAR_i_max & "]" & ".&[" & TRIMESTRE_i & "]" & ".&[" & MONTH_i & "]"
    
    
        If i = MONTH_i_min Then
            DIM_ARRAY = DIM_ARRAY_elmt
          
            ElseIf i = MONTH_i_max Then
                DIM_ARRAY = DIM_ARRAY_elmt
                GoTo ApplyFilter2
            
            ElseIf DIM_ARRAY <> "" Then
                DIM_ARRAY = DIM_ARRAY & "," & DIM_ARRAY_elmt
                    
        End If


    Next


ApplyFilter2:


    Debug.Print "Months -->"; DIM_ARRAY
    
    ActiveSheet.PivotTables("PivotTable3").PivotFields(DIM_DATE_PTF).VisibleItemsList = Array(DIM_ARRAY)


    MONTH_i = WorksheetFunction.Proper(WorksheetFunction.Text(DateSerial(2018, i - 1, 1), "[$-40C]MMMM"))


End Sub
Private Sub Cycle_Date(YEAR_i_max As String, TRIMESTRE_i As String, MONTH_i As String, MONTH_i_max As Integer)




DATE_i_min = StdFilter(SheetName, "DATE_i_min")
DATE_i_max = StdFilter(SheetName, "DATE_i_max")


    If DATE_i_min = DATE_i_max Then ' Fork-out scenario
        
        i = 1
        
        DATE_i = WorksheetFunction.Proper(WorksheetFunction.Text(DateSerial(YEAR_i_max, MONTH_i_max, i), "YYYY-MM-DDTHH:MM:SS"))
        
        DIM_ARRAY = DIM_DATE_SUB & DIM_DATE_MOD_YEAR & ".&[" & YEAR_i_max & "]" & ".&[" & TRIMESTRE_i & "]" & ".&[" & MONTH_i & "]" & ".&[" & DATE_i & "]"
        
        ActiveSheet.PivotTables("PivotTable3").PivotFields(DIM_DATE_PTF).VisibleItemsList = Array(DIM_ARRAY)
        
        Exit Sub
        
    End If


    For i = DATE_i_min To DATE_i_max ' Loop through


        DATE_i = Format(DateSerial(YEAR_i_max, MONTH_i_max, i), "YYYY-MM-DDTHH:MM:SS")
    
        DIM_ARRAY_elmt = DIM_DATE_SUB & DIM_DATE_MOD_YEAR & ".&[" & YEAR_i_max & "]" & ".&[" & TRIMESTRE_i & "]" & ".&[" & MONTH_i & "]" & ".&[" & DATE_i & "]"
    
        If i = DATE_i_min Then
        
                DIM_ARRAY = """" & DIM_ARRAY_elmt & """"


            ElseIf i = DATE_i_max Then
            
                DIM_ARRAY = DIM_ARRAY & "," & """" & DIM_ARRAY_elmt & """"
                
            ElseIf DIM_ARRAY <> "" Then
                
                DIM_ARRAY = DIM_ARRAY & "," & """" & DIM_ARRAY_elmt & """"


        End If


    Next


    Debug.Print "Dates -->"; DIM_ARRAY
    
    ActiveSheet.PivotTables("PivotTable3").PivotFields(DIM_DATE_PTF).VisibleItemsList = Array( _
    "[DIM_DATE_CREATION].[CALENDRIER_CREATION].[ANNEE].&[2018].&[T2 - AMJ].&[Avril].&[2018-04-01T00:00:00]", _
    "[DIM_DATE_CREATION].[CALENDRIER_CREATION].[ANNEE].&[2018].&[T2 - AMJ].&[Avril].&[2018-04-02T00:00:00]", _
    "[DIM_DATE_CREATION].[CALENDRIER_CREATION].[ANNEE].&[2018].&[T2 - AMJ].&[Avril].&[2018-04-03T00:00:00]", _
    "[DIM_DATE_CREATION].[CALENDRIER_CREATION].[ANNEE].&[2018].&[T2 - AMJ].&[Avril].&[2018-04-04T00:00:00]", _
    "[DIM_DATE_CREATION].[CALENDRIER_CREATION].[ANNEE].&[2018].&[T2 - AMJ].&[Avril].&[2018-04-05T00:00:00]", _
    "[DIM_DATE_CREATION].[CALENDRIER_CREATION].[ANNEE].&[2018].&[T2 - AMJ].&[Avril].&[2018-04-06T00:00:00]", _
    "[DIM_DATE_CREATION].[CALENDRIER_CREATION].[ANNEE].&[2018].&[T2 - AMJ].&[Avril].&[2018-04-07T00:00:00]", _
    "[DIM_DATE_CREATION].[CALENDRIER_CREATION].[ANNEE].&[2018].&[T2 - AMJ].&[Avril].&[2018-04-08T00:00:00]")
    
End Sub