excel calculations

[Jacques]

Is there a way to have a window which acts like VB
debugger, but shows each step that excel is performing.
As in the case of circular reference calculations, one
could watch in the order at which excel computes each
cell. So if you have Cells A1, A2, A3, A4, A5, and A1 is
used to compute A2-5, and A2 is used to calculate A1, A3-
5, and so forth through A5 used to calculate A1-4. I have
tried to repeat the calculation long hand 10 different
ways and can not come up with the same information. It
would be beneficial to see how excel starts each and
completes each computation and which order. It could be
used to write more indepth macro's and such.

 

[Peter T]

Someone may come up with a better idea but perhaps you
could try something like this:

Create a User Defined Function (Alt F11, insert a module):

'at the top of the module
Public counter as long

Function myDebug(v As Range) As Long
counter = counter + 1
myDebug = counter
Debug.Print v.Address, Application.Caller.Address

End Function

For each cell you want to check a calculation, enter
following (obviously change A1 as required).

=myDebug(A1)

Do your calculations (Ctrl-Alt-F9). Go back to the VBE
(Alt F11) and press Ctrl G to open the Immediate Window.
Hopefully you will see the order cells were calculated.
Not sure it will calculate if you have circular references.

Press the square reset button to reset the counter.

I don't think the extra UDF's will change the original
order of calculation, but I'm not 100% sure about that.

Regards,
Peter

 

[Charles Williams]

for circular reference calculations Excel calculates left to right, and then
top-to-bottom (except for multi-cell array formulae which are treated as a
separate block).

You can use the CalcTrace functions from my downloads page to trace
calculation sequence.


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