CODEWITHSUNDEEP
excelvba
Andrey Minakov
Andrey Minakov

Reputation: 566

How stop PivotTable re-calculation after I change measure via VBA

Using VBA macros, I have to change measure formulas in data model in Excel. The code snipped which changes formula of every measure:

ActiveWorkbook.Model.ModelMeasures(1).Formula = textOfNewMeasureFormula

Right after the macros changes every particular measure, Excel starts re-calulating the PivotTable. In case of big data it may take a long time and so inconvenient. It would be great to change all the measure needed and launch re-calculation after all that changes. But I cannot find a solution for that.

This code doesn't work, because it's about updating data:

activeSheet.PivotTables(1).ManualUpdate = true

It doesn't have any effect on the calculation after formula measure change.

This doesn't help either, because it's about formulas in Excel Sheets:

Application.Calculation = xlCalculationManual

Just in case - I didn't check 

Application.Visible = false

because it is anyway not acceptable for my case. Thanks a lot in advance.

Upvotes: 0

Views: 687

Answers (1)

cemklkn
cemklkn

Reputation: 40

In the settings (File->Options->Formula), there is an option for formula calculation. If you change it to "manual", it will disable automatic calculations. However, you need to control the update manually, you can use "F9" button for it. If you want to control this setting in macro, you can use:

Application.Calculation = xlManual

to turn calulation method to manual 

Application.Calculation = xlAutomatic

to turn the calculation method to automatic.

Upvotes: 0

Related Questions