Improving Calculations with the Excel Dependency Tree/Table

[MachAngle]

We have a large 5MB Excel Workbook consisting of 58 Worksheets and many VBA modules. We use many methods of improving the performance of this worksbook like:
1.) Empty Excel Temporary Directory
2.) Control Calculations on non-essential worksheets
3.) Optimize sequence of worksheets to minimize backward references
4.) Compile VBA into DLLs for computational efficiency
5.) Disable Excel's screen updates until computations are done

QUESTION: Does anybody know how to visualize the Excel Dependency Tree used by the calculation engine? We would like to look at this to determine if there are other efficiencies/errors we can fix to make the workbook more responsive to the users interaction.

 

[Charles Williams]

You can use the Auditing toolbar to make the dpendency tree visible cell by
cell. The problem with any visualisation of the dependency tree in a large
woorkbook is that there are too many branches/lines to be able to see
anything.

Why dont you analyse the workbook calculation time to find out where the
bottlenecks are?

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

We have a large 5MB Excel Workbook consisting of 58 Worksheets and many

VBA modules. We use many methods of improving the performance of this
worksbook like:

1.) Empty Excel Temporary Directory
2.) Control Calculations on non-essential worksheets
3.) Optimize sequence of worksheets to minimize backward references
4.) Compile VBA into DLLs for computational efficiency
5.) Disable Excel's screen updates until computations are done

QUESTION: Does anybody know how to visualize the Excel Dependency Tree

used by the calculation engine? We would like to look at this to determine
if there are other efficiencies/errors we can fix to make the workbook more
responsive to the users interaction.