Please find below a transcription of the audio portion of Ira Brown’s Beyond Macros Revisited: Automating Microsoft Project for Non-Techies 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. My name is Kyle and I’ll be the moderator today. Today’s session is eligible for one PMI/PDU in the technical category, and the MPUG activity code for claiming that is on the screen now, and that’s MPUG 030420. Like all webinars, the recording of this session will be posted to mpug.com shortly after the live presentation wraps up.
Kyle: And all input members can watch these recordings at any time and still be eligible to earn the PDU credit. All the sessions you watch on demand can be submitted on your webinar history and the live sessions you attend are automatically submitted. Within your history, you can print or download your transcript and certificates of completion including the one for today’s event. You can access your member training history by logging into mpug.com, click on my account and then click on the webinar reports link.
Kyle: If you have any questions during today’s presentation, please don’t hesitate to send those over using the chat question box on the go to webinar control panel. I do plan to answer those for you after each example during the session today. All right, we’ll go ahead and get started. Now, we’re very happy to welcome back Ira Brown today. Ira is the founder and president of Project Widgets. He’s a leader in the field of project management and is a recognized Microsoft Project expert.
Kyle: He has over 25 years of experience helping organizations implement enterprise project management solutions. Ira is the widely published author of several Microsoft Project books and a frequent MPUG webinar presenter on the topic of automating Microsoft Project. With that said, I’d like to welcome you back Ira, and at this time, I’ll go ahead and hand it over to you to introduce yourself a bit more and get a start with today’s presentation.
Ira Brown: All right, well, thank you very much and I’d like to welcome everyone to our webinar today, and the title is called Beyond Macros Revisited, automating Microsoft Project for non-techies. Again, my name is Ira Brown and I think we’re going to have a lot of fun this afternoon, if you’ve never dabbled too much with the macros and Microsoft project, but we’re always curious about how you could perhaps use them to make your jobs easier, this is definitely the session for you.
Ira Brown: I think you’re going to really learn something fun and who knows? This may [inaudible 00:02:32]. Just to tell you a little bit about Project Widgets. We are a Microsoft Project Gold Partner and solution provider, and our core competency is in the project and portfolio management with Microsoft Project. I’ve been at this now for well over 25 years, and when I first started, there wasn’t even a project server, it was just standalone Microsoft Project.
Ira Brown: And over the years, we’ve seen the product evolve quite a bit, having the project server product to any version of Project that you’re using. Whether it’s Standalone or Project Server or Project Online, so it’ll be something you might develop in a standalone copy of Microsoft Project. Maybe it’ll be so good you’ll decide you want to roll it out to people in your organization via Project Server or Project Online. We’ll even talk about how to do that today.
Ira Brown: What do we do with Project Widgets? Well, we work with companies to help implement Microsoft Project systems. We try to understand what your requirements are and we design a system through a combination of configuration and frequently through custom solutions that we develop to make people’s jobs easier, to help them get done what they’re trying to get done with a lot less effort, often at the click of a button.
Ira Brown: This topic today fits right in with that. It’s, how do we make things easier to do in Microsoft Project so you can focus on the things that… In terms of getting your job done in less on how to get things done in Microsoft Project. Let’s get right into it. Our agenda for today is simply, we’re going to see how we can begin to assign a macro to a button in the ribbon. Now you want to record the macro, but we want to be able to provide an easy way to launch that macro, so we can actually just put a button in the ribbon and click that button and it’ll run your macro.
Ira Brown: And then we’re going to go through several different examples of creating macros. The first one we’ll do together is this “zoom in/zoom out” macro to adjust the size of the text in your project. We’ll also see how we can create automation to count the number of display tasks in your project after a filter has been applied. And for each one of these examples, we’ll talk about why you might want to do something like that. In other words, why would that be a useful thing to do? Because if it’s not useful, we probably wouldn’t want to automate it.
Ira Brown: We will create automation to increase or decrease the work for a task by a specified percentage. I’ll even show you how we can go about creating a status report in Microsoft Word directly from Microsoft Project. And we’ll close out with how we go about distributing your macros and custom ribbon to other people within your organization, okay? And by the way, at the end of the session today, I’ll be showing you my contact information and all the macros and everything we create, including this presentation, I’ll make available to you.
Ira Brown: Just shoot me an email and we will send out all of the materials from this session, all the macros, and we even have a couple of free giveaways at the end of the session which I’ll tell you a little bit more about as well. All right, so let’s talk about why would we want to do this in the first place? Well, sometimes things that we do in Microsoft Project can be tedious or time consuming, error prone, and that’s a good reason to create a macro, to go create some automation to make getting that done easier.
Ira Brown: And that’s what this is really all about. Now, in order to create a macro, in order to create automation for something, you first would have to think about how would you accomplish this manually? We can’t automate anything in Microsoft Project that can’t also be accomplished manually, because we’re just really programming the application. You’d have to how do you do that manually? And then once we know that, then we can think about how we would try to program that, using the macro language.
Ira Brown: Oftentimes, especially when you’re getting started, the best way to see what the macro code looks like is to just record the macro. Take advantage of that powerful recorder that you have in Microsoft Project to actually create the macro. And then once you get really good at it, once you’ve been doing it for 25 years or so… Oh no, it doesn’t take that long, then you could actually write a lot of this yourself without even having to record it.
Ira Brown: But I can tell you, even today, a lot of times just to get the code, it’s a lot easier to record the macro to at least get a portion of the code that you’re looking for than to have to type it out from scratch, so I still use this technique today. And then once you’ve recorded the macro, then you edit it as needed, and we’ll see some examples of that. To make the macro easier to run, we’re going to see how we can assign it to a ribbon button. And with that, we’re going to get right into some examples, okay?
Ira Brown: The first thing we’re going to take a look at today, “zoom in/zoom out” macro to adjust the text size in your project, okay?. First question I would have is, why would you want to do that in the first place? Why is that something that would be useful? Let me start on Microsoft Project, and I’m just going to be running the standalone version of project today. However, all of these things that I’m creating will be available or usable within any version of Microsoft Project. All right. Okay, so the question as I mentioned is, why would you want to do this in the first place?
Ira Brown: Well, Microsoft Project has a “zoom in/zoom out” feature right now. However, the “zoom in/zoom out” feature of Microsoft Project doesn’t actually zoom the text. It only zooms the Gantt bar. Let me open up a project. We’ll be working with this project here for today. I’m going to go to file, new and I’m going say, let’s create a project from a template, and I was looking through the different templates that were available and the one that caught my eye… Well, let’s see here.
Ira Brown: Why don’t I see it now? Management institute. Okay, well I don’t see the one I… let’s go with marketing campaign plan. All right, so I’ll go ahead. These are templates that are available within Microsoft Project, and we’ll just go ahead and create a brand new project utilizing that template. For the purpose of what we’re doing here, I’m just going to turn off the timeline. Okay, so there’s a lot of useful templates that are available that you can download right from Microsoft.
Ira Brown: This is one called the marketing campaign template. It will work perfectly fine, I think for what it is that we’re doing. Okay, so notice at the bottom of the screen… Or actually I’ll go to the ribbon. If we go to the view tab, there’s this button here called zoom. And when I say zoom in, it’s zooming in on the timescale, right? Or when I say zoom out, it’s zooming out of the timescale. But sometimes, it may be necessary to apply the same concept to the text within your project.
Ira Brown: Maybe you’re presenting in a large room and the text is small and you need to make it bigger for people to be able to see from across the room. There’s lots of reasons why you might want to easily increase or decrease the size of your text. And it’s the thing you can do in Excel, so it’d be nice if we can do something like that in Project as well. That’s what we’re going to set out to do here. As I mentioned earlier, the first thing you need to know is or be able to describe is, how would you do this manually in Microsoft Project?
Ira Brown: Well, one of the ways you could do it would be using this feature that we have called textiles. If I go to the format tab and I click on textiles, it brings up the textiles dial box and this will allow me to apply a certain font size to my project. For example, I think… Well, let me actually see what the current size is, go to the task tab. Basically everything looks like it’s 12 point right now. If I go back to textiles, I’m going to say, “Let’s make that 14 point.” Yes, and it worked. I just increased the size of my text, okay?
Ira Brown: What if I want to decrease it? Well, we’ll do the opposite, right? And this time maybe we’ll take it down to 10, and we’ve just decreased the size of our text. That’s how we would accomplish it manually, but I want to actually create some automation so I could perhaps click a button and I can say zoom in or zoom out and click it as many times as I want to keep on zooming in or zooming out. It’s to make this even easier to do, okay? How do we get started, now that we have a general idea of what we want to try to accomplish?
Ira Brown: What I’m going to do is I’m going to record a macro, and I’m going to record that step that I just showed you of running the textiles dialog box. How do we go about recording macro? Well, we go to the view tab. Over here on the right, we have macros and we’re going to say record macro. You want to give your macro in name here? Maybe I’ll just call this zoom text, all right? Can’t have any spaces in the name, and you could put a description in if you want. I’ll just leave that default for now and I’ll click okay.
Ira Brown: We are now recording our macros, so basically anything that we do from this point forward is being recorded by the macro recorder. I’m going to just go ahead and say, I want to change my text style and I will pick a larger font. I’ll just pick, say 14 and that should do the trick. Click okay and something probably just got recorded. Let’s keep going here. Let’s try it one more time. I’m going to hit textiles again and this time, I’ll take it down to 12 and click okay. It looks like it did what I wanted it to do.
Ira Brown: I’d like to see what actually was generated by the macro recorder. To do that, I’m going to go to the view tab. I’m going to click macros again. This time I’m going to say stop recording. The question is, how do we go about seeing our macro code? Where is it right now? Well, it’s in what’s called the Visual Basic Editor. If I drop down macros again, I can choose visual basic, okay? When we record a macro in Microsoft Project, we are actually generating what’s referred to as VBA, visual basic for applications code. That’s the macro language of Microsoft Project.
Ira Brown: And not only is the macro language of Microsoft Project, but it is the macro language of Word and it’s the macro language of PowerPoint and Excel and Access. And there’s a bunch of other products out there that use VBA as well. It’s built right into the application. You could not only automate Project, but you could take these same principles and apply them to Excel and create macros and automate things in Excel or PowerPoint. Not only can you do that, but you can get Project to talk to Word or what if you— Maybe you want to create an automated slide deck from information in your Project plan.
Ira Brown: Well, you can do it and in fact, we’re even going to be covering an example today of how we can create a status report in Microsoft Word directly from the information in Microsoft Project. Run a close out with that one since that’s a biggie. All right, but just to keep things moving along here, this is our zoom text macro. Now, when I went into the visual basic, notice, it says here, project global, global.mpt. Well, for those of you that are not familiar with global.mpt, the global.mpt is a file that’s used by Microsoft Project that is the local configuration file for everything that you do in Project.
Ira Brown: If you create custom views or tables or filters, and as well as this macro code, the VBA code, that all lives inside the global.mpt. And the macro that we just recorded is contained within what is called a module. A module is nothing more than a place that these macro routines get… or could live, basically. There it is. We created that macro, we called it zoom text, right? Basically, I just double clicked on where it says module one. I can rename that module anything I want. In fact, I probably ought to do that.
Ira Brown: I’m going to go to what’s called the properties of the module by pressing the F4 function key. And instead of calling it module one, I’m going to call this Mod MPUG PW for Project Widgets, okay? Here’s our macro. Well, let’s just see if we can figure out what this is doing. Apparently, the first time I ran the macro, it recorded this line right here. Text styles, 32X, item equals zero. I’m not quite sure what that means, but I have a pretty good idea that means that apply this to all of the texts, not just milestones or something else. It’s basically across the board.
Ira Brown: I won’t touch that one, but this one looks probably like the thing I want, right? Size: =14. Remember I changed the font size to 14? Well, that’s what that 14 means there. And then when I ran it a second time, remember I reduced it to 12? Well, that’s what that line there is doing. It’s the exact same line. Apparently, just by changing the size, we can accomplish what we want to accomplish. Okay, so I think I have a general idea now of what I want to be able to do.
Ira Brown: Let’s just talk about how we really want the solution to work. When I click my button to zoom in, every time I click it, I want to be able to increase the font size by one point here. I want to go from 14 to 15 to 16, et cetera, and then when I zoom out and I said I want to reduce the font size, I want to again, go the opposite direction by one. Here’s the technique that we’re going to use to do that. In order to be able to keep track of where we are right now in terms of what the current font size is, we need to create what is referred to as a variable.
Ira Brown: And in fact, it’s going to be an integer type variable, and I’ll show you how to do that. What we want to do here is we want to type in dim, D-I-M, which is short for dimension. We’re basically declaring our variable now. I’m going to say Dim intFontsize as Integer, all right? What does that really mean? It’s not all that scary. We’re basically saying we’re going to create this variable that we’re going to use, and we just named it, and because it’s an integer, I named it Int in the beginning, just makes it easier to understand the type of variable.
Ira Brown: And then what will that variable be used for? Well, it’s going to be used to store integer values, right? [inaudible 00:18:43] And we’ll remember, we’ll introduce our zero, one, two, three, four, five, all that stuff. And you can have negative numbers as well. That’s where we’re going to store the font size, okay? And what we’re going to do is we’re going to modify this routine right here and instead of just calling it zoomtext, I’m going to call this zoomtextIn, right? Because that’s going to be our zoom in, and then I’ll create another one just like it, just like copying and pasting it called ZoomTtextOut.
Ira Brown: Okay, so what I want to say is this. I want to say that the first time that we run this, and this is true for any integer variable, the default value for integer variable, the first time you run this macro in a given session is always zero. What I want to say is, I’m going to put some logic in here that says if intFontSize, that’s our variable to recreate it is equal to zero then so we need to actually initialize it to a value other than zero. I’m going to just pick something like 12 because remember, that’s what we started with.
Ira Brown: I’ll just say intFontSize equals 12. And then whenever you have an if, you have to finish it off with an End if, okay? That’s really all we’re doing here, is we’re saying that if that integer variable is zero because it’s going to start off its life as zero, then set it initially to 12, all right? And once we’ve done that, now what we’re going to do is we’re going to use that variable. Instead of hard coding that 14 there like that, we’re just going to put in the integer here. I’m just going to say intFontSize, okay?
Ira Brown: Now let’s just kind of review it and make sure that it looks reasonable. When we run this, if it’s 0, we initialize it at 12 and then we apply it to here. But I think I forgot one thing. What’s important is I have to increase it by one, right? So how am I going to do that? Well, I’m going to say intFontSize is going to be equal to intFontSize plus one, right? And what you’re going to see me do here is also pretty important. I’m going to start off by typing in an apostrophe and then I’m going to say increase font size by one. And what I just did was I put in a comment.
Ira Brown: A comment is nothing more than a way of documenting your code that you’re writing. And I can tell you from lots of experience is that this makes perfect sense to me right now, why I wrote the code the way I did. But if I look at this in a week from now, it’s going to make a little bit less sense and I’m going to have to try to figure out why I did what I did. And sure enough, if I look at this in a year from now, I won’t maybe understand that at all. My recommendation is when you’re getting started and really all the time you want to comment everything that you’re doing.
Ira Brown: Here I’m just going to put another comment in. I’m going to say increase or apply. I’ll just say apply increase to text styles function or something like that, right? Now when I read that later on, I’ll understand exactly what I’m doing. All right. The other thing that I would highly recommend is before you… we’re going to run this in a second, before we run it, always a good idea to save your work. And we can do that very simply by just hitting the little save button up in the toolbar here and it’ll write that back to the global MTC. Okay?
Ira Brown: I think we are ready to give this a try, so let me show you how to do it. What I’m going to do is, since I’ll be going back and forth between VBA, is I’m just going to minimize the VBA window for analysis like that. And how do we go about running the macro? Well, there’s a few different ways of doing it, but the technique we’re going to look at now is just by simply going to the macros button, dropping it down, saying view macros. There’s the one we just created and I’m going to go ahead and run it.
Ira Brown: And notice how the font just got a little bit bigger. I’m going to run it again. Got a little bit bigger. Run it again. Okay, so it appears to be doing what we want it to do. That’s pretty cool, but we want to be able to go in the opposite direction also. Let’s go back to VBA and there’s a just good half of my task bar. There it is. And what we’re going to do is we’re simply going to copy this zoom text in here and I have a feeling you know what I’m about to do. I’m going to paste it on in and I’m going to change the name of this to ZoomTextOut. All right.
Ira Brown: And then instead of increasing the font size by one and instead of plus one, I’m going to make it a minus one. Really, that’s the only change I have to make other than maybe changing my comments would probably be a good idea. This time I’ll say decrease font size by one and apply the decrease here. Again, let’s save it. Let’s minimize the window and let’s test it out. We’ll go to macros, view macros. And this time I’ll say zoom text out and it just made it smaller, zoom text out again, just made it smaller, so it appears to be working. This is really cool.
Ira Brown: But if you think about your end user experience, you don’t want to have to ask your users to have to run the macro by going to macros, view macros, bring up list of macros and running from here. I mean, it’s not the worst thing in the world, but we should be able to make it even easier for people to do that. Okay? The way we’re going to do that next is we’re going to go ahead and we’re going to add these macros to the ribbon in Microsoft project. Here’s how we go about doing that.
Ira Brown: I’m going to go ahead and right mouse click in the ribbon area and I’m going to say Customize the Ribbon. All right, and before I get started here, I was going to reset my… I made a couple of changes early just to test some of this out, so I’m just going to say reset my ribbon, reset all customizations. Be careful when you do that, by the way, if you don’t want to do that if there’s anything that you would lose, but I have nothing to lose here right now, so I’m going to say yes and then click okay.
Ira Brown: Okay, so now I’m just starting with a totally clean ribbon and now I’ll do that one more time. Right click, customize the ribbon and what we’re going to do is we’re going to start off by adding a new ribbon tab to the ribbon. How do we do that? Well, down here where it says new tab, I click it, there’s the new tab and then when you create a new tab, it also creates a new group. We’re going to rename that new tab. Oh, how about if we call it Project Widgets? There we go.
Ira Brown: And under the group, we’ll right click that also and say rename, and let’s call that MPUG. Okay? Now we have a new ribbon tab, a group within the ribbon tab, and now we’re going to see how we can assign those macros to that group. And the way we do that is we go to where it says choose commands from, up top there. We dropped that down and we pick macros. I’m going to pick zoom text in, click add, zoom text out, click add. Notice how it just added it to the MPUG group.
Ira Brown: What I typically like to do is I like give it a little bit of a friendlier name and a nicer little image. I’m going to just click where it says rename. This is my zoom in, so I’m going to just call this zoom text in and let’s find ourselves a nice little picture to put on that. Let’s see, zoom in. I think that one could be a good one. Click okay. And then we’ll do the same thing for zoom out. Call it ZoomTextOut and I’ll pick that arrow going in the down direction. Click okay, click okay.
Ira Brown: All right, so notice that we now have the project widgets ribbon tab with two buttons, and now this is the really exciting part. We get to try it out, see if it works. I’m going to go ahead and click zoom in and I’m just going to keep clicking it. Look at that. How about that? Let’s try ZoomTextOut and now we’re going in the opposite direction and it’s doing exactly what I set out to do. This is pretty exciting. And there you go. We just built our first macro and we have a little mini widget that now exist that we can use whenever we want. Okay?
Ira Brown: Let’s see what else we can do here. Let’s move onto our next example. And our next example is going to be braiding automation to count the number of displayed tasks after a filter has been applied. All right. I guess as always, I’ll ask the question, why would you want to do this? Well, maybe you want to get a sense of how many milestones are there in my project and you don’t want to actually have to go and count them row by row. That’s one example of why this could be useful.
Ira Brown: And there’s probably a lot of other examples where maybe you apply a filter and you want to be able to know how many tasks are now displayed after you’ve applied that filter. Let’s give this one a try. Okay. We’ll go back to Microsoft project. All right, here we go. And first of all, again, I always ask the question, would you do it manually? Well, let’s think about it. We know that a milestone in Microsoft project is a zero day duration task, right? How do we isolate the milestones, I guess?
Ira Brown: Well, we could apply a filter, right? We could filter on this duration column to say, only show me those tasks that are zero days. All right? I think I have a general idea of how I would at least get started doing this. Let’s, once again, return on our macro recorder. Here we go. Remember we go to view macros, record macro, zero, zero days, click okay. I am now seeing all the zero day activities, but I’m also seeing their current tasks. In other words, their summary tasks as well.
Ira Brown: I don’t want to see them right now because that’s not going to help me count the milestone. I’d like to somehow suppress the summary tasks. Well, to show you how you do that. Again, it’s if you know how to do it manually, then just make that part of what you record. You notice how when I click on the format tab, there’s this little checkbox here that says summary tasks. I’m going to uncheck that. Apparently, we just managed to isolate all of our milestones in the project.
Ira Brown: Now, we don’t have too many of them right now, so we could actually count them manually, right? We have 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13 milestones in this project. Now we know the answer. I think the macro recorder has done its job. I’m going to go ahead and turn off the macro recorder. View macros, stop recording. Now we’ll go back into the visual basic environment and notice that we now have a new module that it just got created, and here it is. There’s a bunch of stuff here now, let’s try to figure out what this is all about.
Ira Brown: Well, it turns out that I guess we can get rid of the comments. We don’t need them, so just deleting them. Now, what are all these rows right here? Well, remember I was kept manually counting how many milestones I had and I kept on moving my cursor down, but every time I did that it generated that select task field row. I don’t need any of them, so I’m just going to delete all of them. See, and it’s not a problem, but what we’re left with is a couple of things that I think will be useful.
Ira Brown: This line here is where we filter the tasks in our project to only show zero day duration tasks. This is called the set auto filter method. It’s actually referred to as a method. And what does it say? Well, for the field call duration, we’re using this filter type called auto filter in. I’m not even sure what that means, but I’m not going to touch it. But notice it says criteria, zero days. It looks like what this is doing is applying a filter to the duration field, and the criteria is it’s got to be zero days.
Ira Brown: And I would imagine that now that I see that this exists, I could probably change the name of this to something else, like I want to do something similar to the start field or the finish field, right? But I have a good foundation now. Now, if the macro recorder shows me that this technique actually exists. Let’s put a comment in here. Let’s say apply filter to show zero day duration tasks or also known as milestones. Okay, that’s our comment. This guy right here, this line summary tasks show, that is apparently how I was able to hide the summary tasks.
Ira Brown: Now, one of the things that you’ll need to understand when you record your macro is that it’s not going to get you 100% of the way there most of the time. Most of the time it’ll give you a good foundation, but then there’s some things that you have to know beyond just what was recorded. In this case, notice when I click at the end of summary tasks show and I press my space bar, okay? It provides what is called IntelliSense information about the summary tasks show method.
Ira Brown: What this is telling me is that there is one parameter called the show parameter, and you see where it says As Boolean, well Boolean is in the macro language or VBA simply true or false. That it’s a type of a state that something can be in. It’s something’s either true or something’s either false. What I need to do here is I want to say show colon equals false. All right? So that’s the syntax that we use to basically not show the summary tasks. I’m going to put a comment here, I’m going to call it hide the summary tasks. Okay.
Ira Brown: Now what do we want to be able to do? So presumably when I run this, it’s going to apply the filter and it’s going to hide the summary tasks. And now I want to somehow be able to count the tasks that are displayed on my screen. The technique that we’re going to use is we’re going to select all tasks in project, or I should say all displayed tasks, right? Because I just want to highlight or select the tasks that are showing as a result of applying the filter. The way we do that, we use what’s called the select all method. All right? That’s the command that that selects everything.
Ira Brown: I’m going to go ahead and when I run this, I’m going to actually do what’s called single stepping the code so we can actually watch every single line run and you can see exactly what it’s doing. Okay, so now what we want to do is we want to create another one of those variables. Again I’ll type in Dim and I’m going to name this Int because it’s going to be another integer, intDisplayedTasksCount. You can make up just whatever variable name sounds like. Again, when you look at this in a week from now or a year from now, it’ll make sense.
Ira Brown: So intDisplayedTasksCount as integer, right? That’s how we go about creating that variable, that integer variable, and this is the name of it. All right, so now what we want to say is we’re going to use that variable. We’re going to say intDisplayedTasksCount=ActiveSselection.Tasks.Count. Okay, so let’s dissect that a little bit. Active selection is simply what do you currently have selected in Microsoft project? And if any of them are tasks, then tell me what the count or the number of those tasks are that you have selected. That’s what this will tell us. How many tasks are selected after we’ve run that select all command. Okay?
Ira Brown: I’m just going to call this with a comment, get task count. All right? And now that we have that count sitting in this integer variable here, it’s not doing our users a lot of good unless we can tell them what that count is. So to do that, we are going to display what is called a message box to actually communicate that to the user. So here’s how that works. We’re going to say, message box and then you start off with the quotation mark, and you say something like the number of displayed tasks is, right? And then you close it off with the quotation mark.
Ira Brown: And now what actually contains that number? Well, it’s that integer, your variable that we typed in intDisplayedTasksCount. It’ll say the number of displayed tasks is and then that number. And then we want to… I’m using an ampersand here, oops, I forgot to put the ampersand. Ampersand is how we glue these together. Individual pieces of text, or in this case text and a number. The number of display tasks is and that number, and then we need another little piece of text here with the period in it to close it off, right? And that’ll be a nice sentence that the user will see.
Ira Brown: And then I’m going to type in a comma and then this is where I can tell the message box if there we maybe want to show a nice little icon with the message box. I’m going to just scroll through here and the one I like to use is called VB information. That’s that nice information icon. Okay? Let’s give this a try. Remember, we’re going to save this first. Okay, so here’s a technique that I think you’ll find really useful and taking advantage of a nice Windows feature that we have. Where I can take this code window, I’m going to just click it and drag it so that it occupies the right hand side of my screen like that.
Ira Brown: And then what I will do is I will do the same thing for the Microsoft project schedule. Just drag that over, oops, didn’t quite go right. Let me try that one more time. There we go. All right, so now and actually I can drag this so it’s a little bit less wide so I can see more of my code. Okay, good. All right, now let me turn off the filter that we currently have, right? I don’t want that actually running any longer, so I want to just reset this back to the way we started. I want to show summary tasks.
Ira Brown: Okay, so this was the starting condition of where things began, and now what I’m going to do is I’m going to run this macro line by line. Well, how do we go about doing that? Well, the technique that I like to use is to use the F8 function key, so I’m just going to… each time I press the F8 function key, it advances one line and the line that is highlighted in yellow is the line that we’re about to run. Let’s run this line right here and when I run that line of code, it ought to apply a filter to only show zero day duration tasks.
Ira Brown: Looks like it worked, right? But we’re still seeing the summary tasks. We want to turn those off. Let’s run this line. Look at that. It turned off the summary tasks. I now want to select all the milestones that are now displaying and the projects that will run this line. Again, I’m pressing the F8 function key. Notice how it just selected everything? I now want to get a count of how many tasks are selected. Let me run that line of code and now you’ll see if I hold my mouse over that Int. See how it says intDisplayedTasksCount=13. It’s already given me a little preview that it looks like it works.
Ira Brown: And now I’m going to press the F8 function key one more time, and sure enough, there’s my message box. The number of displayed tasks is 13. I’m going to click okay, and now we’re at the very last line here. This is looking like it’s doing exactly what we want. Now the one thing that we want to do out of a courtesy is, remember, we just applied a filter and we just turned off summary tasks. The nice thing to do is to return the user back to the way they were. I’m just going to say clear filter, and this is going… there’s a line here that I’m going to add called filter clear.
Ira Brown: And then I’m going to take this line right here where we… remember we had to hide the summary tasks. I’m going to now say show the summary tasks and set this to true, right? That always a nice thing to do, is if there’s any kind of cleanup you have to do to return the user back to their starting condition, make sure you do that as well. Okay, let’s save this. And once again, let’s add this to our ribbon. We’ll go back up to Project Widgets here, we’ll customize the ribbon like we did before, macros. There it is. Count the milestones. Let’s add it to the MPUG group.
Ira Brown: All right, I’ll rename it and I’ll call it Display Milestone Count, something like that, and that we just have to figure out a nice little picture we can show. This is sometimes the hardest part. Let’s see. I’ll just pick that guy right there and we click okay. All right, and let me just set this back to the way we had it so we’re starting off with the right data, and let’s click on our Project Widget. Oops, sorry. Project Widgets, displayed tasks count, milestone count. The number of displayed tasks is 13. Click okay. And notice how it undoes the filter and once again, summary tasks are displaying.
Ira Brown: Well, we are making some good progress here. I think we’re ready to move on to our next example. And our next example, let’s go back to PowerPoint for a moment. Creating automation to increase or decrease the work for a task by a specified percentage. Okay, so why would we want to do something like that? Let’s go back to Microsoft project and let’s talk about that. First of all, I’m going to insert the column here called Work. Okay, here we go.
Ira Brown: All right, so let’s say we’re starting off with this project and let’s say this came from a template, which you did, right? And whenever we do a marketing campaign at our company, these are the standard tasks that we follow, and there’s the marketing lead who is assigned to these tasks. And here’s what we think it typically ought to take for the marketing lead to accomplish what they need to accomplish.
Ira Brown: But let’s say for example, we go out and we hire a brand new marketing lead for our company, and this is an experienced person, but they’ve never worked using our methodology before, so all of these tasks will be totally familiar to them. We think that even though we know typically it might take 40 hours to get this done, our new marketing lead, we want to tackle on an extra 50% the first time they do it. Instead of 40 hours, we would want to see half of that applied. Another 20, so make that 60 hours. Or in the case of an 8-hour task, we’d want to make it a 12 hour task, right?
Ira Brown: You could see how it could be useful to… if we had some kind of automation to increase or decrease the work associated with a task by some kind of specified percentage. Okay? Let’s see how we go about doing that. Now that we have some experience in doing this, this time we’re not actually going to record a macro, this time we’re going to just write some of this from scratch. And in the interest of time, what I’m going to do is rather than having you watch me type it, I am going to copy and paste this in and then I will walk you through exactly what I’m doing.
Ira Brown: Okay. I’m just going to copy this, go back to project. And remember if you stick around to the end, everybody, you will get all of these code examples that we’re putting together here. Okay, so I’m going to paste this on in and this is called adjust work. And let me explain to you what this is doing. Okay? This first line of code here, we’re introducing something that you haven’t seen before. This is called an input box.
Ira Brown: An input box is a technique that we have, I’m sure you’ve seen it before, that will display a box as part of the user interface when we click a button, that will prompt the user to enter some information. In this case we’re going to show them the message, “Please enter the percentage to increase or decrease the work for the selected task.” That is what the user will see and then it’s going to allow them to type in some percentage. All right.
Ira Brown: Then what we do is remember we put that percentage in this variable here and this is when we haven’t seen before. It starts off with SNG. This is a different type of variable, it’s called a single. That’s the data type. Instead of integer, it’s a single and the difference is that instead of just being able to store a whole number, like an integer, it can store decimals as well. If I wanted to say something like 0.5 or 0.25 or whatever, a single variable can handle that.
Ira Brown: The next thing we’ll do is once the user types that in, we will set a reference to the selected task. By that I mean there’s probably some tasks that the user has their cursor on, like maybe this, let’s just pick this task right here. This is just a way of saying I want to apply this logic to the task that’s currently selected. That is set T, T is going to be equal to a task variable, set T=activecell.tasks. In other words, the T variable will represent the task that the user currently has selected. This is where we then calculate the change in work.
Ira Brown: What we’re saying is let’s take whatever the work is on that task and multiply it by the percentage that the user entered. But remember, if somebody types in, “I want to change this by 50,” and that represents a percent. We have to divide that 50 by 100 right? In order to get it down to the actual decimal that we want to use. All right, and then all we do at that point is we say now let’s take that incremental change and apply it to the work that’s already there. That’s basically saying T.Work, meaning set the work for the task.
Ira Brown: T is the task variable here, right? The act of cell.test. It the take the active task and take the work that’s already on that task, but then add to it this incremental change which we calculated here. Are we good? So let’s try running this and see what happens. I’m going to go back to the project. I’m going to run it as a macro just to test it. We go to view macros, view macros. I’m going to select Adjust Work and click run. Look at that. There’s our input box. Please enter the percentage increase, decrease the work for the selected task.
Ira Brown: I want to say let’s increase it by 50%. Now keep in mind that this is a very, very simple routine I’ve created. I currently am not supporting them, actually typing in a percent side. We need a little more time to do that together. For this example, just to keep it simple, assume that the 50 represents 50% and what we want our routine to do is say, well, 50% of the 24 is 12, so let’s add the 12 to the 24, so that will get us to 36. I hope this works. We click okay and look at that, 36 hours. That’s pretty cool.
Ira Brown: Now, what if this was the next project that the marketing lead worked on, and this time they are so good they don’t even need the 40 hours. We want to decrease the amount of work by 25%. Again, we’ll run the macro and this time I’ll say minus 25, meaning decrease the work by 25% of this 40 hours. Right? That ought to reduce it by 10, I think, so we should end up with 30. Let’s see what happens and sure enough, there it is. Okay. Yeah, I could definitely see how this would be useful.
Ira Brown: I’m going to add that to my arsenal of widgets here. We’re going to say customize the ribbon and then we’re once again going to pick macros, and there’s our Adjust Work, and I’ll add it on down here, and we’ll rename it. We give it a little bit of a friendlier name and maybe I call it something like Increase/Decrease Work and we have to just give it a nice little picture. Again, that’s usually the hardest part. I’ll just pick that one right there and then click okay. And there you go. Now I click the button and I’ll put in, let’s double it. I’ll just put in a hundred this time and it just doubled the work.
Ira Brown: Okay, so that’s working pretty well. I can’t believe how quickly this time is going by. I am going to just quickly show you one more thing that I’m not going to have time to actually step you through exactly how it works, but I just want to give you a sense of how powerful VBA can be. We’ve developed some fairly simple things, but here’s an example of something that is a little more sophisticated. This is what’s called a user form. And I’m calling this the Status Report Widget and I’m actually prompting the user for several things here.
Ira Brown: Like tell me the start date of my report, the finish date for my report. Select the overall status that I want to show on the status report, like a red, green or yellow, put in who the project manager is and then actually build this in Microsoft word. Let me just give you a little preview of this. Again, I’m not going to take you through this line by line, but you can see we’re doing things like we are actually programmatically launching Microsoft Word with that one line of code and then we’re making it visible.
Ira Brown: And then we’re opening up our template file here. And then we’re really basically filling out the document by bringing information over from Microsoft project. Okay? Let’s see if we can get this to work. First of all, what I want to do is I want to set the baseline for my project. Now I’ve set my baseline. I also want to include only certain activities in this report. Maybe I just want to include the milestones. I’m going to, once again, filter for milestones. There we go. And I’m just going to set this flag here to yes.
Ira Brown: Basically what I’m saying is only show tasks that are flag as flag 10 equals yes, that’s all I can control what goes on my status report. Those are the important milestones that I want to be able to show. And then let me also highlight a few tasks here and set their percent complete equal to 100%. I’ll do that just by going here and I’ll just say 100%. Okay. I really have to keep my fingers crossed on this one. I’m going to go ahead and run this, and there’s my form, the reports start date, I’ll put that in is 3/1/2020. The report finished date, I’ll put in at the end of the month. 3/31/2020.
Ira Brown: The overall status for my project is green and the project manager, I’ll make myself the project manager. All right. Keep your fingers crossed everybody. It’s the only way this is going to work. Let’s go ahead and click okay. We’ve just launched Microsoft Word and it’s now filling out the status report automatically with information from the project. Notice, I picked green. There’s my overall status, there’s the project manager, Ira Brown. There’s my key tasks and milestones that I flagged. Tasks that were completed this period. Tasks that were scheduled to be completed but did not.
Ira Brown: Again, all kinds of cool things you can do. These are just a couple of examples. We are just about out of time. What I’d like to do at this point, I know we only have a few minutes left. Let me show you some information here to receive a copy of today’s presentation. Here’s my email address. Ira.email@example.com. I’ll send you all the macros, the presentation. I also invite you to go to our website to see all the different widgets that we have, including some free ones you can download.
Ira Brown: And for three of you lucky folks who will send me an email, you’re going to be entered in our drawing. We’re giving away three free copies of our Driving Path Widget, which is one of our most popular widgets that shows you select a task and it shows you the driving path that led to that task being scheduled the way it is. It’s pretty useful. That was developed by Ken Jamison. Shout out to Ken who’s on the call today from the Project Widgets team. And I just want to ask you if you have any questions, we do have a few minutes at the end here. Kyle, do we have any questions from anyone on the call today?
Kyle: Thanks so much Ira. And before you change the screen, I just want to remind everyone there’s a screenshot button at the top of the viewer window so you don’t have to rush to jot this down. Just click that button and it’ll save a screenshot of Ira’s contact info right to your computer. We did get a couple of questions and we don’t have much time here, but two are on the same thing. I was regarding the previous example and they were asking if you can apply the increase or decrease work example to multiple tasks by selecting multiple cells to begin with.
Ira Brown: Yes, you absolutely can. And what I will do is I will include that example in the code that I send out. We’re basically going to just add a couple of extra lines of VBA code to say not just for the one task, but for all selected tasks. Absolutely, you can do it and I’ll send you the code on how to do it.
Kyle: Awesome. That’d be great. I appreciate that. And then one more question that came in. Azwa is curious if there’s a VBA reference for functions specific to project that’s out there.
Ira Brown: Well there is some good information on the web. Microsoft has all this documented in all these different methods. I’ll also give a shout out to a guy that’s been in this business for a long time, a guy named Rod Gill who did write a book on VBA for Microsoft project. I believe you can still get that book and it’s a really good book. Those are a couple of different references that you can call upon to learn more about the macro language.
Ira Brown: And of course you can also contact Project Widgets where that’s what we do. That’s how we all make a living here is we help organizations create these kinds of solutions.
Kyle: Thanks Ira. And I think that does it for questions and takes us just about to the end here. I wanted to thank you for a great session today and especially for making the materials available to all the attendees. We really appreciate that.
Ira Brown: Sure, happy to do it. Thanks a lot everybody.
Kyle: Thank you. Yeah, that was a great intro to the using macros. We really appreciate your time today, Ira. And those of you that are claiming the PDU code for today’s session, I’ll get that info back on the screen momentarily. The code for claiming the one technical PDU is mpug030420, and that’s on the screen. Now if you missed any of today’s session or would like to go back and review the demos that Ira showed us, the recording will be posted at mpug.com later today and you’ll receive an email in just a couple of hours with the link to that. MPUG members have access to the full PDU eligible library on demand webinars on mpug.com.
Kyle: We also have some great sessions coming up on the calendar. The next two are on the screen now. Next week on the 11th, Dale Howard will return for a session on Understanding Calendars and Scheduling in Microsoft Project. That’ll be next Wednesday at noon, Eastern. The following week, Sai Prasad will join us for a session on Making Project for the Web Smarter. Those of you that are using the new offering from Microsoft, this is a great session to maximize your abilities with that tool.
Kyle: Those sessions are available now. I am chatting over the link to register for those and the others that we do have on the calendar. I hope to see you there. And that does it for today’s session. Once again, I’d like to thank you Ira for your time and for sharing your expertise with the MPUG community. We hope everyone enjoyed the session and hope to see you back next week for our next live presentation. Thanks again and have a great day.