Excel VBA Application Object

[Beatrix]

Hi Everyone,

Can anyone please tell me the purpose of using With statement for Application at the beginning and at the end of the script as below??


With Application
.ScreenUpdating = False
.DisplayAlerts = False
.EnableEvents = False
Calc = .Calculation
.Calculation = xlCalculationAutomatic
End With



With Application
.ScreenUpdating = True
.DisplayAlerts = True
.EnableEvents = True
.Calculation = Calc
End With

Cheers
B.

[mikerickson]

To avoid having to type


Application.ScreenUpdating = False
Applictaion.DisplayAlerts = False
'etc:)

Each of those things, ScreenUpdating, Calculation, etc. take little bits of time to do. If a macro changes the workbook a lot, those bits can add up to a significant amount of time.

Turning them off before making any changes and then on again after, avoids them being performed everytime the code changes the workbook, saving a noticeable amount of time.

Note that the itiaial. Calculation value is saved and re-applied at the end.

[Beatrix]

Thanks very much It makes sense :)

Using ScreenUpdating, Calculation, etc is it default when it comes to changing the workbook? They are not used in all scripts. I noticed that it's used at batch processing more.. Did it sound right or silly :o) ??

[mikerickson]

It depends on the code.
If everything is in the back-ground, like calculating from a lot of data, and the macro doesn't change the screen, .ScreenUpdating isn't a factor in the macro.
If the macro doesn't write to cells, then Excel's calculation isn't a factor, so turning it off effects nothing.

For small, shorter routines, most coders won't turn things off, just to avoid the typing.

But if a routine seems to be taking some time, .ScreenUpdating and .Calculation are the first things that I try to see if the speed can be improved.

[Beatrix]

Thanks very much for taking your time to explain.