Excel VBA performance variance issue

[aidan.salehi]

I'm having an issue where my code is running much longer for a block of code compared to basically the same type of codes. Context - I'm reading Start and End week numbers from sheet1: "Report_DIV". there are 5 separate timeframes (5 Start and 5 End week numbers) input by the user. the week number values are flowing into the second sheet: "Report_LOB". there are 5 pivot tables on this second sheets that need to be filtered by the 5 timeframes (LOBStartWk and LOBEndWk's). if the week values change on the first sheet it will change in the second sheet as well and it triggers the worksheet calculate sub. the code will run for each pivot and updates the pivot item relating to week numbers: "FISCAL_WK_NBR).
Note: to ensure the code only runs for the pivots for which the respective timeframe has changed, I introduced dummy variables (StartWk1, EndWk2) and keep their current values in "Variables" sheet to compare with the user input week numbers.
the mysterious performance problem is that the code takes up to 20 seconds to run for the first timeframe (StartWk1 and EndWk1) as opposed to only 5 seconds for the other 4 timeframes!! I'd appreciate if you could help me figure out what is happening here!
here is the code for the second sheet worksheet calculate sub:

' for changes to worksheet "Report_LOB".Private Sub Worksheet_Calculate()
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
timer1 = Now()


' introduce dummy variables to track changes in timeframes on this worksheet.
Dim StartWk1 As Integer
Dim EndWk1 As Integer
Dim StartWk2 As Integer
Dim EndWk2 As Integer
Dim StartWk3 As Integer
Dim EndWk3 As Integer
Dim StartWk4 As Integer
Dim EndWk4 As Integer
Dim StartWk5 As Integer
Dim EndWk5 As Integer


' reading timeframe values (user input) from "Report_DIV" tab. same values will flow into "Report_LOB" tab 
StartWk1 = Sheets("Report_DIV").Range("DIVStartWk1").Value
EndWk1 = Sheets("Report_DIV").Range("DIVEndWk1").Value
StartWk2 = Sheets("Report_DIV").Range("DIVStartWk2").Value
EndWk2 = Sheets("Report_DIV").Range("DIVEndWk2").Value
StartWk3 = Sheets("Report_DIV").Range("DIVStartWk3").Value
EndWk3 = Sheets("Report_DIV").Range("DIVEndWk3").Value
StartWk4 = Sheets("Report_DIV").Range("DIVStartWk4").Value
EndWk4 = Sheets("Report_DIV").Range("DIVEndWk4").Value
StartWk5 = Sheets("Report_DIV").Range("DIVStartWk5").Value
EndWk5 = Sheets("Report_DIV").Range("DIVEndWk5").Value


' 5 similar blocks of code to filter the week items in 5 pivot tables linked to 5 timeframes if week values are changed by the user.
' dummy variables' values are recorded in the "Variables" tab to compare with the user input (to determine if pivots need to be adjusted respectively).


Dim pf As PivotField
Dim pi As PivotItem
StartWkVal1 = Sheets("Variables").Range("StartWkVal1").Value
EndWkVal1 = Sheets("Variables").Range("EndWkVal1").Value
    If Range("LOBStartWk1").Value <> StartWkVal1 Or Range("LOBEndWk1").Value <> EndWkVal1 Then
    Sheets("Variables").Range("StartWkVal1").Value = Range("LOBStartWk1").Value
    Sheets("Variables").Range("EndWkVal1").Value = Range("LOBEndWk1").Value


    Set pf = Sheets("Report_LOB").PivotTables("PivotMetrics1").PivotFields("FISCAL_WK_NBR")


        For Each pi In pf.PivotItems
            pi.Visible = (pi.Name >= StartWk1 And pi.Name <= EndWk1)


        Next
    End If


Dim pf2 As PivotField
Dim pi2 As PivotItem
StartWkVal2 = Sheets("Variables").Range("StartWkVal2").Value
EndWkVal2 = Sheets("Variables").Range("EndWkVal2").Value
    If Range("LOBStartWk2").Value <> StartWkVal2 Or Range("LOBEndWk2").Value <> EndWkVal2 Then
    Sheets("Variables").Range("StartWkVal2").Value = Range("LOBStartWk2").Value
    Sheets("Variables").Range("EndWkVal2").Value = Range("LOBEndWk2").Value
    Set pf2 = Sheets("Report_LOB").PivotTables("PivotMetrics2").PivotFields("FISCAL_WK_NBR")


            For Each pi2 In pf2.PivotItems
                pi2.Visible = (pi2.Name >= StartWk2 And pi2.Name <= EndWk2)
            Next
    End If


Dim pf3 As PivotField
Dim pi3 As PivotItem
StartWkVal3 = Sheets("Variables").Range("StartWkVal3").Value
EndWkVal3 = Sheets("Variables").Range("EndWkVal3").Value
    If Range("LOBStartWk3").Value <> StartWkVal3 Or Range("LOBEndWk3").Value <> EndWkVal3 Then
    Sheets("Variables").Range("StartWkVal3").Value = Range("LOBStartWk3").Value
    Sheets("Variables").Range("EndWkVal3").Value = Range("LOBEndWk3").Value
    Set pf3 = Sheets("Report_LOB").PivotTables("PivotMetrics3").PivotFields("FISCAL_WK_NBR")


        For Each pi3 In pf3.PivotItems
            pi3.Visible = (pi3.Name >= StartWk3 And pi3.Name <= EndWk3)
        Next
    End If


Dim pf4 As PivotField
Dim pi4 As PivotItem
StartWkVal4 = Sheets("Variables").Range("StartWkVal4").Value
EndWkVal4 = Sheets("Variables").Range("EndWkVal4").Value
    If Range("LOBStartWk4").Value <> StartWkVal4 Or Range("LOBEndWk4").Value <> EndWkVal4 Then
    Sheets("Variables").Range("StartWkVal4").Value = Range("LOBStartWk4").Value
    Sheets("Variables").Range("EndWkVal4").Value = Range("LOBEndWk4").Value
    Set pf4 = Sheets("Report_LOB").PivotTables("PivotMetrics4").PivotFields("FISCAL_WK_NBR")


        For Each pi4 In pf4.PivotItems
            pi4.Visible = (pi4.Name >= StartWk4 And pi4.Name <= EndWk4)
        Next
    End If


Dim pf5 As PivotField
Dim pi5 As PivotItem
StartWkVal5 = Sheets("Variables").Range("StartWkVal5").Value
EndWkVal5 = Sheets("Variables").Range("EndWkVal5").Value
    If Range("LOBStartWk5").Value <> StartWkVal5 Or Range("LOBEndWk5").Value <> EndWkVal5 Then
    Sheets("Variables").Range("StartWkVal5").Value = Range("LOBStartWk5").Value
    Sheets("Variables").Range("EndWkVal5").Value = Range("LOBEndWk5").Value
    Set pf5 = Sheets("Report_LOB").PivotTables("PivotMetrics5").PivotFields("FISCAL_WK_NBR")


        For Each pi5 In pf5.PivotItems
            pi5.Visible = (pi5.Name >= StartWk5 And pi5.Name <= EndWk5)
        Next
    End If


Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.EnableEvents = True
timeend1 = Now()
Debug.Print DateDiff("s", timer1, timeend1)
End Sub

[SamT]

For more speed

In the block of code dealing with Sheets("Report_DIV"), Begin with

With Sheets("Report_DIV")

and end with

 
End With

Then delete all instances of "Sheets("Report_DIV")" inside the block

Place all the code dealing with Sheets("Variables"), inside one

With Sheets("Variables")
'
'
'
End with

Encapsulation block and again, delete all internal references to "Sheets("Variables")"

Since all your Ranges are Named, you probably don't need any Sheet references anywhere.

I notice that Start and End - WkVal1 are the names of Variables and of Ranges. This may be taking a long time for VBA to initialize the Variables. Change the Names of the variables OR of the Ranges so they are unique.

[Jan Karel Pieterse]

All I can think of is that the first pivottable is slower in updating its filtering. Do these pivot tables all have their own source data?

[p45cal]

You could try setting
Sheets("Report_LOB").PivotTables("PivotMetrics1").ManualUpdate=True
before changing the pivot and
Sheets("Report_LOB").PivotTables("PivotMetrics1").ManualUpdate=False
afterwards.
The same for the other pivot tables.

[SamT]

p45cal,

I don't know much about Pviot Tables, but what about

For Each Pt in Sheets("Report_LOB").PivotTables
Pt.ManualUpdate=True
Next

[p45cal]

Perfectly good, just remember to do the same with False afterwards