Getting “Type Mismatch” with VBA PivotTables and Filter Dates

Gabriel222

New Member
Joined
Oct 24, 2008
Messages
9
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:
Code:
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. Code:
    .PivotFilters.Add2
    method but it doesn't seem to work seen as it is a
    Code:
    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).VisibleItemsList = 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--->
Code:
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)

Code:
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)

Thanks a ton !


Full Macro below:


Code:
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
 

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

Watch MrExcel Video

Forum statistics

Threads
1,118,415
Messages
5,571,975
Members
412,430
Latest member
Huuktkt
Top