Solved: Application.Calculation . . . Explain Please

[Cyberdude]

I guess I don?t fully understand ?Application.Calculation?. The default state is ?xlAutomatic? . . . what does this imply?

I thought that ?Application.Calculation = xlManual? says only recalculate those cells that are affected by the last change to any cell. ?xlAutomatic? means recalculate the whole sheet (or is it workbook??) each time any cell is altered.

Why would you want everything recalculated if the most recent change has no effect on most cells? It would seem that ?xlManual? should be the default. Obviously I don?t understand.

It just occurred to me that Excel might not know all the cells that are affected by a change. For example, the use of INDIRECT does not allow Excel to know that a formula in one cell is affected by the contents of the cell accessed indirectly.

Would someone tell me about those cases iin which ?xlAutomatic? must be used?

[Bob Phillips]

Application.calculation is setting the workbook calculation mode.

If it is manual, it means that Excel will not recaclulate at all if a cell value is changed, it will only recalculate if you force it, such as with Alt-F9.

Automatic means that it will recalculate whenever a cell value changes. This does snot mean that evry formula will recalculate, Excel is far smarter than that, and it maintains a dependency tree, so it only recalculates any cells that are affected by that cell change (and any cells affected by those affected cells, and so on).

INDIRECT is different. As you say, Excel cannot know what cell that an INDIRECT formula is dependent upon, so any cells with an INDIRECt are calculated every time anything changes (as long as clacultion mode is automatic). This is known as a volatile function. NOW() and TODAY() are two more volatile functions.

You should only raelly set calculation mode to manual if you need to do some batch porcessing and which will change cells, which will be slowed down considerably if calculation mode is automatic, and do not need recalculation within that process. And you should then set it back to automatic at the end.

[Cyberdude]

Thanx DL of VBAX . . . WOW, I was REALLY wrong about that one. I'm sure glad I asked the question.

I?m still a little confused about how it works. Suppose I use cell ?A1? as my manual input cell, and cell ?B1? as my processing cell. In cell ?B1? I have the formula: = 2 * A1
I start by putting a zero in cell ?A1?.
I set Calculate to be xlManual.
Now I enter into ?A1? the numeric value ?2?.
Next I enter into ?A1? the numeric value ?3?.
Finally, I set Calculate to be xlAutomatic. At this point, what will be the value in cell ?B1??

I will assume that ?A1? still has the value 3 in it, so it appears to me that ?B1? will have the calculated value 6 in it. If I?m correct, then B1 will have only 2 values: first a zero and then a 6. ?B1? will never compute the value 4, so the 2 that I input into ?A1? is in effect thrown away and not used as an input. Jeez, ya gotta be careful about using xlManual.

[Bob Phillips]

That is exactly what will happen.

Did you try it?

I did say at the end of my last response that setting to Manul has a specific, but limited, usage.

[Cyberdude]

Thanx again, DL! :friends: