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/

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

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

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

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

1. XL how to change a cell reference in a formula to variable value?

2. How to trap the change of color event in a cell?

3. Cell value change to trigger macro (worksheet change event?)

4. determine if a cell changed value

5. Formula for determining negative value change between cells

6. change cell value greater than another cell value using formulas

7. auto calc on, but have to edit (f2) cells to force re-calc..he

8. cell value to populate based on changing value in another cell

9. How to change value of cell based on another value of another cell

10. change value in a cell according to another cells value

11. how to change the value of some cells, if one specific cell value is there.

12. Change cell value based on other cell value

13. How to lock a cell value when values going into that cell keep changing...

14. Alter one cell value, in case another cell's value changed

15. Change Cell Value and Subtract the cell value in another sheet

5 post • Page:**1** of **1**