Please find below a transcription of the audio portion of Ira Brown’s Beyond Macros Revisited: Automating Microsoft Project for Non-Techies Part 2 webinar being provided by MPUG for the convenience of our members. You may wish to use this transcript for the purposes of self-paced learning, searching for specific information, and/or performing a quick review of webinar content. There may be exclusions, such as those steps included in product demonstrations. You may watch the live recording of this webinar at your convenience.
Kyle: Hello everyone and welcome to today’s MPUG webinar, Beyond Macros Revisited: Automating Microsoft Project for Non-Techies Part Two. My name is Kyle and I’ll be the moderator today and today’s session is eligible for one PMI PDU in the Technical category. The activity code for claiming that with PMI is on the screen now.
Kyle: Like all MPUG webinars, a recording of this session will be posted to mpug.com shortly after the live presentation ends and all members can watch these recordings at any time and still be eligible to earn the PDU credit. All of the sessions you watch on demand can be submitted to your webinar history and the live sessions you attend are automatically submitted. Within your history, you could print or download your training transcripts, certificates of completion, including the one for today, and you can access your member training history by logging into mpug.com, click My Account and then click on the Webinar Reports Link. If you have any questions during today’s presentation, please send those over at any time using the chat question box, I’m going to go to webinar control panel, where we do plan to answer those questions for you throughout the session today.
Kyle: All right, we’ll go ahead and begin. We’re very happy to welcome back Ira Brown today. Ira is the Founder and President of Project Widgets. He is a leader in the field of project management and is a recognized Microsoft Project Expert. He has over 25 years of experience helping organizations implement enterprise project management solutions. Ira is a widely published author of several Microsoft Project books and a frequent MPUG webinar presenter on the topic of Automating Project. He also presented part one of this macro training series last month, which many of you were able to attend and there was such a great interest and feedback from that session that Ira has returned to build onto that presentation and share more with the MPUG community. So welcome back Ira and at this time I’ll hand it over to you to start with today’s session.
Ira Brown: All right. Thank you very much Kyle. I appreciate that. Let me go ahead and share my screen with everyone.
Kyle: That looks good.
Ira Brown: Okay, great. Well thanks everyone for joining the session this afternoon. Again, my name is Ira Brown and I’ve presented to the MPUG community now for many, many years so it’s great to be back. Today’s session is called, Beyond Macros Revisited: Automating Microsoft Project for Non-Techies Part Two. So some of you may have noticed in Microsoft Project that there is this macro feature and you can… it says record macro and you may be tried it out from time to time or maybe it seemed a little scary. So, the whole purpose of today’s session is to familiarize you with how do we go about using macros and just get things done more efficiently in Microsoft Project. So we’re going to go through some examples of what I think are some real world applications for macros to make your job easier in Microsoft Project. So let’s get right into it.
Ira Brown: So first just a quick overview about Project Widgets. Project Widgets is a Microsoft Project, gold partner and solution provider. I’ve been at this now for well over 25 years delivering Microsoft Project related services and products and contributed to a number of books on Microsoft Project and we work with companies in many different industries that use Microsoft Project and we specialize in basically, implementing Microsoft Project solutions for a company to help meet their requirements and to make it easier for them to get their job done. That often involves creating custom solutions that we refer to as our widgets. So we actually have an offer, I’ll tell you a little bit more about at the end of the session, but we’re going to have a drawing today where you can win a free copy of what we call our Driving Path Widget.
Ira Brown: So we’re going to be giving away a few copies of that and all you have to do at the end of the session, just send me an email to request a copy of this presentation today. Also, all the macros that we’re going to be creating during the session, I will make available to you. You just have to send me an email with your contact information and we’ll send you a copy of the presentation, we’ll send you a copy of all the macros we create and you’ll be entered into our drawing for a free copy of the Driving Path Widgets. So, pretty good deal. All right. So here’s our agenda for today. So we’ll do [inaudible 00:04:57] able to attend last time we’ll see how do we go about recording and then editing basic macros within project.
Ira Brown: We’ll see how we can assign that macro to a button in the ribbon just to make it easier to run. Then we’ll get into some specific examples around how you can utilize macros. So, we’ll go with something fairly simple to get us started. We’ll see how we can create an automated printing routine. So, if you have to create some reports on a regular basis, we’ll build a little macro to do it. Just click a button and then you can produce your reports whenever you need to. We’ll also see how we can come up with a macro to set the baseline for new tasks that have been added to your project. We’ll talk a little bit more about why you’d want to do something like that.
Ira Brown: This is one that maybe you’ve noticed from using Microsoft Project where sometimes the duration of a task comes out to this weird decimal number like this task takes 3.72 days to complete. We’ll oftentimes, Microsoft Project will calculate that duration based upon the resources that are assigned and the number of hours they’re working. So oftentimes you can get kind of odd looking durations and people may be confused when they see that. So it might be better to say, you know what? Let’s round the duration to the nearest complete day so that way you’ll get rid of some of those fractional duration. So we’ll show you how to do that and how you can record a macro to do that. Then we’ll see how we can even go beyond Microsoft Project. So one of the great things about the macro language is that everything that we’re going to learn about today in Microsoft Project also applies to Excel and Word and PowerPoint and even Outlook. They all have the same internal macro language.
Ira Brown: So we’re going to see how we can actually do some integration with Excel, how we can use Excel to store resource rates for resources in our project that way we don’t have to actually store the rates in the project. Then we’ll see how we can just sort of bring them in via the automation, produce some kind of cost report and then when we’re finished producing the report, we can then remove the rates from the project. Okay? Then we’ll wind up the session by saying, well, these are great macros that you’ve created, what if you want to share them with other users within your organization, how do you go about distributing your macros as well as your custom ribbon buttons to other users? So, we’ll see how we can do that as well.
Ira Brown: So that’s quite a bit we have packed into the next hour so I’ll try to keep this moving along pretty quickly. This session is being recorded by the way, so you’ll be able to view this again if you’d like. Again, don’t worry about trying to have to write a bunch of things down and remember the macros or anything like that because all you have to do is send me an email and I will send you a copy of them and you’ll be able to have everything that we’ve done here today. All right, so with that, let’s get right into it.
Ira Brown: So how do you get started? Well, the first thing you probably want to do is you want to target a task that tends to be tedious, time consuming, error prone, repetitive. Macros are great for things that you have to do again and again. So, instead of having to go through the same series of steps all the time to do something, let’s build a macro to automate it then we can click a button in the ribbon and then all of a sudden we’ve maybe saved ourselves a few minutes, possibly a few hours depending on how complicated the function is. Okay? So that’s a good use for a macro. Well, the important thing that you want to think about to get started is how would you accomplish this manually? In other words, macros can’t do anything in Microsoft Project that you can’t otherwise do manually.
Ira Brown: So, the first example that we’re going to be covering is, how would you create some kind of automated printing routine? Well, the first thing you have to know is, how would you print reports manually in project? Then once you know how to accomplish something manually, then we can actually turn on the macro recorder and we’ll go through those same series of steps and it’ll actually record those steps and we’ll be able to take a look at the macro that got generated and sometimes we’re going to be editing that to do even more than what the recorder can pick up. So we’ll see how to do that.
Ira Brown: So, once you have your thoughts together about what it is you’re trying to accomplish? How would you do it manually? Then you have to click the button, record a macro. Then at that point all the things that you’re doing in Microsoft Project are being recorded by the macro recorder. Once you’ve completed recording the macro, we will stop the recorder and then we’ll be able to edit the macro as required. We’ll then want to most likely assign the macro to a ribbon button so you can make it easy to run. That’s all I have to say on that. So let’s go, let’s get into our first example, example number one, creating automated printing routines.
Ira Brown: Okay, so let’s get out of PowerPoint for now. Let’s start a Microsoft Project. I’m going to be starting it just in this computer mode here, meaning standalone Microsoft Project but keep in mind that everything that I’m showing you today will work in standalone project, it will work in, if you’re using project server, it will work if you’re using project online. In other words, it’s all… it just requires the Microsoft Project client application and you can record these macros and run these macros. All right, so I’m going to go ahead and open up a project that I thought might be a fun one to work with, our wine tasting fundraiser.
Ira Brown: So believe it or not, there is a template that’s available from Microsoft Project, if you ever running a wine tasting fundraiser, here’s your template to get that done. You can just go ahead and download that. What I’m going to do is, I’ve been asked to produce a few different reports on a weekly basis for my management team. So seems like a good use for maybe automating… good opportunity to automate that. So how would we do it? So remember I said first thing you want to do is how would you accomplish it manually? So to do this manually we would be clicking on the report tab under dashboards. This is where all the reports are that I want to be able to run. So, I’m going to record the macro and then I’m going to run the reports and then we’ll just let all of that macro code get captured by the recorder.
Ira Brown: All right, so I think we’re ready to get started here. So the way you go about recording a macro is you click on the View tab up top, all the way on the right you see where it says Macros, just click on… just drop that down, and choose Record a Macro. All right. Then you want to give your macro a name. So I’m just going to call this PrintWeeklyReports. All right, notice that I am not putting any spaces in. I’m capitalizing the first letter of each word just to make it a little bit easier to read and that’s it. So I’m going to just go ahead and click Okay and we’re now recording, all right?
Ira Brown: So, the first thing I will do is I want to run the project overview report. So I’m going to click on report, dashboards, Project Overview, and there’s the report and it’s showing on my screen, but I also want to print it. So I’m going to… again, how do I do that manually? Well, File and Print. All right and then we probably want to do something about making sure that it fits on nicely on the page. So I’ll go to page setup and I’ll make sure that I pick this option right here, fit to one by one. That’s going to be part of what gets recorded. We’ll click Okay, and then I’ll click Print and here goes. You’ll actually probably hear in the background, that’s my printer printing out that report right now. So that was just recorded by the macro recorder and I’m actually holding the report in my hand right now. It looks really nice.
Ira Brown: All right, so remember we’re still recording the macro. So let’s go back because remember there’s another report that I want to produce. This is called the Burn Down Report. So I’ll click on Report, Dashboards, Burned Down. All right. This time I won’t print it and I’ll show you why in a moment. Then I’m going to run the third report called Resource Cost Overview. Again, Report, Dashboards and a Cost Overview and there we go. So we’ve just run all the reports. Now, remember once we’ve completed going through the steps, we need to turn off the macro recorder. So we go to Macros and we say Stop Recording. Now, we want to take a look at the macro code that got captured by the recorder. So to do that I click on the dropdown and I can choose, View Macros and PrintWeeklyReports. That’s the one I just recorded. It’s in the list and then I’ll click Edit and there we go.
Ira Brown: So we are now looking at the visual basic for applications editor in Microsoft Project. Let’s take a look and see what’s here. Well, notice on the left side of the screen we have this area of the screen that’s referred to as the Project Explorer. You can see that this module just got added into there that’s actually what the macro recorder created something just called Module 1. All right, and then within the module, this is the macro, remember that’s what I named it, PrintWeeklyReports. So the macro language that Microsoft project uses is actually referred to as Visual Basic for Applications. Let’s take a look and see what the macro recorder gave us. Well, first of all we… I’m going to just put some spaces in here or some blank rows just to make it a bit easier to read. Okay? So, the first thing we’ll notice is that when I click the button to run the Project Overview report, it actually added this line here that says, ApplyReport and then name:= “Project Overview.”
Ira Brown: So this is what’s referred to as the Apply Report Method and then you need to specify a parameter that goes along with the method. In this case, it’s the name of the report that you want to run, which is Project Overview. So that’s how it knows to run that particular report, right? So we ran the report and then remember I went into the page set up and I said how that I wanted to be able to fit on one page so it captured that as well and then finally, file print. That’s when I actually click the Print button and then it sent it to the printer. So one of the things that you may want to do, and I always do this whenever I write any kind of code, is I always put in comments just to make this easier to read because right now it makes perfect sense to me but next week, not as much, next year, I may totally forget what the purpose of this macro was.
Ira Brown: So always put in comments and the way you do that is you put in a little apostrophe character and then you just start typing in your comments. So I’m just going to say here, print report one and then I’ll put a comment here and I’ll say set up page and then I’ll put another comment here and I’ll just say print or send to printer or something like that. This is just for your benefit to make it easier to read. All right. Then notice that we ran the next report. It’s exactly the same method, apply report but this time we have a different name Burned Down. Then we did it a third time, Apply Report, Cost Overview. So, let me just copy these lines right here, right? Because we’re still going to want to make it fit on one page and we’re still going to want to print it.
Ira Brown: So I’m just going to highlight it and copy it and then I’m going to paste it there and I’m just going to paste it there. Put in one more comment here. Run report two and ran report three. There we go. So there is our macro PrintWeeklyReports, looks pretty good. What I will now do is, I want to test it to see if it will actually print all three reports. So I’m going to go ahead and go back to the main project window. Notice at the bottom of the screen I can click on project, then go back to the project right here. If I click Macros, View Macros, there’s my macro called PrintWeeklyReports and I’m just going to click Run and notice how it’s just going through and running all those reports for me.
Ira Brown: So this is where you might have to do a little bit of troubleshooting if the macro recorder didn’t capture everything that you wanted to do, you can kind of fine tune it a little bit and out comes your report. So now what we want to be able to do is, we want to make this something that’s very easy to do any time I want to be able to run the report. So for that, we’re going to want to assign this macro to the ribbon in project. So how do we go about doing that? Well, what I’m going to do is I’m going to right click in the ribbon area, choose Customize the Ribbon and we’re going to add a new tab to the ribbon. We do that just by clicking the New tab button here. All right, and I’m going to highlight where it says New tab. I’m going to rename it and I’m going to call it Widgets.
Ira Brown: Then we’re going to then see where it says New Group right below it. So the group is really what you see at the bottom here, like where it says View, Assignments, Insert, that’s considered the group, in other words, group of buttons. So we’re going to rename the group and we’re going to call it MPUG. Okay. So now that I have my tab, now that I have my group, I want to bring that macro over as a button. So I’m going to pick where it says here, choose commands from, I’m going to pick Macros, I’m going to find my PrintWeeklyReports. I’m going to then click the Add button. Notice how it adds that right below the MPUG Group. Then what I’ll do is, I’m just going to right click it. I’m going to say Rename. I’m going to give it a little bit of a friendlier name.
Ira Brown: At this point I can put the spaces back in Print Weekly Reports. I can try to find a good image that I want to show for this particular macro. So let’s see if there’s anything that looks… as a printer right there, look at that one. Click Okay, click Okay. Notice that we now have a Widgets tab and we have an MPUG group, Print Weekly Reports. So now anytime I want to run those reports, all I have to do is click the button. So we’re well on our way here to becoming proficient at using macros. So what we’re going to do now is we’re going to move on to our next example. Let’s go back to PowerPoint for a moment.
Ira Brown: Our next example is setting the baseline for new tasks in your project. Okay. So, why would you want to do something like this? What’s the business need for that? Well, let me give you an example of why you might want to do something like that. I’m going to go ahead first of all, open up a… I’m finished with this project let’s go ahead and close it and I don’t have to save it. Let me open up my little baseline example project here. Okay, so once again, we’re back to the fundraiser and the reason you’d want to do something like this… well, first let me explain a little bit about setting a baseline in Microsoft Project. So, if you’re not familiar, when you set a baseline in Microsoft Project, what you’re doing is you are capturing this snapshot of what your project looks like at a particular point in time.
Ira Brown: So in other words, when you’ve created your project, you’ve gotten your schedule exactly the way you want it, management agrees that this is what we’re going to be using to manage this initiative, you want to be able to capture what your project looks like at that time so then as things change throughout the life of your project, you’ll be able to compare where your project is at the current time, to what it looked like at the time you set your baseline and you’ll be able to identify variances in your project. So that’s why we want to be able to set a baseline. So when we go ahead and set the baseline, I’ll show you how we do that, we just go and say… and actually before I do that, let me insert a column here. Just so you can see, I’ll put in something here called Baseline Start.
Ira Brown: When I put in Baseline Start and I’ll also put in Baseline Finish, notice that they all say NA right now. That’s because I have not yet set the baseline for this project. So when you set your baseline, what Microsoft Project’s going to do, it’s going to take whatever’s in the start field and it’s going to copy into Baseline Start, whatever’s in finish, it’s going to copy it into Baseline Finish. It also does that for cost and work and duration as well. We’ll just look at start and finish for now though. So let me go to the Project tab. I’m going to go to Set Baseline and then I’m just going to say set the baseline for the entire project and then click Okay. Notice when I do that, this Baseline Start, Baseline Finish are now populated and they look exactly like what’s in Start and Finish.
Ira Brown: I guess I’ll just show you one more thing that’s useful to know. If I insert the column here called Finish Variance, this is just another built-in field that we have in Microsoft Project. Finished Variance is the difference between your Finish and your Baseline Finish. So when it’s zero, that tells you that there’s no difference, everything is exactly on schedule. When that number gets to be greater than zero, that says that task is running late by a certain number of days, right? So that’s a really useful way to see that information. Okay. So getting back to our example here. So one of the things that you may need to do from time to time is you may need to add new tasks into your project. So for example, let’s say when we originally thought we were going to be doing this wine tasting fundraiser, we all planned to have this onsite meeting where we got everybody into the same room.
Ira Brown: While we know, unfortunately, we’re not able to do that at the current time. So we’re all going to have to get on a Teams meeting to do that. We’ll give teams a plug here. So what we’re going to do is, we’re going to add a new task in this project. I’m just going to insert, click the Insert key and I’m just going to call this task, Conduct Teams Meeting. So that way we can all get on this webinar together and plan for the wine tasting event that way. So there’s this new task that we just added into the project. We’ll notice that the baseline start and baseline finish for a new task still says NA because we haven’t captured the baseline for that. So the business reason why we want to do this is so that anytime we add new tasks into the project, we want to be able to click a button to say, let’s just capture the baseline for these new tasks but we don’t want to overwrite the baseline that we captured originally for the project when we first set the baseline.
Ira Brown: So that’s what we’re going to try to accomplish with our macro. So the question is, how would you do something like that manually? It always comes back down to knowing what you do manually. So I think the way I would probably approach this is, if I wanted to say something like, only show me those tasks or let’s find just those tasks that we haven’t set the baseline for, I know I could accomplish that via filter. In other words, I could build a filter that says, only show me those tasks where the baseline start field equals NA and that will kind of isolate that task and find any newly added task in my project. Once I’ve applied the filter, then there’s a feature in Microsoft Project that says, and I’ll just show it to you real quick here. If I go to set baseline, there’s an option that says just set the baseline for selected tasks rather than the entire project. So if we apply that filter, we select the tasks in the filter and then run this function just for select the tasks that should accomplish what we’re trying to do.
Ira Brown: So, now that we know what we want to do, let’s give it a try. So I’m going to go ahead and turn on the macro recorder. So once again, I’ll say View Macros, Record a Macro, and I’ll just call this something like Set Baseline New Tasks and I’ll just throw in MPUG at the end of it and then we’ll click okay. So we’re now recording the macro. All right, so let’s think about what we want to do. Well, I said we want to probably be able to filter, right? So let’s… how do we go about building a filter? Well, we’ll go up here and we’ll say, let’s build a new filter and let’s call this tasks, not baseline. Okay? That’ll be the name of the filter. Then we say… I always like to think of it this way, only show those tasks where, and you put the name of the field in here, well, the name of the field we want to evaluate is baseline start.
Ira Brown: Now we could also use baseline finish. We don’t have to do both, but they’ll both accomplish the same thing. But in this case I’ll say Baseline Start equals NA, like that. Then I think we want to click Apply. So notice then we only are seeing the one task that has not yet been baselined. So far, so good. We now need to select that task so I’m going to click up here in the corner button that’ll select all the tasks that are currently displayed. All right, now I want to set the baseline. So I go back to Project, Set Baseline but this time I’m going to say only do it for selected tasks. This way I won’t overwrite the baseline that I had saved previously for the other tasks. We’re going to go ahead and click Okay. It will give me a little bit of a warning here that says the baseline has already been used. Are you sure? Now, technically this message is incorrect, we’re not going to be overwriting the baseline, it’s only going to do it for the newly added tasks.
Ira Brown: So I’ll say yes and notice that we now have the Baseline Start and Baseline Finish fields populated. So, so far everything’s working well and we probably want to maybe unselect everything so there’s a command in project that’s kind of useful control home that’ll take you back up to the top and de-select everything. Now we want to unapply the filter, right? We no longer want to keep this filter applied. So I’ll go back to the View tab. I will just say clear the filter and now we’re back to looking at everything. I think we accomplished what we set out to accomplish. Remember, we’re recording the macro this whole time. So now what I’m going to do is, I’m just going to go to Macros, Stop Recording, Macros, View Macros, Set baseline new tasks MPUG. That’s the one I just created, let’s click edit and there it is. That’s everything that we did. So let’s dissect it a bit and see exactly what happened here.
Ira Brown: Okay. So the first thing it did was we created the filter, right? So let’s just put a comment here, create filter to only show unbaselined tasks. That’s what this is doing. So this is using what’s called the Filter Edit Method and notice it says Create equals True. Now you don’t have to become an expert in how you do this manually. To be honest with you, I’ve been doing this for a long time now and when it was time to add a filter into my VBA code, my macro, I always recorded first because it’s a heck of a lot easier to let the macro recorder generate that for you than to try to figure out how to type it in yourself, right? So just let the recorder do all that hard work for you, it creates the filter right here and then this line right here is apply the filter, right?
Ira Brown: So notice this is now using the filter apply method as it’s called name colon equals tasks not baseline. That’s the name of the filter that we’re saying to apply. So you just apply the filter. Now, the one thing that looks like maybe did not get captured by the macro recorder, and this is where it’s helpful to know some of these little tricks is where I went and I selected all the tasks so I’m going to add one more line here, select all tasks and that is just the select all method as it’s called, that will select all the tasks in the project. Then we’re going to say here, put a comment in, set the baseline for selected tasks, right? So that says Baseline Save All:=False. You know what that means? It means not every task in the project, only those that are selected. So that’s the line of code that gets generated when I say set the baseline.
Ira Brown: Now, the other thing that happened when I clicked at the very top of the project, it generated this line right here. I’m going to get rid of that one because there’s like… I have a better way to do it. I’m going to… I’ll just say select top of project and that’s called Select Beginning. All right. So, we’re going to put that in there and that’s just going to do the equivalent of taking your cursor to the very beginning of the project. This is going to be where we clear the filter, right? Remember we said we don’t want to leave the user with the filter view, we want to return it back to the way it was so that’s why we say, filter apply all tasks. Then if I do that, I technically don’t need that line right here so I’ll just go ahead and get rid of that.
Ira Brown: We’re going to do one more thing here just to jazz this up a little bit. I’m going to put a message in here that the user will see to let them know that this all worked successfully. So we’re going to put a comment in, I’ll say display message and we’re going to do something here called a message box and I’ll say, “The new tasks were baselined successfully.” Okay? And we’ll put that in quotes like that, comma and then we want there to be that little information icon that you see on the message box. So VB information is what you do to put that in. All right? So we now have the macro all ready to go and I’m going to go back to Microsoft Project right now and I am going to add that to the ribbon. So once again, we’re going to right click and we’re just going to say Customize the ribbon and there’s our Widgets.
Ira Brown: I’m sorry, I had… I actually had a second [inaudible 00:34:13] there’s this one right here. There we go. Widgets MPUG and we’re going to say only show me macros here, commands from macros. Then there’s set baseline new task, we’re going to add that on in, there it is. We’re going to rename it just like we did before and we’ll say set baseline for new tasks and I’ll get rid of where it says MPUG and we’ll put our… give a nice little picture there. Let’s figure out what we want to use for our picture. How about something like that? Click Okay, click Okay and now when I go to My Widgets tab, there’s our new button. So now we want to test it. So let’s put in a new tasks. New task here. I’m going to call this Conduct Second Teams Meeting and notice it’s not yet baselined. Let’s click the button. The new tasks were baselined successfully and that’s our message box, right?
Ira Brown: There’s that little information icon, let’s click Okay and sure enough, notice how that new task is now baselined. So it’s doing exactly what we wanted it to do and life couldn’t be better. So we are all set. Let’s move on to our next example and our next example is, let’s see here, rounding a tasks duration to the nearest complete day. Okay, so let’s open up another sample project for this one. Round duration example. Okay, so here’s an example of a project that just has a bunch of these fractional durations, right? 3.55 days. Well, that’s a little bit confusing, right? You’re never going to tell somebody, you know what? We’re going to take 3.55 days to get this done. Chances are people would prefer to see this in whole days, four days, or five days or six days or whatever, right?
Ira Brown: So how do we automate getting this to round up to the nearest whole day? Well, first thing I’m going to do is I’m going to insert a column here, and this is just going to be called the duration one column. So there’s another column or another field and those get 10 duration fields. I’m just going to pick duration one just set to zero right now. I’m just going to copy the contents control C, control V, copy it into duration one just so we can preserve what the original durations were so we can compare this to see if it worked. Now what we’re going to do is, we’re going to take a look at an example of a macro that will do the rounding of the duration for us. Now, this is an example of a macro that we’re actually not going to record. This is one that we’re actually just going to have to write ourselves to understand some of the fundamentals of working with the macro language.
Ira Brown: So let me show you this macro and exactly what it’s doing. We’re going to go to View Macro and this time I’ll just go to where it says Visual Basic. This’ll take me to the visual basic editor and let me go into here the example that I created prior to this session just so I can take you through what it’s doing. By the way, you will get a copy of this. So this is something that you can run anytime you want. Okay. So the first thing we’re going to do here is, we are going to create what’s called a Task Loop and what that means is, this is going to basically through this macro code, process every task within my project.
Ira Brown: So the way we go about doing that is, we put this line of code here that says, for each t in ActiveProject.Tasks. Okay. So what does that mean? Well, first of all, what is t? Well, t is a variable that I created at the top here. Dim t As Task. So t is going to be this variable that represents each task in my plan as I loop through all of the tasks. So for each or for every, think of it that way, looping through each task in my plan, for each t in the active project that I’m currently in and then specifically the tasks within that active project. That’s what that does. It goes on to say skip blank rows. If not, t is nothing then. So what that means is, you know how sometimes people put in blank rows in a project? They’ll go and they’ll do something like this maybe to separate out certain things.
Ira Brown: Well, if you have blank rows in your project that’s really going to mess things up for running a macro. It will fail at that line unless you have something in your macro code to handle it. So that’s what this line does here. If not, t is nothing. That essentially says, if it’s a blank row, just skip right over it. The next line here says skip summary test if not t.summary Then. What does that mean? That says if the task that I’m now processing is a summary task, I don’t want to round the duration for the summary task, project won’t even let me do that. I can only do this for sub tasks not summary tasks. Okay, so finally, now we get to the line of code where all the action’s happening and that’s this one right here. All right, so let me dissect this for you a bit.
Ira Brown: So the first thing, notice that it says t.Duration, well t.Duration is the duration of the task that I’m processing. However, one of the things that you have to know is that when you’re working within the macro language, duration is actually expressed in minutes. So when you have a task that is one day, for example, if you were to look here in the macro language, that actually is going to display as… is going to show as 480 minutes assuming that there’s eight hours in a working day. Okay? So what we want to do first is we want to take that duration and we want to divide it by 480 so that now will express it in terms of a day, but we might have a fractional portion of the day so that’s where this comes in. Now we use this built in command that’s available within VBA called round that will actually do the rounding for us of that fractional portion of the day.
Ira Brown: Then once we’ve done the rounding, that’s what that it takes care of or actually that, up to that paren. Then what we want to do as a final step is we have to convert it back to minutes. So that’s why we have to multiply it by 480 here at the end. Okay. So yeah, you’re back to doing math, but as long as this makes sense, then we’re good to go. So what I’m going to do now is we actually want to test this out. So I am going to just take this window, I’m going to just kind of drag it over and by the way, close the project explore, that’s okay, I can always bring it back and I’m just going to drag this over so I can actually see that duration field, right? Now I’m going to step this code and by that I mean run one line at a time. I do that by pressing the F8 function key.
Ira Brown: Notice each time I press F8, it advances to the next line if not t.Summary. So notice the very first one was a summary so it skipped it. I go now doing the next one, it’s skipped the second row, which also is a summary. So now we’re about to do the third row. This time it’s not a summary, okay? So notice if I hold my mouse over t.Duration, you see how it says 1,704? Well, that’s minutes. I divide that by the 480, I do my rounding and then multiply it by 480. Let me run that line of code right there, keep your eye on that duration right there where it says 3.55 days. I’m going to press F8. Notice that now it says four days. Oh my goodness, it’s working. Now I just want to let the whole thing run so I’m going to press the F5 function key that just says go ahead and process the whole thing rather than do it one line at a time and it’s now running. It’s done and now if I look at my project, look at that folks, all these tasks now have whole day durations.
Ira Brown: Remember in Duration One, there’s our 19.8, that’s what it was now it’s 20, 6.6 is now seven. All right? So it did exactly what we wanted it to do. Perfect. Then of course what we’d want to do is assign it to the ribbon. I’ll skip that step for now in the interest of time but you all know how to do that. We would just add that right to the Widgets tab here and put that in and round duration to days. Now, one of the things you could do also is, this might be a nice kind of macro to run when you save your project. Well, I’m going to throw in a little bonus here, if I go back to the VBA environment, this is where we started off, right? I just brought up that Project Explorer again. Okay? I’m going to show you this, if I go to where it says Microsoft Project Objects and I double click that and then I go to where it says This Project and then I go to where it says Project here and then I go to where it says Before Save here, let me just get rid of that because we don’t need it.
Ira Brown: This is actually allowing me to assign a macro to what’s called the Before Save Event in Microsoft Project. So what that means is if that round duration two days, which is right here, no it’s not, it’s actually right here, Set duration to whole days. That’s the name of the macro. What I can do is I can actually go to this Before Save event and I can type in the word Call followed by the name of the macro. You know what that does? I think you’ve figured it out already, right? When I go to Save My Project, it will automatically set the duration to whole days, right? So that’s something that you could do as well.
Ira Brown: Now, what I would probably recommend that you do, and we don’t have time to do it now, but maybe before you change the duration, check to see if it has already been changed to whole days. In other words, only make the change for those tasks that have not yet been converted over to whole days. Okay? So I just got to test this real quick. So if I make this 4.3 again like that, and now if I go to Save My Project, if this worked, that ought to around it and it did, notice that work. So it’s now working with the Before Save event. Pretty cool.
Ira Brown: All right, so let’s move on to our next example. Our next example is, using automation to import resource rates from a Microsoft Excel file. Okay, well, let’s talk about why you might want to do something like that. Well suppose you have a project, let me go ahead and open up another project here and let’s say that you’ve assigned the running of these reports for this project to an intern that maybe just started working for your company and if we go to the Resource sheet here, there’s all the people, all the resources that are in this project. Let’s say that we didn’t want it to be common knowledge as to what the rates were, the billable rates for each of the resources that are working in this project, we wanted to keep that confidential, right? So notice where it says standard rate, zero, right? So that way if someone’s opening up this project, they’re not going to see what those rates are. But for the person who needs to run the report, they need to have some visibility to that.
Ira Brown: So what we’ve done is, I’ve created this… let me just go to my C Drive, my MPUG, I’ve created this Excel file called Project Widgets Resource Rates. Let me go ahead and open that up and just show it to you. Okay. So you can see there’s just two columns in there, resource name, hourly rate. So you have all the resources that are in my project listed here and their hourly rates listed there. Okay? So what I would like to do is, I would like to basically say, you know what? Let’s write a routine that’s going to run this report that I need to run that has the cost information in it but prior to running the report, let’s go out to this Excel file, let’s grab these rates, let’s temporarily put them into our project, let’s run the report, and then we’ll remove those rates from the project. So we’ll only keep them in there just long enough to generate the report and then we’ll remove them so that way people who go back into that project in the future, will not see anybody’s hourly rate. Okay? So, that’s what we’re trying to accomplish here.
Ira Brown: So let’s take a look and how that can be done. So I’m going to go back to the visual basic editor and this is one of those examples where, you won’t be able to just record a macro like this. This is where you get into a little more advanced knowledge of the macro language as far as doing things like this. So let me just take you through conceptually what this is doing. First of all, notice it’s called Import Resource Rates. That’s the name of the macro and then we have a bunch of different variables here that we’ve created. I won’t get into all of them here, but just generally, here’s what’s happening, this line of code right here, set objXL Excel=New Excel.Application. So what that does is that is how we programmatically start Excel. Just that one line starts Excel, but then the next line says, we don’t just want it to run, we want it to be visible on the screen so let’s go ahead and make Excel visible.
Ira Brown: Now it’s possible that maybe you don’t want it to show on the screen maybe you actually do want this to be more behind the scenes so you don’t necessarily have to put that line in there. Okay. The next thing we’re going to do is, we’re going to go ahead and open up the file that has the resource rates. So we do this thing called objXL workbooks open and that’s the name of the file and it’s exactly kind of what it sounds like it’s doing, right? It’s taking… it’s doing the open method of that workbook and here’s the location of where that is. All right.
Ira Brown: Then we set a reference to the worksheet, in other words, the active sheet, we’re going to refer to that as the CurWorksheet. I then have this line of code that I think is pretty useful that says, find the number of rows that are in that Excel sheet and that’s what this intLastRow tells me, that’s how many rows are in Excel. Because remember I may be adding new resources down the road so we don’t want to hard code that, we always want to be able to just get what that number is. Okay. Now without getting into all the details here, this little section of code here just basically says for each of the resources in the project, go ahead and find me that resource in Excel and then we’re going to set the standard rate for that resource equal to the value that it found in Excel, which is that line right there.
Ira Brown: Then once we’ve done that for all the resources, we’re going to run this report called Cash Flow. Once the Cash Flow report has been run, then we’ll clear the rates by setting them all back to zero, just like it’s doing here. Notice how we’re looping through each resource and then we show a message that the Cash Flow report has been created successfully. We then close the Excel workbook and then we programmatically close Microsoft Excel. So we’re ready to give this a try. Let’s go back to our project. Let me just go back to the Gantt chart. There we go and I’m just going to go ahead and say View Macros. Let’s see, this is Import Resource Rates One and then click Run.
Ira Brown: Just notice how it opened up Excel, generated my Cash Flow report, Cash Flow report has been created successfully. Notice that we see all this dollar information in here, I’ll click Okay and notice the dollars immediately go away because we’ve just cleared out the rates from the file. So it accomplished what we set out to accomplish, we ran the report, we just safely removed all the dollar amounts, the rates and now anybody else who goes into this project later on, will just see zeros for those rates. So pretty cool doing exactly what we wanted it to do. All right. So we have made it through all of our examples here, folks.
Ira Brown: So the one final topic that I want to cover with you is, you’ve done some pretty great things and they’re really useful for yourself and you’re on your desktop with Microsoft Project, how would you go about distributing these macros as well as your ribbon to other users? Well, here’s how we go about doing that. These are the high level steps and I’ll demonstrate it for you. So what we’re going to do is in Microsoft Project we’re going to use something called the Organizer and we’re going to basically use that to copy the macros into a blank project file. So, we’re just going to go into project one for example, and we’re going to copy the macros into project one. We’re then going to export the custom ribbon for Microsoft Project. I’ll show you how to do that.
Ira Brown: We’re then going to go to this other user’s computer and we’re going to open up the project file that we just created that contains now those macros that we copied into it, right? We’re going to open that up on another user’s computer. So you could maybe put this in a central location in SharePoint or you could email it to them, however you want to get it to them is fine. Then on the other user’s computer, we’re going to go back into the Organizer. This time we’re going to go in the opposite direction, we’re going to copy the macros from your project that contains the macros into what’s called the Global.MPT. That is kind of the local configuration file that’s used by Microsoft Project to store macros and views and tables and filters and that kind of stuff.
Ira Brown: So that’s where macros live in a user’s environment. We’re going to copy them from your project into the Global.MPT. Then finally we’re going to import the custom ribbon that we had exported from your computer into the user’s computer and that’s all there is to it. So let’s see how that is done. So first of all, let’s go back to Microsoft Project. Let me just go to a blank project here called Project One and now I’m going to go to File and then I’m going to go to Info and then click Organizer. Then I’ll click on Modules and all of the code that we did today are in those four… actually, yeah, why not? It’s all those four modules right there. So I’ll just go ahead and highlight them all and then click copy. Notice how it just copied the modules into Project One.
Ira Brown: Now, if there were any views or tables or anything else that were needed in order to run those macros, you could do the same thing here by just using the organizer to bring them into Project One. In this case, I’ll just bring over the modules, click Close, right? Now what I would do is I would save this project and I’ll go ahead and just say Browse and I’ll just save it out to my desktop and I’ll call this my MPUG Project Widgets Macros. Okay. That’s the file I would take with me to the other user’s computer or put it down in SharePoint and we can grab it from there. Now, how do you get the ribbon buttons to export? Well, we right click where it says in the ribbon area and then we go to Customize the Ribbon and you see now we have an Import/Export button here. So you click that and then you say Export all customizations. I’ll go back to my desktop here and it just defaults to Project Customizations.exportedUI.
Ira Brown: You can really name it whatever you want, just don’t change the extension, but we can call this Project Widgets MPUG. Okay, then click Save and it just exported those ribbon changes. So now when you go to the user’s computer, what you would do is you’d right click, Customize the ribbon and this time you would say, Import customization file, browse to the location where you saved it, there it is. You select it, click Open. Do you want to replace the existing ribbon? So remember, if the user has customized their ribbon, this is going to override any customization they’ve done so you just be careful when you do this and just say, click Yes. Click Okay and then you will then notice that they will have the Widgets tab along with the buttons and that’s all there is to it folks.
Ira Brown: If you’re using project server or project online, then the technique for distributing these would actually even be easier, you would put them in what’s called the Enterprise Global rather than the Global.MPT as we’re doing right now. But if anybody has any questions about how to do that, you can feel free to reach out to me and I’ve been known to get on these kinds of teams meetings from time to time to help people out. So, if you need any kind of support with this, just let me know. With that, let me just go back to the PowerPoint for a moment and I’ll just wrap up my presentation.
Ira Brown: First, I just want to thank everyone for attending the session today. I hope you enjoyed it and I hope you learn some things that you may be able to put to good use. So, I want to remind you that if you send me an email to email@example.com, a couple of things will happen. One is, you will be entered into the drawing to receive a free copy of the Driving Path Widget. We’re going to be giving away three free copies of that and it’s a really, really useful tool. Shout out to my colleague Ken Jamison, who… he was kind of the brains behind developing that tool and it’s one of our most popular widgets that we sell. So again, three free copies, just send me an email. You will also receive a copy of the presentation along with all of the macros that we created during the presentation.
Ira Brown: So please send me that email and we’ll get that to you. We’ll also send you our newsletter that we send out about four times a year. Don’t forget to visit our website at projectwidgets.com. You’ll see all of the other widgets that we have available out there. I’d love to work with you folks. If there’s anything that I can ever do for you in terms of automating some of the things that you do within your organization, please feel free to reach out and we’ll figure out how we can work together. So with that, I’m going to turn it over to Kyle. Thanks again everybody. Have a wonderful day.
Kyle: Thank you so much Ira. That was a great session. We really appreciate it. Also, thanks for the offer on the free widgets as well as the macros that you’ve created today. We really appreciate that. Anyone watching live, if you click that screenshot icon at the top of the viewer window, it’ll take a screenshot of Ira’s slide here and allow you to save that to your computer for reference later on.
Kyle: Everyone claiming the PDU for today’s session, I’ll get that info on the screen for you now. You should see that show up in just a second here. If you missed any of today’s session or would like to go back and review, the recording of part one is available now and part two will be available in just a couple of hours and we’ll send you an email with a link to both of those so you can watch part one if you haven’t or catch up on part two. MPUG members have full access to our PDU eligible library upon demand webinars on mpug.com.
Kyle: We also have quite a few great sessions up on the calendar, open for registration now. I chatted over a link to access those and save your seat for these upcoming live sessions. Beginning of May 30th, Raphael Santos will return for a session on Enhancing your Project Online and Power BI Reports with Custom Visuals and the following week then Ben Howard will return for a session on Microsoft Planner Deep Dive. Both of those are 45 minutes sessions and available now along with quite a few others so we hope to see you there for those sessions and that does it for today.
Kyle: So once again, Ira, thank you so much for your time and for sharing your expertise with the community. We really appreciate it. Thanks to everyone that joined us live or is watching this on demand, we hope you enjoyed the session and we’ll see you back in a couple of weeks for our next live presentation. Thanks.