Developer Forum

Hi ALL,

I am trying to write a procedure that can trap the cell value change event
so I can prorammatically determine in what sequence Excel calculates cells
in a worksheet: A1, A2, A3....B1, B2, B3..


or A1 B1, C1...A2, B2, C2?

Any ideas? thanks

Alex

XXXX@XXXXX.COM

You could do something like:

Test sub for processing existing formula, XX outputs current cell being
calculated

Sub test()
Dim rng As Range

For Each rng In ActiveSheet.UsedRange
If rng.HasArray Then
'process an array formula
ElseIf rng.HasFormula Then
rng.Formula = rng.Formula & " + XX()"
End If
Next
End Sub

Public Function XX() As Long
Debug.Print Application.Caller.Address
XX = 0
End Function


--
Rob van Gelder - http://www.yqcomputer.com/

Hi Alex,

Neither by row or by column: its mostly dependency sequence within last
evaluation sequence.
The sequence is actually dynamically determined by Excel as a function of
the changes since the last recalculation and the calculation method being
used.

for an explanation of excel's calculation sequence see
http://www.yqcomputer.com/

for useful functions for tracing the calculations sequence download
CalcTrace.zip from
http://www.yqcomputer.com/


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

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.




event
cells

You can use the CalcSeqCountRef() function in the down load to determine the
calculation sequence.
You can look at the code in the function (its very simple) and use debug to
trace its execution.

But the answer is indeterminate because it depends on which cell was last
changed (and which calculation method you are using, rangecalc, recalc or
fullcalc).
In other words I can make Excel calculate either A3 or B1 first depending on
what I do.
Is there a practical reason why it matters to you?

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



B1


of
being