measuring time between Change and Sheet Updated

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

lalbatros,

Maybe I am thinking a little to simply here, but could you not put your stopping time at the end of the macro that does the last calculation?

In other words you have a variable that captures the start time which you have indicated you have put in the change event. Have another variable that captures the time at the end of the last calculation. Then calculate the difference between the two values. You could even have a separate sheet that would keep a record of the start, stop, and difference.

lalbatros,

Here is the type of thing I have used in the past where you simply record a start time and an end time. Of course you can capture the time in as many places as you would like and as long as you have your record sheet (tab you record your times) set to a variable as I have below I don't believe it will slow things down much. You can have the different times stored in different columns so you will know what triggered the recording. In other words, at the end of each function you record a time and each function is assigned a column.

Please Login or Register  to view this content.

Again, this may be far simpler than what you are looking for. If you are looking for micro or nano seconds, this is not going to get it and I really don't know what will, but that does not mean it does not exist. I'm sure there are some scientific things within Excel which you can probably turn on, but unfortunately I'm not a scientist so I don't use that type of thing.

If this is not what you are looking for, you may need to repost your question to get some more folks to look at it.