Application Enable Events/calculation

[John Davis]

Hello All:

I'm trying to speed up some of my macros and have been advised in the past to set Enable Events to false and Calculation to x1Calculation Manual. However, before I proceed to do this can any one tell me exactly what I'm doing when I set Enable Events to false? As well when I set Calculation to x1 Manual?

 

[Jonmo1]

Events are Things that happen when an event occurs.
Things like
Changing the value of a Cell
Delting/Inserting Rows
Right Clicking A Cell
Selecting A Cell
Changing Sheets

There are tons more, but those are just examples..

Now you can have VBA code that makes things happen anytime one of those events happens. Like Whenever a Cell Value is changed, display a message box..

Setting EnableEvents to FALSE makes those things not happen.


Calculation..
Whenever a value in a cell is changed, any formula that is dependant on that cell recalculates. There are some formulas like Indirect and Offset that recalculate ANY time something changes, even if that formula is not dependant on the Cell that changed.

So setting calculation to manual, makes formulas not update anymore.
So say you have a formula in A1 like =B1
And if you have calculation off, and you change B1's value, the formula in A1 will not update. You need to be carefull with this if your macros depend on the values of formulas AND your code changes the values of the cells those formulas use.

Hope this helps..

 

[John Davis]

Events are Things that happen when an event occurs.
Things like
Changing the value of a Cell
Delting/Inserting Rows
Right Clicking A Cell
Selecting A Cell
Changing Sheets

There are tons more, but those are just examples..

Now you can have VBA code that makes things happen anytime one of those events happens. Like Whenever a Cell Value is changed, display a message box..

Setting EnableEvents to FALSE makes those things not happen.


Calculation..
Whenever a value in a cell is changed, any formula that is dependant on that cell recalculates. There are some formulas like Indirect and Offset that recalculate ANY time something changes, even if that formula is not dependant on the Cell that changed.

So setting calculation to manual, makes formulas not update anymore.
So say you have a formula in A1 like =B1
And if you have calculation off, and you change B1's value, the formula in A1 will not update. You need to be carefull with this if your macros depend on the values of formulas AND your code changes the values of the cells those formulas use.

Hope this helps..

Thanks for the education and advice. Since my macros often include a need for these things to happen, it's probably best if I not use these two features. Can you offer some information Application.Screenupdating? I've already set this too false/true in many macros. However, I'm not clear about the meaning.