I am working on improving the speed of some of my spreadsheets.
Typically, when a change is made in these spreadsheets, some time is spend for:
- preparing the main data: calculating dependent cells with excel formulas, sometimes a lot of cells, few UDF
- the main calculations: calculating some rather time consuming User Defined Function in VBA
- analysing the results: calculating cells depending on the main results, a lot by Excel formulas, some by other UDF moderately time consuming
I have been able to log to the immediate window the time needed to execute the most time consuming UDF (main calculation).
However, this represents only a fraction of the total delay experienced by the end user (between 10% and 80%, say).
Till now, to measure the delay experienced by the end-user, I used my wrist watch, a paper and a pencil.
Would there be a way to measure the total time automatically, from the change event up to the end of the spreadsheet update?
I looked naïvely for events to do that.
The "change" event can be used to start the timer.
Unfortunately there is no "AfterCalculation" event.
Any idea about how I could do that?
Thanks
Bookmarks