How do I prevent Excel from rendering the spreadsheet as my macro calculates it?

Question

My macro updates a large spreadsheet with numbers, but it runs very slowly as excel is rendering the result as it computes it. How do I stop excel from rendering the output until the macro is complete?

Answer 1

You can turn off automatic calculation in the options dialog, it sets it so that it only calculates when you press F9.

Answer 2

Application.ScreenUpdating = False

And of course set it back to True again when you've finished, even if an error is raised. Example:

Public Sub MyMacro
    On Error GoTo ErrHandler
    Application.ScreenUpdating = False
    ... do my stuff that might raise an error
    Application.ScreenUpdating = True
    Exit Sub
ErrHandler:
    Application.ScreenUpdating = True
    ... Do something with the error, e.g. MsgBox       
End Sub

Answer 3

I use both of the proposed solutions:

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
...
...
...
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

Answer 4

Building upon Joe and SeeR (this uses old syntax so it's compatible with VBA of Office 2000):

On Error Goto AfterCalculation Application.ScreenUpdating = False Application.Calculation = xlCalculationManual ...

AfterCalculation: Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True

Answer 5

I also prefer to use both of the proposed solutions, but also keeping the users previous calculation mode.

For this particular application this might be no biggie, but it's usually best practice to let the users have their settings restored after your procedure is finished:

Application.ScreenUpdating = False
PreviousCalcMode = Application.Calculation
Application.Calculation = xlCalculationManual
   ...  
   ...
   ...
Application.Calculation = PreviousCalcMode
Application.ScreenUpdating = True

NB. It would also be worth your time to insert some error handling that turns on Application.ScreenUpdating should an error occur in your otherwise brilliant code ;) If memory serves me right, Excel will not show any errormessages etc when ScreenUpdating = false. Something like this:

Sub DoSomeThing


On Error Goto DisplayError

Application.ScreenUpdating = False
PreviousCalcMode = Application.Calculation
Application.Calculation = xlCalculationManual
   ...  
   ...
   ...
Application.Calculation = PreviousCalcMode
Application.ScreenUpdating = True

Exit Sub

DisplayError:
Application.Calculation = PreviousCalcMode
Application.ScreenUpdating = True

MsgBox Err.Description
End 'This stops execution of macro, in some macros this might not be what you want'
    '(i.e you might want to close files etc)'
End Sub