PDA

View Full Version : Time how long a formula calculate vba



Shazam
08-22-2006, 05:25 PM
Hi everyone,


Time to time I use formulas and some formulas I use are very fast some are a bit slow. My question is has anyone developed a code that will time how long a formula takes to calculate down to the milliseconds? This will be very useful for me to see what suite best for the application.


Example:

Enter =SUM(A1:A10000) in cell A1 and hit enter.

Then the code will place 0:00:03 in cell B1.

malik641
08-22-2006, 08:16 PM
Hey Shazam

This will calculate the cell (or cells) that have just been changed (in your example, A1):

Place this in the ThisWorkbook Module and set calculation to manual.

Option Explicit

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim Start As Double, Finish As Double
Start = Timer
Worksheets(Sh.Name).Range(Target.Address).Calculate

Finish = Timer

MsgBox "Time to calculate was: " & (Finish - Start) & " seconds."
End Sub
It goes further than Milliseconds, because it's a double and the sum calculation is very fast. I had to use A1=Sum($2:$65536) and I used 50,000 rows and ended up with ~0.489 seconds.

I used the Range.Calculate method because you wanted the time for the one cell (although this will work with a multiple amount of cells if wanted).

Hope this is what you were looking for! :thumb

P.S. KB pending :)

mdmackillop
08-23-2006, 03:54 AM
No need for the Finish:
MsgBox "Time to calculate was: " & (Timer - Start) & " seconds."

Bob Phillips
08-23-2006, 06:42 AM
http://www.decisionmodels.com/fxlfaq.htm

Shazam
08-23-2006, 06:49 AM
Hi malik641,

I used your code below and I get 0 seconds.


Option Explicit

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim Start As Double, Finish As Double
Start = Timer
Worksheets(Sh.Name).Range(Target.Address).Calculate

Finish = Timer

MsgBox "Time to calculate was: " & (Finish - Start) & " seconds."
End Sub





So I took mdmackillop advice.



No need for the Finish:
MsgBox "Time to calculate was: " & (Timer - Start) & " seconds."



But I get different results.

I have this formula in cell E51

=LOOKUP(2,1/(('S:\Lab Data\[2006.xls]Screen Analysis'!$A$2:$A$5000=C3)*('S:\Lab Data\[2006.xls]Screen Analysis'!
$C$3:$C$5001=2)),'S:\Lab Data\[2006.xls]Screen Analysis'!$F$5:$F$5003)


Now when I double click in that cell I count to myself being 14 seconds.


But the code gives me.

8.59374998981366E-04 seconds

Now when I double click in the cell again I get:

-3.7499999962165E-04

and again I get:

-2.18749999476131E-04


Any way to solve this?

Shazam
12-09-2006, 09:57 PM
Hi everyone,



This is a old problem and I'm still trying to solve. The question is if I double click anywhere on the worksheet that has a formula I would like a message box appear with the time how long a formula takes to calculate.


Here is the link I found but I dont know how to modify to suite my needs.

http://msdn2.microsoft.com/en-us/library/aa730921.aspx


Here is the code.



Private Declare Function getFrequency Lib "kernel32" _
Alias "QueryPerformanceFrequency" (cyFrequency As Currency) As Long
Private Declare Function getTickCount Lib "kernel32" _
Alias "QueryPerformanceCounter" (cyTickCount As Currency) As Long
'
Function MicroTimer() As Double
'
' Returns seconds.
'
Dim cyTicks1 As Currency
Static cyFrequency As Currency
'
MicroTimer = 0
' Get frequency.
If cyFrequency = 0 Then getFrequency cyFrequency
' Get ticks.
getTickCount cyTicks1
' Seconds
If cyFrequency Then MicroTimer = cyTicks1 / cyFrequency
End Function
Sub RangeTimer()
DoCalcTimer 1
End Sub
Sub SheetTimer()
DoCalcTimer 2
End Sub
Sub RecalcTimer()
DoCalcTimer 3
End Sub
Sub FullcalcTimer()
DoCalcTimer 4
End Sub
Sub DoCalcTimer(jMethod As Long)
Dim dTime As Double
Dim dOvhd As Double
Dim oRng As Range
Dim oCell As Range
Dim oArrRange As Range
Dim sCalcType As String
Dim lCalcSave As Long
Dim bIterSave As Boolean
'
On Error GoTo Errhandl
' Initialize
dTime = MicroTimer
' Save calculation settings.
lCalcSave = Application.Calculation
bIterSave = Application.Iteration
If Application.Calculation <> xlCalculationManual Then
Application.Calculation = xlCalculationManual
End If
Select Case jMethod
Case 1
' Switch off iteration.
If Application.Iteration <> False Then
Application.Iteration = False
End If

