XL Calc Sequence - To Charles and ROb

[Alex Lai]

Hi Charles and ROb,

Thanks for your emails. My situation is simpler:
there are only 6 cells in the same worksheet: A1, A2, A3, B1, B2, B3
A1 has a value of 1
A2 has a value of 2
A3 = A1 + A2

B1 = A1+ 2
B2 has a value of 2
B3= B1+B2

Now I want to determine, by vba, in which order XL calcs: A3 first or B1
first?


Thanks,
Alex.

 

[Myrna Larson]

I *think* Stephen Bullen once told me that it calculates across, then down,
unless the formulas dictate a different order. If that's correct, B1 would be
calculated first.

But my question for you is, WHY do you want/need to know this???

 

[Myrna Larson]

PS: I don't think there's any way to determine this from a VBA macro.

 

[Rob van Gelder]

There is no event to capture which cell is calculating.
My reply to your first post was to include a dummy UDF within the formula,
one which returns 0.
The dummy UDF just outputs to the immediate window the address of the cell
which is currently calculating.

 

[Charles Williams]

Its not possible to prevent Excel passing uncalculated/empty cells to
functions: you have to handle this in the function itself:
for examples of how to do this see
http://www.DecisionModels.com/calcsecretsj.htm

You will also find a discussion of Excel's dynamic calculation sequence on
this website


Charles
______________________
Decision Models
The Excel Calculation Site.
www.DecisionModels.com

I think that the order cells are processed is causing empty arrays to be
passed into my home made functions. What order does Excel use? Does it
process B1 before or after A2?

I *think* Stephen Bullen once told me that it calculates across, then down,
unless the formulas dictate a different order. If that's correct, B1 would be
calculated first.

But my question for you is, WHY do you want/need to know this???