Likes:  0

# Thread: speed performance measure vba function

1. ## speed performance measure vba function

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.

2. ## Re: speed performance measure vba function

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.

3. ## Re: speed performance measure vba function

I have been debating about whether readers would find this interesting or an annoying nitpick. Some feedback would be appreciated.

Originally Posted by JoeMo
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.

4. ## Re: speed performance measure vba function

I have been debating about whether readers would find this interesting and useful, or just annoying detail. Some feedback would be appreciated.

Originally Posted by archer18
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
Set myRng = Range("a1")
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
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:
```    Set myRng = Range("a1", Cells(1, "a").End(xlDown))
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.
• 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.

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•