Two Timers

The simplest way to check the execution time of VBA code is with the built in Timer function, but this returns a single precision value in seconds, resulting in a minimum time step of about 10 milliseconds.  For VBA code much better precision can be achieved with the Microtimer function, found at a Microsoft article by Charles Williams:

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

For use with Python code, called from VBA, the code below will call the Python time.clock function, allowing the VBA and Python code to be timed on the same basis:

Function xl_pytime()
Dim result As Variant, Res As Variant
Static methods As Variant

On Error GoTo fail:

    Set methods = PyModule("timesum", AddPath:=Path1)
    Set result = PyCall(methods, "xl_getpytime1", PyTuple())

    Res = PyVar(result)
    xl_pytime = Res
    Exit Function

fail:
    xl_pytime = Err.Description

End Function

Public Function Path1() As String
Dim pyPath1 As Variant
    If pyPath1 = vbNullString Then pyPath1 = ThisWorkbook.Path
    Path1 = pyPath1
End Function

The Python code is simply:

def xl_getpytime1():
    return time.clock()

These two timers are included in the spreadsheet Timers.xlsb, together with a routine to sum 7 sequences of integers from 1 to 10 up to 1 to 10,000,000. The results for the two timers, compared with the VBA Timer function are shown below.

Also included are times for the sum routine in Python, using pure Python code, and with the addition of the Numba just-in-time compiler for the sum loop. Note that the Python code is about 4 times slower than the VBA, but with the addition of the Numba compiler it is about 20 times faster.  Also note that the Python timer, when called from VBA, has a lag of about 0.5 milliseconds, so the Microtimer is preferable for code all in VBA.

TwoTimers

 

 

This entry was posted in Excel, Link to Python, VBA and tagged , , , , . Bookmark the permalink.

2 Responses to Two Timers

  1. Great work. This one is really good. Thanks for sharing.

    Like

  2. Pingback: Using Numba with Excel and pyxll … | Newton Excel Bach, not (just) an Excel Blog

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.