[VBA] High-Precision Time Measurement in Milliseconds (GetTickCount API)

Introduction

The VBA Timer function is useful for easily measuring processing time in seconds. However, if you want to compare the performance of processes that complete very quickly, the Timer function’s precision is often insufficient.

For such high-precision time measurement, the Windows API GetTickCount function is ideal. GetTickCount returns the elapsed time since the PC started in milliseconds (1/1000 of a second). This allows you to accurately measure the speed of processes that finish in a very short time.

In this article, I will explain how to measure macro processing time in milliseconds using the GetTickCount API.

VBA Sample Code: Measuring Time in Milliseconds

The Declare statement must be written at the very top of the module (before any procedures like Sub).

'--- Declare the API function at the top of the module ---

' Compatible with both 64-bit and 32-bit
#If VBA7 Then
    ' API function to get time elapsed since PC startup in milliseconds
    Private Declare PtrSafe Function GetTickCount Lib "kernel32" () As LongLong
#Else
    Private Declare Function GetTickCount Lib "kernel32" () As Long
#End If


' Measure processing time in milliseconds (ms)
Sub MeasureTimeInMilliseconds()
    
    Dim startTime As LongLong
    Dim endTime As LongLong
    Dim elapsedTime As LongLong
    
    '--- 1. Record time before processing ---
    startTime = GetTickCount()
    
    '--- 2. The process to measure ---
    ' (Example: 100,000 loop iterations)
    Dim i As Long
    For i = 1 To 100000
        ' Some light processing
    Next i
    
    '--- 3. Record time after processing and calculate the difference ---
    endTime = GetTickCount()
    elapsedTime = endTime - startTime
    
    '--- 4. Display the result ---
    MsgBox "Process complete." & vbCrLf & vbCrLf & _
           "Elapsed Time: " & elapsedTime & " ms", vbInformation, "Time Measurement"
           
End Sub

Explanation of the Code

Declare PtrSafe Function GetTickCount …

This is a declaration to make the Windows API function GetTickCount available in VBA.

• As LongLong: In a 64-bit environment (VBA7), the value returned by GetTickCount can potentially be very large, so it is safer to receive it as a LongLong type. In a 32-bit environment, Long is sufficient.

startTime = GetTickCount()

This stores the number of milliseconds elapsed since the PC started into the variable startTime immediately before the process begins.

elapsedTime = endTime – startTime

Subtract the startTime (recorded before the process) from the endTime (recorded immediately after). The difference is the time taken for the process in milliseconds.

Difference from the Timer Function

1. Precision: Timer measures in seconds (with decimals), while GetTickCount measures in milliseconds (integers). For short processes, GetTickCount is overwhelmingly more precise.
2. Reset: Timer resets at midnight (00:00:00). GetTickCount resets (returns to 0) approximately 49.7 days after the PC starts. This rarely causes issues for standard VBA macro measurements.

Summary

In this article, I explained how to measure macro processing time with high precision using the Windows API GetTickCount.

1. Declare the GetTickCount API.
2. Record the GetTickCount value before the process.
3. Record the GetTickCount value after the process.
4. Calculate the difference to get the processing time in milliseconds.

This technique is very useful when strict speed measurement is required, such as when analyzing how slight changes in code affect performance.