Step 1: Search for the Right Windows API to solve a problem
There are numerous sites that provide sample VBA codes for creating millisecond resolution timers. In this tutorial, not only I will show the complete VBA codes that you can simply copy and use, I will describe the research process involved to create millisecond timers in VBA using Windows APIs so that you can deepen your understanding of using Windows APIs with VBA coding.
Our motivations to create a millisecond timers is to allow us to measure the execution time for a piece of VBA code. Ideally this timer should be able to measure up to millisecond resolutions. However, looking at native VBA and Excel time related functions they only provide up to seconds accuracy.
A web search on “timers using Windows API” eventually lead to this documentation on high resolution timers in Win32 APIs. Excellent! So we can use two Win32 APIs QueryPerformanceFrequency function to express the frequency (in counts per second) and the QueryPerformanceCounter function to retrieve the current value of the high-resolution performance counter.
So to measure time with milliseconds accuracy, we calculate this formula: (“End Performance Counter” – “Start Performance Counter” ) / Performance Frequency .
Step 2: How to Use a Windows API Function within Excel VBA
In a previous article I described how this is done (you might want to go read that before continuing on here). A quick search for “QueryPerformanceFrequency” and “QueryPerformanceCounter” in the text file “Win32API_PtrSafe.TXT” which is the list of Windows API function declarations for VBA yielded this:
' Performance counter API's
Declare PtrSafe Function QueryPerformanceCounter Lib "kernel32" Alias _
"QueryPerformanceCounter" (lpPerformanceCount As LARGE_INTEGER) As Long
Declare PtrSafe Function QueryPerformanceFrequency Lib "kernel32" Alias _
"QueryPerformanceFrequency" (lpFrequency As LARGE_INTEGER) As LongWhat is this LARGE_INTEGER ?
Notice that the arguments to both Windows APIs have the data type LARGE_INTEGER. If we use this declaration as it is, VBA will give us a compile error: “User-defined type not defined”. A quick web search on the phrase “Windows API LARGE_INTEGER” tells us that it “Represents a 64-bit signed integer value”. So we can change the declarations slightly to use the native VBA 64-bit integer data type Currency.
' Performance counter API's
Declare PtrSafe Function QueryPerformanceCounter Lib "kernel32" _
Alias "QueryPerformanceCounter" (lpPerformanceCount As Currency) As Long
Declare PtrSafe Function QueryPerformanceFrequency Lib "kernel32" _
Alias "QueryPerformanceFrequency" (lpFrequency As Currency) As LongThe Complete VBA Code for Millisecond Timer Using Windows APIs
So let’s put these 2 Windows API function declarations at the top of a code module and use it to measure elapsed time in seconds but with milliseconds accuracy.
You can download the Excel workbook with the millisecond timer VBA codes to save bit of time.
Option Explicit
' Performance counter API's
#If VBA7 And Win64 Then
'for 64-bit Excel
Declare PtrSafe Function QueryPerformanceCounter Lib "kernel32" (lpPerformanceCount As Currency) As Long
Declare PtrSafe Function QueryPerformanceFrequency Lib "kernel32" (lpFrequency As Currency) As Long
#Else
'for 32-bit Excel
Declare Function QueryPerformanceCounter Lib "kernel32" (lpPerformanceCount As Currency) As Long
Declare Function QueryPerformanceFrequency Lib "kernel32" (lpFrequency As Currency) As Long
#End If
Dim curStartCounter As Currency
Dim curEndCounter As Currency
'***********************************************************
'Purpose: Measure elapsed time in milliseconds
'***********************************************************
Sub MilliSecondTimer_Start()
Dim lgResult As Long
lgResult = QueryPerformanceCounter(curStartCounter)
End Sub
'***********************************************************
'Purpose: Measure elapsed time in milliseconds
'***********************************************************
Sub MilliSecondTimer_End()
Dim lgResult As Long
Dim curFrequency As Currency
lgResult = QueryPerformanceCounter(curEndCounter)
lgResult = QueryPerformanceFrequency(curFrequency)
Debug.Print "Elapsed time (ms): " & (curEndCounter - curStartCounter) / curFrequency
End SubHow to use the Excel VBA codes in your projects
I think it will benefit you further if I explained how to use the VBA codes in your own projects. I’m assuming you are an intermediate VBA coder and already have the codes copied and placed in a VBA code module.
'***********************************************************
'Purpose: Sample code to measure elapsed time in milliseconds
'***********************************************************
Sub TimeACode()
'measure start count
Call MilliSecondTimer_Start
'*****************************************
'Insert the code to measure elapsed time
'*****************************************
Dim lgCounter As Long
For lgCounter = 1 To 100000000
'do nothing
Next
'measure end count
Call MilliSecondTimer_End
End SubYou can start the millisecond timer with the line:
Call MilliSecondTimer_StartThis subroutine will store current value of the high-resolution performance counter in the module variable curStartCounter. From that point onward, you can write any VBA code that you wish to measure the elapsed time.
The milliseconds timer can be stopped with the line:
Call MilliSecondTimer_EndThe above subroutine will still store the current value of the high-resolution performance counter in the module variable curEndCounter. To determine the elapsed time, we calculate the number of elapsed ticks : curEndCounter – curEndCounter. We divide the number of elapsed ticks with curFrequency to obtain the elapsed time in seconds.
In my next tutorial, we will explore the fastest VBA codes to search for a cell within a large worksheet.
About Aeternus Consulting
Related Link: Aeternus Consulting Excel Training Courses Singapore
Aeternus Consulting is the premier training centre in Singapore for Excel Courses – Basic Excel, Advanced Excel and Excel VBA Macro courses. For more Microsoft Excel training courses, please visit our Excel training page.
Aeternus Consulting is now on Instagram! So take a look and follow us on Instagram.
One Response
According to Microsoft, Large_Integer is a signed 64-bit integer. Currency is also a signed 64-bit, but a fixed-point type. LongLong is a SIGNED 64-bit integer, which exactly fits description of Large_Integer.QuadPart property. I.e. you do not need “currency”, a workaround for 32-bit office on x64 systems. You can use LongLong directly.