speed performance measure vba function

archer18

New Member
Joined
Apr 3, 2010
Messages
24
Hi all,

if i want to measure how long a cell take to recal, do you have a vba function that can use on a cell or a range to show me the time it takes to recal in 0.0000 of a sec for that particular cell or range. basically as accurate as possible 5 to 6 decimal sec will do. :)
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
According to this article: Improving Performance in Excel 2007
at the time Excel 2007 was introduced, the highest resolution timer had a resolution on the order of microseconds. You might want to use the MicroTimer function in the referenced article.
 
Upvote 0
I have been debating about whether readers would find this interesting or an annoying nitpick. Some feedback would be appreciated.

According to this article: Improving Performance in Excel 2007[.] at the time Excel 2007 was introduced, the highest resolution timer had a resolution on the order of microseconds. You might want to use the MicroTimer function in the referenced article.
That is incorrect; and that is not exactly what the article says.

The resolution of the performance counter is 1/freq, where "freq" is the counts-per-second returned by QueryPerformanceFrequency. (Actually, it is 1/(freq*10000), since freq is type Currency.)

That depends on the computer
[*], not the version of Office (although that might not have been JoeMo's intent). For example, it is under 280 nanoseconds (0.280 microseconds) on one of my computers, and it is under 480 nsec on another. Surprisingly, the latter is the newer and faster computer.


[*] By "computer", I mean the combination of hardware, BIOS and perhaps the version of Windows.


The article says, correctly, that the implementation of MicroTimer "can measure time intervals down to small numbers of microseconds". Call that accuracy. That is due, in part, to VBA overhead in the MicroTimer implementation.

My point about "resolution" v. "accuracy" is: assuming the timer overhead is constant (it isn't, especially for MicroTimer!), the difference between two timed intervals (e.g. two formulas) can be measured to within a fraction of a microsecond, not "small numbers of microseconds". It is limited by the resolution, not the accuracy.

That's really a nitpick. The important take-away is: QueryPerformanceCounter is indeed the tool of choice for high-resolution time measurement.

However, it should also be noted that a precision instrument is only as good as the person using it. There is much more to performance measurement than the precision of the data. It is as much art as it is science.
 
Upvote 0
I have been debating about whether readers would find this interesting and useful, or just annoying detail. Some feedback would be appreciated.

if i want to measure how long a cell take to recal, do you have a vba function that can use on a cell or a range to show me the time it takes to recal in 0.0000 of a sec for that particular cell or range. basically as accurate as possible 5 to 6 decimal sec will do.
JoeMo points to the use of QueryPerformanceCounter described in the MS article "Improving Performance" by Charles Williams (click here). That article contains a wealth of information that applies to isolating bottlenecks in workbook designs.

Many people try to apply the same methodology to measuring and comparing the performance of specific Excel functions and individual formula design.

If that is your purpose, beware that that road is fraught with pitfalls that I feel many people fall prey to, often reporting dubious numbers and sometimes reaching incorrect conclusions. The precision of QueryPerformanceCounter can be misleading. The interpretation of performance measurements is as much art as it is science.

Consider the following experiment (#1)....

In a new workbook, enter =RAND() into A1, and copy A1 into A2:A10000.

Then enter the following code and execute procedure "doit" to measure the performance of just the formula in A1. (The guts of the performance measure is highlighted in red.)
Code:
Option Explicit

Public Declare Function QueryPerformanceFrequency Lib "kernel32" _
    (ByRef freq As Currency) As Long
Public Declare Function QueryPerformanceCounter Lib "kernel32" _
    (ByRef cnt As Currency) As Long
    
Sub doit()
    Dim sc As Currency, ec As Currency, dt As Double
    Dim s As String, i As Long, n As Long
    Dim oldCalc, myRng As Range
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
        oldCalc = .Calculation
        .Calculation = xlCalculationManual
    End With
    [COLOR=#ff0000]Set myRng = Range("a1")[/COLOR]
    n = myRng.Count
    s = myRng.Address & "   " & Format(n, "#,##0")
    For i = 1 To 10[COLOR=#ff0000]
        sc = myTimer
        myRng.Calculate
        ec = myTimer
        dt = myElapsedTime(ec - sc)
        [/COLOR]s = s & vbNewLine & _
            "    " & Format(dt / n, "0.000\,000\,000") & " sec" & _
            "    " & Format(dt, "0.000\,000\,000") & " sec"
    Next
    With Application
        .EnableEvents = True
        .Calculation = oldCalc
        .ScreenUpdating = True
    End With
    MsgBox s
End Sub

Function myTimer() As Currency
    ' defer conversion to seconds until myElapsedTime
    QueryPerformanceCounter myTimer
End Function

Function myElapsedTime(dc As Currency) As Double  ' return seconds
    Static df As Double
    Dim freq As Currency
    If df = 0 Then QueryPerformanceFrequency freq: df = freq
    myElapsedTime = dc / df
End Function

On one computer, I see results like the following (YMMV):
Code:
$A$1   1
    0.003,482,007 sec    0.003,482,007 sec
    0.000,051,124 sec    0.000,051,124 sec
    0.000,045,537 sec    0.000,045,537 sec
    0.000,044,419 sec    0.000,044,419 sec
    0.000,044,419 sec    0.000,044,419 sec
    0.000,044,419 sec    0.000,044,419 sec
    0.000,044,140 sec    0.000,044,140 sec
    0.000,044,419 sec    0.000,044,419 sec
    0.000,044,698 sec    0.000,044,698 sec
    0.000,044,978 sec    0.000,044,978 sec

You might notice some oddities, to wit: all the measurements are somewhat different; and usually, the first measurement is significantly different. I will discuss these below.

For now, only note that the average is about 44.6 microseconds, excluding the first two measurements ("outliers"). Should we conclude that Excel RAND takes nearly 45 microseconds?(!)

Now, consider experiment #2....

Make the change indicated in red below, and re-execute procedure "doit" to measure the performance of all the formulas in A1:A10000.
Code:
    [COLOR=#ff0000]Set myRng = Range("a1", Cells(1, "a").End(xlDown))[/COLOR]
    n = myRng.Count
    s = myRng.Address & "   " & Format(n, "#,##0")
    For i = 1 To 10
        sc = myTimer
        myRng.Calculate
        ec = myTimer
        dt = myElapsedTime(ec - sc)
        s = s & vbNewLine & _
            "    " & Format(dt / n, "0.000\,000\,000") & " sec" & _
            "    " & Format(dt, "0.000\,000\,000") & " sec"
    Next

On my computer, I see the following results (YMMV):
Code:
$A$1:$A$10000   10,000
    0.000,001,289 sec    0.012,886,275 sec
    0.000,000,934 sec    0.009,343,646 sec
    0.000,000,955 sec    0.009,549,258 sec
    0.000,000,960 sec    0.009,600,941 sec
    0.000,000,996 sec    0.009,957,690 sec
    0.000,000,930 sec    0.009,296,712 sec
    0.000,000,950 sec    0.009,498,135 sec
    0.000,001,360 sec    0.013,601,170 sec
    0.000,000,945 sec    0.009,450,084 sec
    0.000,000,971 sec    0.009,712,966 sec

The numbers on the right are the total execution time for 10,000 formulas. The numbers of the left are the average per-formula execution time, by simply dividing the right by 10,000.

Again, let's overlook some interesting oddities for now.

Note only that excluding "outliers" (#1 and #8), the average per-formula time is about 0.9 microseconds, 1/50th of the time we measured for a single formula in experiment #1.

Did RAND get faster when we executed more of them?;) Of course not!

In fact, 0.9 microseconds is closer to the real execution time (on my computer).

We were fooled by the single-formula time of 45 microseconds because all of our attempts to measure performance incur overhead. In this case, significant overhead relative to the short interval we tried to measure in experiment #1.

The following is a brief and over-simplistic list of some sources of that overhead:
  • VBA late-compilation overhead the first time we execute a code path.
  • Overhead in interprocess communication between VBA and Excel threads.
  • System overhead in process management to switch between VBA and Excel threads, which might include paging in code and data.
  • Excel overhead in initiating the calculation.
  • Excel and system overhead in managing the workbook data space.
  • System overhead in processing interrupts, some of which stop all other processing, even on multi-core computers (notably system clock interrupts).
Each of these overhead factors is variable and unpredictable. This is a key source of execution time variations, especially the "outliers".

And since we incur all of these overhead factors each time we execute range.Calculate in VBA, we cannot reduce their effect simply by executing range.Calculate multiple times in a loop.

(On the other hand, the value in such a loop is to recognize and account for execute time variation.)

The important take-away is: usually, we cannot measure the performance of "a" formula simply by measuring one instance of the formula.

But that does depend on the nature of the formula and the situation that we are trying to measure. Sometimes we need to measure one instance of a formula, but increase the size of ranges that it references in order to overcome the effects of overhead.

(And sometimes, the Heisenberg Uncertainty Principle applies: changes we must make to improvement the accuracy of a performance measurement also changes circumstances and introduces new inaccuracies.)

I have only touched on the issue; this is only the tip of the iceberg. But I hope it is enough to recognize the potential effect of some performance measurement pitfalls, if not avoid them.
 
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,177
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top