calculate processing time

[ammartino44]

Hello. How do I calculate processing time in excel to compare which approach is more effecient?

[Marc L]

Hi !

As here it's a VBA forum, use VBA Timer function …

[joeu2004]

How do I calculate processing time in excel to compare which approach is more effecient?

There are two common approaches; see below.

But they only solve the problem of physically measuring time. The greater problem is how to interpret the results.

Beware of the situation where much of what you measure is VBA and system overhead and the overhead of the communication between the VBA and Excel threads.

-----

If the operations take several seconds and you don't mind that time is measured in increments of 15.625 millisecond (albeit accurate to the microsecond), the following is the simplest paradigm.

Please Login or Register  to view this content.

"Whatever you want to measure" might be Range(...).Calculate or the equivalent. You should decide whether you want to do disable ScreenUpdating and EnableEvents and to enable Manual calculation, or if that overhead is part of what you want to measure.

Note: st and et are type Single because Timer is type Single. We use type Single instead of type Double arithmetic in order to minimize arithmetic side-effects that creates the illusion that the elapsed time is not be a multiple of 15.625 msec.

If the operations take less than several seconds or you want finer resolution (which varies from computer to computer; about 297 nanoseconds on mine), you could use the following paradigm.

Please Login or Register  to view this content.

AFAIK, neither approach works on a Mac.

[ammartino44]

What do you mean between the vba and the excel threads? I'm just trying to measure how long a calculation takes. For example, using one type of formula (for a huge data set) vs. using a different type of formula.

[shg]

There's a workbook that times formulas at https://app.box.com/s/b5ykwwa9x6xs8d7v9vhv