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.
Need to time calculation speed of formulas? Check this out.