This post may contain affiliate links. Please read my disclosure for more info:

How to create a calculation timer with VBA

Excel Create Calculation Timer VBA

webimagecalculationtimer

When you are trying to optimise your VBA code it can be useful to know how long a process takes.  It’s probably not desirable to sit at your desk with a stopwatch, so a VBA timer is a good idea.

This timer is so simple.  At the start, it uses the built-in Timer function, which records the number of seconds since midnight.  Once the code has carried out all its actions the Timer function is used again, and the difference between the starting and finishing times is displayed.

 

The Timer VBA Code

Sub VBARunTimer()

'Declare the variables to hold the Start Time and the Run Time
Dim StartTime As Double

'Set the start time time to the StartTime variable
'Timer is a VBA function which counts the number of seconds since midnight.
StartTime = Timer

'Insert the actions you wish to run here

'In this example timing how long a full recalculation of a workbook takes
Application.CalculateFull

'End of the code for the actions here

'Output the number of seconds it took to run in 3 decimal places.
MsgBox "Total time: " & Round(Timer - StartTime, 3) & " seconds"

End Sub

To use the code simply insert your actions into the section highlighted in the code.

 

The Timer VBA Code as a Function

Sometimes it is more convenient to use a Function at the start and end of your code, rather than a Sub which surrounds your code.  Here is the code changed into a Function.

To use the function just call it at the start and end of the code.  By declaring a public variable or multiple variables it is possible to have multiple timers running at the same time, which is great if you want to identify the slowest part of your code.  If you do have multiple timers running then a message box is probably not the best output option, as it requires the user to click “OK” before it will move to the next step in the process.

Function VBARunTimer(Optional StartTime As Double)

'If the start time has not been set then return the start time
If StartTime = 0 Then

    VBARunTimer = Timer
    'Exit the function as the remaining code is not required
    Exit Function

Else
    'The start time as now been fed back into the Function by the user,
    'so can calculate the run time.  Output as the number of seconds to
    '3 decimal places
    MsgBox "Total time: " & Round(Timer - StartTime, 3) & " seconds"

End If

End Function

To use this function.  You can include the following code at the start of your procedure.

Dim StartTime As Double
StartTime = VBARunTimer()

Then use the following code at the end of the procedure.

VBARunTimer (StartTime) 

 

Need to time calculation speed of formulas?  Check this out.

Save

Leave a Reply

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