Microsoft Project VBA: How to Store Your Code by Importing and Exporting Modules

Posted: June 21, 2017 | Author: | Filed under: MS Project 2010, MS Project VBA, Program Management | Leave a comment

I 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.

MSP Ribbon to Visual Basic Editor

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.

MSP VBE Project Explorer

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.

VBA Environment v1

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

MSP VBE Export

  • Choose where you want to save your code and click Save.  This will export your code as a .BAS extension file.

MSP VBE Export Save

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

MSP VBE ImportMSP VBE Import Open

Then you are done!  You have Exported and Imported your code in MS Project!

Until next time… 🙂

Looking to Recover your Global.MPT? You’re in good company….

Posted: May 4, 2017 | Author: | Filed under: MS Project Custom Fields, MS Project Formatting, MS Project VBA, Program Management, Uncategorized | Leave a comment

When working with Microsoft Project (MSP), there is nothing more frustrating than losing the hard work you’ve put into a file whether it’s a schedule or VBA / Macros to work with your schedule.

Today I’m here writing about ways to try to recover your Global.MPT because I completely lost it today…. or so I thought.  I was happily coding away building an awesome script in VBA  and was feeling such success when this setback had me going from blissful to horrified in seconds.  The code hung and I had to force MSP to close.  Once I tried to open it back again and go to my code, it was all gone!  The first thought that crossed my mind was, “Why didn’t I back it up?!?”  followed by, “How  could I have done such a rookie mistake?!?”

I then tried to calm down and try to troubleshoot and see if I had actually lost it all.   The thing is, I keep a backup of all my code that I push to production from the development environment. The code that I build as helper functions such as adding text to the beginning or the end of a group of tasks quickly, this code I keep it in my .MPT file because I don’t want to keep it in the development environment and accidentally push it to production.  I had built quite a few of this functions in the last couple of weeks, but hadn’t taken the time to back them up…. they are just little helper functions anyway, right?  Hard lesson to learn was how important they are to me once I realized they were all gone….

A quick search online does not quickly give you ways to try to restore your Global.MPT file.  I had to dig deep to actually find different methods to try.  I will add the two that might give you results.  Firstly, to find where in your computer you might find your Global.MPT,  you can check out this article from Microsoft Support.

Once I found my Global.MPT  on my computer at:

“C:/users/[your user name]/App data/Roaming/Microsoft/MS Project/1033”

I then immediately:

  1. Closed Microsoft Project completely
  2. Copied the Global.MPT file to a new folder
  3. Once I had a copy, I went back to the above address and renamade the file and changed the extension of the file to .MPP
  4. Then I opened the newly renamed file in step 3 with Microsoft Project
  5. The organizer pops up, then choose the Modules tab and copy all the “lost” modules to your new MPT file
  6. Make sure to reset any references in your library you were using for your macros if any

Of course, this doesn’t work for everyone.  If you are one of the unlucky ones, I have one more trick up my sleeve.  I found this solution when I was ready to give up on recuperating my awesome code.  I had already started to mourn the loss of such great work…

  1. Go to C:\Users\[Your User Name]\AppData\Local\Temp
  2. Filter by Date
  3. Scroll down until you find your latest, mine was: “AutoRecovery Save of Global (183).MPT
  4. I immediately made a copy before I attempted to open.
  5. Once I had the backup, I renamed the file and changed the extension to .MPP
  6. I opened it and the Organizer was immediately launched.
  7. I copied over my code from the Modules tab to my new file.

I cannot tell you the immense relief I felt once I figured I hadn’t lost it all.  I can recreate the few lines I did lose, but those helper functions are life (and time) savers for sure!

The moral of the story for both you and I is to make sure to create backups of ALL your code.  Even code that you might not think is very important.  It’s better to have it and not need it, than to need it and not have it.

Some ideas to save your code would be first of all NOT keep the code in Global.MPT.  Even if you do, make sure to save your code in an actual .MPP file and create backups of that.  Also, you can export your modules as .bas files or .frm for forms to a folder on your desktop.  I’ve actually created code that exports all of them quickly.  That’s one of the sub routines I had thought lost forever!  That would have been a shame to lose….

Anyway, good luck  recuperating your Global.MPT file…. know that you are in good company!

MS Project 2010 VBA: Uppercase or Lowercase

Posted: February 10, 2016 | Author: | Filed under: MS Project 2010, MS Project Custom Fields, MS Project VBA, Program Management | Leave a comment

Today 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 | Author: | Filed under: MS Project 2010, MS Project Formatting, MS Project Gantt Formatting, Program Management | Leave a comment

The 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.

 

Plan of Action for PMI-SP Exam

Posted: February 6, 2016 | Author: | Filed under: PMI-SP, Program Management | Leave a comment

I have finally decided to come back to writing here… it seems I haven’t written for about a year.  The good news is I was away because I was really busy with work and life.  I have acquired so much more experience in such a short year!  I have done about 40 proposal schedules for different programs, ranging from programs worth a couple $100k USD to a multi-billion dollar program.  I’ve also handled about 15 schedules in execution mode at my highest point… that sure was exhausting! While I was doing all that, I was also helping out other schedulers build custom formulas and VBA scripts to streamline the statusing phase of their programs.  It has become of vital importance for me to be up to speed on cost and schedule integration using MS Project Server.  This next year promises to be interesting!  I hope I learn more and expand my skills in different directions…

