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.
