PDA

View Full Version : [SOLVED] Excel VBA Application Object



Beatrix
11-06-2015, 08:18 AM
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
11-06-2015, 08:45 AM
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
11-06-2015, 09:05 AM
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
11-06-2015, 06:58 PM
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
11-07-2015, 02:53 PM
Thanks very much for taking your time to explain.