Microsoft Project VBA: How to time your code

Posted: June 28, 2017 | Author: | Filed under: Microsoft Project VBA, MS Project 2010, MS Project VBA, MSP VBA | Tags: , , , | Leave a comment

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:

mySimpleTimer

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:

mySimpleTimerWindow1

mySimpleTimerWindow2

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:

calculateTimeExample

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:

calculateTimeExample1

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!

Microsoft Project VBA: Option Explicit vs Implicit

Posted: June 26, 2017 | Author: | Filed under: Microsoft Project VBA, MS Project 2010, MS Project VBA, MSP VBA | Leave a comment

After coding quite a while in Microsoft Project using VBA I’ve noticed that many people don’t use Option Explicit at the top of the modules they are creating code in Microsoft Project.  I see it more often used when I see Microsoft Excel code.  It’s important to understand that using Option Explicit is beneficial when coding.

When writing VBA the default is to read variables implicitly.  This means that if you want to create new variables you can create them on the fly without having to declare them.  That sounds wonderful until you misspell your variable and VBA reads it as a new variable.  Therefore, I find it more useful to declare in your code that you will explicitly declare each of your variables and if they are not explicitly declared, then you will automatically get an error.

To better understand this issue let’s start by looking at some simple code.  Say you have the following code with only one variable myNum.  You find yourself typing really fast and misspell myNum and write mNum:

Sub explicitTest()
Dim myNum As Integer

myNum = 25

ActiveProject.Tasks(1).Text1 = myNum

mNum = 80

ActiveProject.Tasks(1).Text1 = mNum

End Sub

Result:

codeSnippetText1

You run the code and there is nothing to tell you that now you have introduced a new variable without even knowing.  What if you make the same mistake later in the code and try to use mNum and you get in return the wrong number?  It becomes difficult to trace this type of error back to where it was introduced.

Here is where Option Explicit is very important.  You try the same code but with Option Explicit at the top of the module.

Option Explicit

Sub explicitTest()
Dim myNum As Integer

myNum = 25

ActiveProject.Tasks(1).Text1 = myNum

mNum = 80

ActiveProject.Tasks(2).Text1 = mNum

End Sub

Result:

errorVariableNotdefined

The error was caught as soon as the code was run.  Since it wasn’t defined as a Dim you will get an error.  This is good because you will avoid introducing potential bugs into your code that are hard to trace.  If you did mean to introduce a new variable, then when getting this error you will know you need to go back and declare this new variable before continuing.  This gives you greater control of everything happening within your code.

You can automatically add it to all your new code.  Remember, this will NOT add it to your existing code, you will have to make sure to add it manually at the top.

To add it automatically to your code every time you are start new code you can do the following:

  • Make sure you are in the Visual Basic Editor (VBE)
  • Go to Tools > Options
  • Once the Options pop-up window comes up check off the “Require Variable Declaration”

optionsExplicit.png

  • Click Okay

There!  you will not have to worry about adding this to all your new code.