aidan.salehi
01-05-2018, 07:32 AM
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
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