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.
Great work. This one is really good. Thanks for sharing.
Pingback: Using Numba with Excel and pyxll … | Newton Excel Bach, not (just) an Excel Blog