Speaking of which, I have decided to try to get my PMI-SP certification this year.  I don’t know how feasible it is since I’m so busy, but it won’t happen if I don’t at least try, right?  Since I’m a planner in and outside of work, I have already laid out a plan to achieve this goal.  Would you like to hear about it?

For those of you who don’t know what PMI-SP certification, I will explain it a little here.  You can find more information at Project Management Institute (PMI) Scheduling Professional about page.  The PMI-SP certification is very similar to the Project Management Professional (PMP) certification, but specializes on the scheduling aspect of project management.  To be even allowed to take the test you need:

  • Advanced knowledge and experience developing, managing and maintaining project schedules
  • High school diploma or Associate degree holders need:
    • 5,000 hours of project scheduling experience
    • 40 hours of project scheduling education
  • 4 year degree holders need:
    • 3,500 hours of project scheduling experience
    • 35 hours of project scheduling education

I totally exceed these requirements, so I went ahead and applied.  I waited 5 LONG work days to find out they had accepted my application and now I can pay and schedule my test date… The problem is that I’m not sure I’m ready to take the test yet.  The good thing is that I can take the test up to three times in a one year period before I have to re-apply.  All I have to say is that the test is EXTREMELY expensive, to me anyway, and I want to try my best to pass it the first time.  Wouldn’t you feel the same?

  • $520 member (subject to change)
  • $670 non-member (subject to change)

I quickly became a member because then you can see their Practice Standards and Frameworks for free.

So what’s my plan to study for the PMI-SP certification?  First I have started reading these documents/books:

  • Practice Standard for Scheduling
  • Practice Standard for Earned Value Management – Second Edition
  • Practice Standard for Work Breakdown Structures—Second Edition (Reaffirmed)
  • PMBOK® Guide & Standards
  • Dynamic Scheduling® With Microsoft® Project 2013: The Book By and For Professionals by Rodolfo Ambriz and Mario Landa

I’m also going to look all over the internet to see if I can find practice questions for free or low cost.  As I try these questions I’ll go back to the resources above and see how everything aligns.

If you’ve taken this test in the past, what have you done to prepare?

MS Project VBA/Formulas – Intro

Posted: February 9, 2015 | Author: | Filed under: MS Project 2010, Program Management | Tags: | Leave a comment

Since 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!  🙂

Managing Multiple Program Schedules

Posted: February 2, 2015 | Author: | Filed under: Program Management | Tags: | Leave a comment

Recently I’ve been tasked to manage more programs in addition to the programs I’m currently managing.  Although I could have used a little bit more work, it feels like now the work has quadrupled what I used to do.  If you have been a scheduler with complex program schedules for a while, you know that just adding one more program can be a challenge.  Add to that, now I have more than three program managers I have to coordinate and status.  Let’s just say I’m feeling a little overwhelmed right now.

I’m a time manager, so how do I manage my time?  The first thing I did was lay down boundaries.  Everyone’s program is priority to them.  They have to realize there is only one of me and a finite amount of time.  So, I began by explaining how my workload has exploded and that I am trying to get a handle on it.  Once I get a handle, I can better commit to a percentage of my time per program.  I was clear that eventually we might need to hire more schedulers.  I’m pretty sure they realized this, but it was just a matter of timing.

Although it surprised them that I was vocal about my time commitment, it showed that I can handle the work and will let them know when it gets to be too much.  It also showed that I don’t like to make promises that I cannot keep.  Being straight forward with your stakeholders is very important.  They need to know what they can and can’t realistically expect from you.

So how am I going to deal with the increase of workload?  I’m going to have to immerse myself in all details of each program.  By really diving deep and understanding what is the end goal and how we plan to get there, I can build more robust schedules.  I already have several templates to streamline the process of building new schedules.  The challenge right now is the schedules I’ve inherited that have not been optimized.  Schedules should not be too short or too long.  They should be the right size for the program/project at hand.  Wherever I can, I will re-design and implement scheduling best practices.  If the schedules are already baselined, I will have to live with it and do my best.

Finally, I will have to win the trust of each of my new teams.  I have to show that they can trust my skills.  The best way I have found that works for me is to actively listen, ask a lot of questions, and show that I care about their program.  Having everyone’s trust is the key to not only building great schedules, but getting accurate status.  People need to know I’m not out there to get them in trouble, I’m there to ensure our program is a success and we deliver on time and on budget.  Don’t forget to communicate!  Let the team know at all times when there are major changes, and when you need something from them.

To recap, these are the main ideas of managing multiple programs:

1. Be clear of what you can and can’t do as a scheduler.

2. Get the trust of your team.

3. Really listen and show that you care.

4. Ask a lot of questions.  Make sure everything is clear in your mind.

5. Communicate as often as necessary.

If you find yourself in the same situation I’m in right now, just hang in there.  Once you figure out and straighten out the schedules, I find that it is smooth sailing.  Or almost.