How to Time a Macro

Watching a macro run is like watching a kettle boil, it takes ages!  However if you time it often you’ll find it doesn’t take as long as you think, or it will give you a good benchmark to understand how processes you add / remove / alter affect runtime.

This code which you place around your macro code times the macro and displays a message box with the total elapsed time.

Sub Subname

Dim StartTime As Double
Dim MinutesElapsed As String
StartTime = Timer

....
Your Macro Code goes here
....

MinutesElapsed = Format((Timer - StartTime) / 86400, "hh:mm:ss")
MsgBox "This code ran successfully in " & MinutesElapsed & " minutes", vbInformation

End Sub

The Above code will start a timer at the beginning of your macro (StartTime), then place all your macro code in the middle, at the end of your macro it will pop up with a message box to say how many minutes and seconds it took for your code to run.

Not only is this a great way to show users that it may feel like a long time but it was “only 52 seconds” – The message box acts a sign that the macro has completed.

Add a Comment

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.