Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How To Trap Cell Value Change Event so determine XL calc sequence?
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Trap show toolbar event | Links and Linking in Excel | |||
how can I trap the change event (Tab Press key) of these controls | Excel Worksheet Functions | |||
how can I trap the change event (Tab Press key) of these controls | Excel Worksheet Functions | |||
Trap CTRL+C keypress event | Excel Programming | |||
how to trap a event coming from a dll | Excel Programming |