Excel VBA Calculation

Question

We have VBA code that turns off Excel Automatic calculation, runs the VBA code and then turns Automatic calculation back on.

Part way through the code we need the spreadsheet calculated.

This works ok in Windows but when we run the same code on Mac, it doesn't appear to work in the same way.

So code would be

Application.Calculation = xlCalculationManual

VBA code

Application.Calculation = xlCalculationAutomatic             To update the spreadsheet

Application.Calculation = xlCalculationManual                 To turn calculation off

VBA code

Application.Calculation = xlCalculationAutomatic

On Mac, this does not work.

We have tried

Calculate instead of xlCalculationAutomatic and xlCalculationManual and that didn't work.

On Mac the xlCalculationAutomatic doesn't seem to work immediately. We got it to work by running calculate after it.

Any answers would be appreciated.

Thank you

Answer 1

Call the Calculate method of the worksheet instead of

Application.Calculation = xlCalculationAutomatic             To update the spreadsheet

Application.Calculation = xlCalculationManual                 To turn calculation off

to force the calculation. But the calculation results might not be visible on screen at this point.

Andreas.

Answer 2

Thanks Andreas

I have tried using calculate but that doesn't work.

I turn off screen updating at the start, Application.ScreenUpdating = False and then turn it on again at the end of the Macro.

I replaced xlCalculation with xlCalculationManual and Calculate but no effect.

It is strange. The code works find on Windows but not on Mac.

I have come across other anomalies on the Mac version.

Answer 3

I have tried using calculate but that doesn't work.

I turn off screen updating at the start, Application.ScreenUpdating = False and then turn it on again at the end of the Macro.

The calculation works, you can access the cells with the formulas afterwards and get the correct results.

It's just the screen that is not updated. Try to call DoEvents after the calculation to give some time slice to the system, but that can extend the run time of your macro significantly.

Andreas.