Step 1: Search for the Right Windows API to solve a problem
There are numerous sites that provides 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. Great, so we can use 2 Windows 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 in order to measure time in milliseconds. The basic logic is to determine this logic: (“End Performance Counter” value – “Start Performance Counter” value / Performance Frequency value).
Step 2: How to Create the Windows API Function Declaration for 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:
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.
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 milliseconds.
In my next tutorial we will explore what kind of VBA codes can help to speed up execution times.
Choose from our Most Popular Courses
Basic VBA Programming Course with Microsoft Excel. Designed for non VBA practitioners. This is a short & easy-to-follow workshop to learn how to build VBA programs from scratch in Microsoft Excel.
Useful Resource: Get Microsoft Excel for free here.