Getting “Type Mismatch” with VBA PivotTables and Filter Dates

gabriel222 · Apr 10th 2018

Hi all I am encountering a very frustrating problem with some VBA, especially seen as the hardcoded value of the variable works well, but not the variable itself !

The 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

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

2017
then the first Quarter of 2018 (called "T1-JFM" below)
then nothing for Months
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 :

The full Macro at the bottom of the post (The Dim(s) are at the top and Functions at the bottom)
Code
```
.PivotFilters.Add2
```
method but it doesn't seem to work seen as it is a
Code
```
xlPageField
```
field
Playing around with the quotation marks for DIM_ARRAY (both """" and Chr(32) )in multiple shapes and forms
Taking off the array() function --> ActiveSheet.PivotTables("PivotTable3").PivotFields(DIM_DATE_PTF).VisibleItemsList = DIM_ARRAY
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

Display More

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

Display More

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

Display More

Getting “Type Mismatch” with VBA PivotTables and Filter Dates

Participate now!

Share