' Max is used range.
If Selection.Count > 1000 Then
Set oRng = Intersect(Selection, Selection.Parent.UsedRange)
Else
Set oRng = Selection
End If
' Include array cells outside selection.
For Each oCell In oRng
If oCell.HasArray Then
If oArrRange Is Nothing Then
Set oArrRange = oCell.CurrentArray
End If
If Intersect(oCell, oArrRange) Is Nothing Then
Set oArrRange = oCell.CurrentArray
Set oRng = Union(oRng, oArrRange)
End If
End If
Next oCell
sCalcType = "Calculate " & CStr(oRng.Count) & _
" Cell(s) in Selected Range: "
Case 2
sCalcType = "Recalculate Sheet " & ActiveSheet.Name & ": "
Case 3
sCalcType = "Recalculate open workbooks: "
Case 4
sCalcType = "Full Calculate open workbooks: "
End Select
' Get start time.
dTime = MicroTimer
Select Case jMethod
Case 1
If Val(Application.Version) >= 12 Then
oRng.CalculateRowMajorOrder
Else
oRng.Calculate
End If
Case 2
ActiveSheet.Calculate
Case 3
Application.Calculate
Case 4
Application.CalculateFull
End Select
' Calc duration.
dTime = MicroTimer - dTime
On Error GoTo 0
dTime = Round(dTime, 5)
MsgBox sCalcType & " " & CStr(dTime) & " Seconds", _
vbOKOnly + vbInformation, "CalcTimer"
Finish:
' Restore calculation settings.
If Application.Calculation <> lCalcSave Then
Application.Calculation = lCalcSave
End If
If Application.Iteration <> bIterSave Then
Application.Calculation = bIterSave
End If
Exit Sub
Errhandl:
On Error GoTo 0
MsgBox "Unable to Calculate " & sCalcType, _
vbOKOnly + vbCritical, "CalcTimer"
GoTo Finish
End Sub

johnske
12-10-2006, 12:03 AM
Hi,

The msdn article makes an interesting read. If you follow the link to the authors site (which xld has also provided) you'll see he has the timer code you're using above available as a free add-in download at http://www.decisionmodels.com/downloads.htm , there is also an explanation there of how to use this, I'll copy this below for you... :)



High Resolution Timers: Timers.ZIP

This Add-In contains VBA function wrappers for Windows API high resolution timers. These functions are useful when you need greater accuracy than is available from the VBA Timer (which turns out to be most of the time). VBA Timer is also quite slow to execute.

Portions of the code in these functions are from Ken Getz and John Green.

Mostly I use the code in these functions as timers within VBA, but you can also use them as UDF's.

To use as a timer, call the function, execute the thing you want to time, call the function again and subtract the second time from the first.

The XLA password is dm
The functions measure elapsed time rather than processor time.
The two functions are:

Microtimer: returns a Double containing seconds. Resolution about 1 microsecond and takes about 5 microseconds to execute in Win ME at 1200MHZ

Millitimer: returns a Long containing milliseconds. Resolution about 1 millisecond and takes about 5 microseconds to execute in Win ME at 1200MHZ

johnske
12-10-2006, 12:42 AM
Here's a basic example
Sub TimeMe()
Dim Start As Double, Finish As Double

Start = MicroTimer

UserForm1.Show
Unload UserForm1

Finish = MicroTimer

MsgBox (MicroTimer - Start)
MsgBox (Finish - Start)

End Sub
Notice I've given two msg boxes here, the 1st uses Malcolms example, the 2nd uses the variable Finish.

Note that the 1st may give a very small time difference (i.e. it usually takes a whisker longer - which I'd put down to the time taken to execute the "Show the MsgBox" part of it) but this difference is really very very small, as processes operating in the background can make substantially larger differences than that. :)

Bob Phillips
12-10-2006, 03:50 AM
Shaz,

The code works fine as presented.

What you have to do run the FullCalc or SheetCalc macros, or select a range of cells and then run RangeCalc.

There are two things to note here though.

Firstly, the times to recalculate a single formula, or even a small range of formulae, is so small it is very difficult to draw any conclusions from it. Even if you recalcualte a whole sheet, it is either very fast, which tells you nothing, or it is very slow, and where is the problem. What I tend to do is to target a particular formula, and then increase the amount of data it works upon, and replicate the formula hundreds or thousands of times, and then time the larger range. If it seems too long, I then try alternatives and see if they are quicker.

The other thing is that you have to run the timings repeatedly, and take an average. A single once-off run can be skewed by so many other factors.

And finally, you could always buy FastExcel, it is very useful if you do this a lot.

Shazam
12-10-2006, 07:06 PM
Thank You Johnske and xld!