Microsoft Project VBA: How to time your code

One of the most common questions I get is “How do you time your code?”  There are many ways to time your code.  Here I will go over some simple ways to time it.

The first way I will show you is to just get the start time and the end time.  You can add this code to different part of your code to see how long each section, sub, or function took to run.  This is pretty handy especially when you have very complex code.

For this example make sure you have your “Immediate” window open:

• Make sure you are in Visual Basic Editor (VBE) – CTRL + F11
• Navigate View > Immediate Window or CTRL + G

Using the code below and Debug.Print will print into your immediate window:

     Option Explicit    

     Sub mySimpleTimer()

     Debug.Print “mySimpleTimer Start: ” & Time

     ‘**********
     ‘ Insert your code here
     ‘**********

     Debug.Print “mySimpleTimer Finish: ” & Time

     End Sub

Results:

Since I don’t really have any code in there other than my timer, I get the same time for my start and my finish.  With complex code you should see a difference between your start and finish times.

If you prefer to get a pop-up message you can easily modify this code to use a message box instead of the immediate window.  Here is the code:

Option Explicit

Sub mySimpleTimerWindow()

MsgBox (“mySimpleTimer Start: ” & Time)

‘**********
‘ Insert your code here
‘**********

MsgBox (“mySimpleTimer Finish: ” & Time)

End Sub

Results:

Yes, you get it twice.  Once when you start the code and a second time when the code finishes.  This might be exactly what you want.  But maybe you would like to know the total time it took to run the code between start and finish time.  Here is where the next code comes in.

Note: I will continue using the debug.print from here on out for demonstration purposes.

This code is a little more complicated.  It gets you total number of seconds, minutes, and hours.  You can easily add code to figure out number of days if needed, but is not part of this code.

I use the mod operator to find remainders.

Option Explicit
Sub calculateTime()
Dim startTime As Double
Dim finishTime As Double
Dim totalTime As Double
Dim totalSeconds As Double
Dim totalMinutes As Double
Dim totalHours As Double

startTime = Now()

‘**********
‘ Insert your code here
‘**********

finishTime = Now()

totalTime = finishTime – startTime

totalTime = totalTime / 60

If totalTime < 60 Then
     totalSeconds = Round(totalTime)
ElseIf totalTime < 60 * 60 Then
     totalMinutes = Round(totalTime / 60)
totalSeconds = totalTime Mod 60
Else
     totalHours = Round(totalTime / 60 / 60)
     totalMinutes = Round((totalTime / 60) – (totalHours * 60))
     totalSeconds = totalTime Mod 60
End If

Debug.Print ” ————–“
Debug.Print “Hours: ” & totalHours
Debug.Print “Minutes: ” & totalMinutes
Debug.Print “Seconds: ” & totalSeconds

End Sub

Results:

Since I am not really running any code other than timing the code goes really fast and doesn’t take any time at all (in seconds).

If I force total time to be say totalTime = 225000 milliseconds (which is what the computer calculates), the result is the following:

So let’s calculate it manually to see if the code is working:

totalHours = 225000 / 60 = 3750 seconds then

totalHours = 3750 / 60 =  62.5 minutes

That means that 225000 milliseconds is 1 hour (60 minutes) and 2.5 minutes (62.5 – 60).  We can further break it down to 1 hour, 2 minutes, and 30 seconds (0.5 * 60).  You might try to test it a few times to make sure that this code checks out for you.

I say we have been successful. Another fun day using VBA!

---