SheetPivotTableChangeSync to Stop Wkbk Calculations

[kyle_bos]

Problem: I have a customer complainingthat every time they manipulate a pivot table in the workbook, that the workbookstarts calculating, which takes a long time and freezes the users Excel untilcalculations are finished.

My Thinking on a Solution: My thinkingis to use a workbook level event like SheetPivotTableChangeSync to control howextensive the calculations are and reduce calculation time and reduce the enduser's computer resource consumption.

Request: Can anyone offer up any goodadvice with example code?

 

[Worf]

The event you mentioned occurs after changes to a pivot table, it may be too late. The example below turns calculation to manual when the table is selected:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Select Case Intersect(Target, Me.PivotTables(1).TableRange1) Is Nothing
    Case True
        Application.Calculation = xlCalculationAutomatic
    Case False
        Application.Calculation = xlCalculationManual
End Select
End Sub

 

[kyle_bos]

Hi Worf,

Your response is great! I added a small tweak and used Application.Calculate instead of Application.Calculation =

xlCalculationAutomatic​

to hasten the calculations even further. It seems to work nicely. Just for reassurance, can you tell me if I need to specify what needs to be calculated (like workbook, worksheet, or range?) or is

Application.Calculate​

only going to calculate the worksheet because it is in the worksheet module?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Select Case Intersect(Target, Me.PivotTables(1).TableRange1) Is Nothing
    Case True
        Application.Calculate 
    Case False
        Application.Calculation = xlCalculationManual
End Select
End Sub

 

[Worf]

Hi

The example below shows how to refine it:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Select Case Target
    Case Is = 1
        Application.Calculate           ' all workbooks
    Case Is = 2
        Me.Calculate                    ' only this sheet
    Case Is = 3
        Me.Columns("d:e").Calculate     ' range
    Case Is = 4
        Application.Calculation = xlCalculationAutomatic
    Case Is = 5
        Application.Calculation = xlCalculationManual
    Case Is = 6
        Application.Calculation = xlCalculationSemiautomatic
End Select
End Sub

 

[kyle_bos]

Worf,

Are you saying that if I paste in the code as you have it that the workbook will choose the beast method? How is this working? Thank you for this important and awesome lesson. It's a big help towards advancing my VBA understanding for Excel.

 

[Worf]

Yes, the first option will calculate all open workbooks. To do it differently you have to tell Excel so.
Also, search the online help for CalculationVersion, CalculateFull and CalculateFullRebuild.