[VBA] How to Measure Macro Execution Time (Timer Function)

Introduction

Have you ever wanted to know exactly how long it takes for your VBA macro to finish its process? It is very useful to compare performance with numbers, especially when handling large amounts of data or improving your code.

VBA provides a function called Timer. By using this, you can easily measure the time it takes to execute a macro.

This article explains the basic method to measure the time taken for a specific process in seconds using the Timer function.

VBA Sample Code to Measure Execution Time

This macro measures the processing time in the following steps:

1. Save the value of the Timer function as the “Start Time” in a variable right before starting the process.
2. Execute the main process you want to measure.
3. Get the value of the Timer function again right after the process completes and calculate the difference from the “Start Time”.

Complete Code

' マクロの実行時間を計測する
Sub MeasureExecutionTime()
    
    '--- 1. 開始時刻を記録 ---
    Dim startTime As Single
    startTime = Timer
    
    '--- 2. 時間を計測したい処理 ---
    ' (例: 大量のセルに値を書き込む処理)
    Dim i As Long
    For i = 1 To 50000
        Cells(i, "A").Value = i
    Next i
    
    '--- 3. 終了時刻との差分を計算して表示 ---
    Dim endTime As Single
    Dim elapsedTime As Single
    
    endTime = Timer
    elapsedTime = endTime - startTime
    
    MsgBox "処理が完了しました。" & vbCrLf & vbCrLf & _
           "処理時間: " & elapsedTime & " 秒", vbInformation, "処理時間計測"

End Sub

Explanation of the Code

What is the Timer Function?

The Timer function is a built-in VBA function that returns the number of seconds elapsed since midnight (00:00:00) as a Single (single-precision floating-point number) data type.

For example, if you run Timer exactly at 9:00 AM, it returns the value 32400 (9 hours × 3600 seconds).

startTime = Timer

This stores the seconds elapsed since midnight (e.g., 32400.50 seconds) into the variable startTime immediately before the process begins.

elapsedTime = endTime - startTime

This subtracts the startTime from the seconds elapsed immediately after the process finishes (e.g., 32402.75 seconds). The difference is exactly the time taken for the process (in this example, 2.25 seconds).

Notes

The Timer function resets at midnight. If there is a possibility that your macro execution will cross over midnight, you need to use the Now() function to record the date as well for calculation.

However, since most VBA macros finish within a few minutes, the Timer function is usually sufficient.

Summary

In this article, I explained a simple method to measure macro execution time using the VBA Timer function.

1. Record the Timer value before the process.
2. Record the Timer value after the process.
3. Calculate the difference to get the execution time.

With this simple technique, you can check the effect of code performance improvements using objective numbers. Please try using this measurement method when improving processes that take a long time.