Microsoft Project VBA: How to time your code
Posted: June 28, 2017 Filed under: Microsoft Project VBA, MS Project 2010, MS Project VBA, MSP VBA | Tags: Macro timing, time code, time macro, vba code timing Leave a commentOne 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!
Microsoft Project VBA: Option Explicit vs Implicit
Posted: June 26, 2017 Filed under: Microsoft Project VBA, MS Project 2010, MS Project VBA, MSP VBA Leave a commentAfter 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:
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:
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”
- Click Okay
There! you will not have to worry about adding this to all your new code.
Microsoft Project VBA: How to Store Your Code by Importing and Exporting Modules
Posted: June 21, 2017 Filed under: MS Project 2010, MS Project VBA, Program Management Leave a commentI frequently get questions on how I store my VBA code so I don’t accidentally lose it when the Microsoft Project client decides to quit on me. This is a good question since I have completely lost all my code in a project file and also while in the Global.MPT. So how do you protect you protect your precious code?
The best way I found that works for me is exporting my code to a folder on my desktop or a hard drive every time I make a change to my code. This means that some days I might make up to 15 copies of my code in an external folder so that I can always go back to see what changes I made along the way. So how is it done?
Understanding MS Project VBA Editor
Before we start exporting data from the VBA Editor in MS Project, let’s explore where we can find this environment. One way to get to the VBA Editor from MS Project window is by clicking Alt + F11. Another way is to go to the View tab in your ribbon and click the upside down triangle on the Macros section. From there choose Visual Basic.
Visual Basic Editior (VBE)
Once you get to the Visual Basic Editor (VBE) you can start coding. I recommend you save your code all the time, especially before running it if you are constantly making changes. After a couple of lines of code, you might want to consider exporting your data to safeguard it outside of MS Project so no matter how many times it crashes you can always bring it back.
Let’s first take a look at the VBE. In there you will notice at least two main areas (three if you are connected to MS Project Server). Since on my personal laptop I don’t have access to MS Project Server, I only have two main areas. You can see these areas usually to the left top of your VBE. If you can’t see it go to your View menu and click on Project Explorer or do CTRL + R.
Once you can see your Project Explorer, then you will be able to see the two main areas in a tree structure.
The first main node is the Project Global (Global.MPT). The second main node will be your currently opened project. If you have more than one project file open, then you will see one node per project. The project file node is the one with VBAProject (<project file name>). Since I only have one project open I only see two main nodes. (For those of you with MS Project Server, the third main node or bottom node will be a Global Enterprise node. This is where all the code is kept for the whole enterprise/company).
Within each node you will find different types of sections for modules, class modules, and user forms as your project requires.
Exporting MS Project VBA
Now that we have identified the two main areas in the Project Explorer, now we are ready to export our code. I want to export Module1 from my opened project file.
- Navigate VBAProject(MG VBA Macros) > Modules > Module1
- Right-click on Module1 and choose Export File
- Choose where you want to save your code and click Save. This will export your code as a .BAS extension file.
And that’s it! Your wonderful code is saved! Now if MS Project crashes or something weird happens you will have your code somewhere else.
What I love about having the code exported this way instead of highlighting it all and pasting to Notepad (or any other editor) is that the code is properly formatted. So when you try to imported back into MS Project it works perfectly. I sometimes have trouble with the code when I copy / paste directly to notepad… it can create errors once you try to paste it back into MS Project.
Importing MS Project VBA
So now you may be asking yourself, how do we import the code back in? Good question!
To demonstrate I deleted the previous Module1 from my project. Now I want to bring it back into my project by importing it.
- Right-click on VBAProject(<your project name>)
- Click on Import File
- Navigate to the folder where you keep your code
- Click on the file with your code and click Open
Then you are done! You have Exported and Imported your code in MS Project!
Until next time… 🙂
MS Project 2010 VBA: Uppercase or Lowercase
Posted: February 10, 2016 Filed under: MS Project 2010, MS Project Custom Fields, MS Project VBA, Program Management Leave a commentToday we are going to try our first VBA script on MAD Schedules! I promise I’ll try to keep it as simple as possible so you can follow easily. I assume you have the VBA Editor open and have a module ready for code. If not, then go back to my post on How to Start with VBA in MS Project 2010.
Okay, let’s start! I assume you have a schedule with some information. Here is an example of a schedule I built:
I have a custom field in text1 called “Contract#”. All the text in text1 is in lower case letters, but what if I want them to all be in upper case letters? It would take me about 10 or 15 minutes to fix each one at a time… but what if I had 1000 lines? That would take me quite some time! Especially if I had different Contract numbers and couldn’t just fix it quickly. That’s where the beauty of VBA comes in for our MS Project schedules! I go to my VBA Editor with my blank module such as here:
In the editor you can type:
Sub UpperCase()
Dim tskTask As Task
For Each tskTask In ActiveProject.Tasks
tskTask.Text1 = UCase(tskTask.Text1)
Next tskTask
End Sub
This code can be used for any text field in Microsoft Project. Just change out Text1 in the code for whatever field you need to change to Uppercase.
How to run your new VBA Script
Now you are wondering, how do I test this script? First of all, make sure you are testing code on a copy of a schedule. Do NOT test on a current file! Once the changes are done with a script, there is NO undo button! So proceed with caution…
There are a couple of ways you can access and use your new script. I will touch on one way today. Go to your project file and click on the Developer tab. Then click on View Macros.
Make sure you are viewing macros only for your current project and choose Uppercase macro. Then click on Run.
As you can see it worked! My “project 001” text was all changed to “PROJECT 001”.
How to change to Lowercase Letters
To change your text to lowercase letters you add a new module to your VBA Editor. If you try to use the same module as for the Uppercase script, there is potential for errors to be generated because I’m using the same variable for both scripts.
Type in the new module:
Sub LowerCase()
Dim tskTask As Task
For Each tskTask In ActiveProject.Tasks
tskTask.Text1 = LCase(tskTask.Text1)
Next tskTask
End Sub
To run it, go back to your project file Developer > View Macros > Lowercase > Run. That should do it!
Now that you have a little practice with VBA Script, you can keep modifying this code to do other things in MS Project!
MS Project 2010 Custom Gantt: Add dates to the bars
Posted: February 8, 2016 Filed under: MS Project 2010, MS Project Formatting, MS Project Gantt Formatting, Program Management Leave a commentThe project team has decided they want to see dates on their Gantt chart bars. So how do you do? Easy! Here are the steps you take to change your Gantt in MS Project. It is similarly done MS Project 2013, which I also used daily. Here is a schedule with no dates on the bar chart.
To put dates on the Gantt we can start by adding them to the right side of the bars.
First right click on the Gantt > Bar Styles and make sure you have “Task” highlighted. On the bottom section click on Text tab. Once there next “Right” type Finish and click OK.
Once you click OK you will see the dates on the right of the bars:
If you want to take out the lines and arrows to have a a cleaner looking bar chart, you can right click on gantt > Layout and make sure under Links change it to no arrows (first option). Here you can also change the date format in your bar chart under Date Format.
Below you can see the changes I made to my Tasks and Milestones, plus I took off the lines and arrows. I’m ready to submit my Gantt chart for a schedule presentation. Look how pretty it looks….
Just by playing around with the settings in the two areas I pointed out today, you can change the settings on how your bar chart looks. Hope that helps you on your journey of learning how to format in MS Project.
How to Start With VBA in MS Project 2010
Posted: February 8, 2016 Filed under: MS Project 2010, MS Project VBA Leave a commentTo start scripting in Microsoft Project with Visual Basic for Applications (VBA) you can either start recording a Macro, or work in the VBA Editor. Here I’m going to show you how to get to your VBA editor. I’m using MS Project 2010, but it is very similar in MS Project 2013.
So let’s start:
Go to File > Options
In Options click on Custom Ribbon. Once you are in Custom Ribbon make sure that you have Developer checked on the right pane under Main Tabs Options > Custom Ribbon > Main Tabs > Developer . Then click OK.
Once you have clicked okay, you should be able to see the Developer tab on your Microsoft Project ribbon:
Now that you can access the Developer tab, you can click on the Visual Basic icon to get to the Visual Basic Editor.
To start writing VBA code, you will have to right click on modules and choose to add a module Modules > Insert > Module. Now you are ready to start your first VBA script. Have fun! 🙂
MS Project VBA/Formulas – Intro
Posted: February 9, 2015 Filed under: MS Project 2010, Program Management | Tags: MS Project Leave a commentSince my work has recently increased exponentially, I have been playing around with writing formulas for custom fields and also writing small programs using VBA to simplify my work. I have used VBA with MS Excel in the past, but not so much with project. The problem I find is that it’s hard to find any examples of Project VBA anywhere. There is only one book available called VBA Programming For MS Project ’98 through 2010 with an Intro to VSTO by Rod Gill. (I have no affiliation and first link leads you to two free chapters). If you look hard enough you can find some examples, but mostly learning Project VBA is just trial and error. You have to do it, then run the code to see how it works and to learn how to use it. I plan to have a section somewhere in this site where I have recommendations to books and sites/blogs where you can explore more of these ideas.
Anyway, when I write about formulas for custom fields or snippets of VBA, I can’t promise it will work for everyone. You can take my ideas and implement them to your projects. Play around with them until you get the desired effect. If you have questions and I have the bandwidth to answer, I will gladly help! I plan to name the help titles clearly so it is easy to navigate. Most of this code and formulas I have tested only in MS Project 2010 and 2013 and MS Excel 2010. I’m pretty sure they can be worked somewhat successfully from MS Project 2003 through 2013. As for Excel, I can’t venture to guess. But never fear, unlike MS Project, there is lot’s of VBA information about MS Excel on the internet.
Hopefully my journey through MS Project VBA and my documentation here will help you out! Cheers! 🙂
MS Project 2010 – How to Show and Hide Columns
Posted: January 26, 2015 Filed under: MS Project 2010, MS Project Formatting | Tags: Microsoft Project, MS Project Leave a commentI am frequently asked how to add and remove columns in Microsoft Project. I help people who use MS Project 2010 and 2013, so it should work for both. As I’ve mentioned before, for these examples I’m using MS Project 2010 because that’s what I have installed on my PC. At work I use MS Project 2010 and 2013.
Show/Hide a Column
Let’s talk about removing columns first. When I say remove column, I mean remove it from view or hide it. MS Project acts like a database and stores all our hidden columns and all the information as long as we don’t delete data on purpose. So how do we do it?
- Option 1 – Select the whole column you want remove (hide) and click delete on your keyboard. That’s it, the column is hidden from view. But don’t fret, the column is still within the MS Project database which is your file.
- Option 2 – Select the whole column you want remove (hide) and right-click on your selected column. Choose Hide Column and your column will be hidden.
- Option 3 – If option 1 scares you a little, that’s okay. You can hide your column from options on the ribbon located at the top of MS Project. First select a cell or the whole column you want to hide, then go to Gantt Chart Tools -> Format -> Column Settings -> Hide Column
Presto! Your column is now hidden.
Show/Insert a Column
So you’ve hidden the columns you didn’t need. But suddenly your boss calls you and tells you he wants to see the column you just hid! How to show it again? Well, as with most actions in MS Project, there are a few ways to do this. Be patient, the solution is coming up!
- Option 1 – The easiest way to add a new column, at least for me, is to highlight (select) the column that is going to be to the left of the column you want to insert. For example, if you want your new column to be the first column, then select the whole first column. Then press the Insert key on your keyboard, then scroll down the menu until you find the column you want to add. You can also start typing the name and it will start scrolling by itself.
- Option 2 – This option is similar to option 1 where you select the column you want your new column to be at. You right-click on this selected column and choose Insert Column from the menu that pops open. Now scroll down and find the column you want to insert.
- Option 3 – If you prefer to use the ribbon, you can go to Gantt Chart Tools -> Format -> Insert Column. Then scroll down until you find the column you need.
- Option 4 – Believe it or not, there is a fourth way to add a new column into your project schedule. In your current view, scroll to the last column and the last column should be a column that says Add New Column. If it’s not visible and you really want to use this method, you can add it by going to your Gantt Chart Tools -> Format -> Custom Settings and choose Display Add New Column
That should do it! Now you know how to manipulate your schedule to show/hide any column within the schedule. Customizing new columns is for another post! Enjoy your new knowledge.
MS Project 2010 – How to Zoom in and out Using Timescale in Gantt
Posted: January 23, 2015 Filed under: MS Project 2010 | Tags: MS Project, Timescale Leave a commentHave you ever inherited a project where the Timescale of your Gantt does not show anything useful? It may only show a couple of days worth for a year-long project? Fear not. There are a couple of ways you can adjust it, either through the ribbon, or directly through the Timescale area.
Have you seen a Gantt that looks like this?
It shows nothing in the Gantt area because the Timescale is in hours, while the project spans weeks. So how can we fix this?
Option 1 – Go to the ribbon on the top part of MS Project. Select the View tab, then look for the Zoom group. You can quickly see the whole project by selecting Entire Project.
Once you click Entire Project you should be able to see your whole project in the Gantt chart.
You can play around with the buttons within the Zoom group to get your Gantt looking how you want it to look.
Option 2 – Another way to change your timescale is to actually right-click on the timescale. You will then see several options
To zoom the timescale to show all the tasks you can choose Zoom and then choose to zoom to Entire Project.
You can also do a custom zoom to see different time periods on your Gantt.
Have fun changing the settings! Do you have other ways you adjust zoom in your project?
MS Project 2010 – How to easily spell check your project schedule
Posted: January 22, 2015 Filed under: MS Project 2010 | Tags: Microsoft Project, MS Project Leave a commentRecently one of my coworkers asked me how to do spell check in Microsoft Project. Although I usually create new schedules, I usually export them to MS Excel to do other calculations. There I easily check my spelling. But how in the world do you spell check in MS Project 2010? You would think that just by highlighting a column and then right click you would easily find the spell checker in the options. Unfortunately it’s not there, but there is no need to worry. There are two ways to quickly run the spell checker. I did all the digging, you just need to scroll down and read some more.
- Option 1 – Press F7. Yes, it’s that easy (and you can find this in my MS Project Shortcut List).
- Option 2 – Go to the ribbon on the top part of MS Project and click on the Project tab (shown below). Then go all the way to the right and you will find the ABC button for the spell checker.
Then make sure that you pick the right language. I’m not sure why mine was set up to a language other than English You can see in the image below:
If you do need to change the language, be prepared to reset the spell checker and you will need to restart it.
MAD Schedules 