How To Trap Cell Value Change Event so determine XL calc sequence?
 

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

How To Trap Cell Value Change Event so determine XL calc sequence?
 

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.vangelder.co.nz/excel


"Alex Lai" wrote in message
...
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

How To Trap Cell Value Change Event so determine XL calc sequence?
 

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.Decisionmodels.com/calcsecretsc.htm

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


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

"Alex Lai" wrote in message
...
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

How To Trap Cell Value Change Event so determine XL calc sequence?
 

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

"Alex Lai" wrote in message
...
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.
"Charles Williams" wrote in message
...
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.Decisionmodels.com/calcsecretsc.htm

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


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

"Alex Lai" wrote in message
...
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