Simple VBA code for Timing Excel Macros

Every now and then you may want to know how long an Excel macro is taking to run. You may even want to time portions of code to see if they are particularly slow.

We use a simple bit of VBA code that shows you the time in a pop up box once it’s finished…

Timing_Macros_1

The code is below…

Enter the START TIMER code when you want to start the timer

'-----START TIMER-----
Dim StartTime As Double
Dim TimeTaken As String
StartTime = timer

Then all your code will go here…

Then add the END TIMER code when you want to show how long it’s taken.

'------ END TIMER------
TimeTaken = Format((timer - StartTime) / 86400, "hh:mm:ss")

MsgBox "Running time was " & TimeTaken & " (hours, minutes, seconds)"

Obviously if you want to time the whole macro you should add the START TIMER code straight after your opening Sub NameOfMacro() statement and then add the END TIMER code before your closing End Sub statement.

No ratings yet.

Please rate this