Best Milliseconds Timer in VBA Tutorial

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.

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 Long

What 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 Long

The 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 Sub

How 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 Sub

You can start the millisecond timer with the line:

    Call MilliSecondTimer_Start

This 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_End

The 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 : curEndCountercurEndCounter. 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

Aeternus Consulting is the premier provider of analyst-grade Excel training courses in Singapore. Our courses are conducted by trainers with 20 yrs of industry and teaching experience.

Related Link: Aeternus Consulting Excel Training Courses Singapore

Leave a Reply

Your email address will not be published. Required fields are marked *

Wow! You made it this far?

It must be something important! Drop us a message, we’ll get back to Earth ASAP.
We are a Microsoft Office consultancy & digital creative agency. We specialise in Microsoft Office training courses, VBA application development, Business modeling in Excel, PowerPoint presentation designs, infographic designs, data visualizations and web UX UI.

Search Keywords

Excel Training Course Singapore, PowerPoint Training Course Singapore, Excel VBA Training Course.

Our Location

Location: 182 Cecil St,
Level 17 Frasers Tower,
Singapore 069547.