Author: Ira Brown

Please find below a transcription of the audio portion of Ira Brown’s session, Exploring VBA: Microsoft Project’s Macro Language Part I, 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 recording of this webinar at your convenience. Melanie: Good morning and welcome. Good morning, good afternoon depending on what time zone we’re in. We will get started here in just a minute to VBA Microsoft’s Project Macro Language. You have Melanie here with team MPUG today, and I have up on the screen, how to use GoToWebinar. So, please take a look. We’ll be taking questions throughout the session, but I’ll hold them until the end and then present those to Ira. So, feel free, if you have a question, as you’re going to chat it to us through the chat window on GoToWebinar. Melanie: I’m going to take the time now to introduce our presenter. Our expert today is Ira Brown. I was going to be talking to us again about VBA. He’s the founder and president of Project Widgets, which is a leader in the field of project management, and he’s a recognized Microsoft Project and project management expert for many years. I do remember Ira leading the pack of innovation at Microsoft Project conferences decades ago. Not to drop a hint on my age as we go here. Ira’s company, Project Widgets, is a Microsoft gold partner, and is well known for offering ad-on products for Microsoft Project and Project Online, as well as creating custom solutions to meet client’s needs. Melanie: They offer several free downloadable widgets available on their website, so I’ll chat that out to you as we go through the session. Just a reminder, today’s session is just part one of this series. There’s three parts in this series, and there’ll also be a quiz afterwards if you’re interested after the third session. So, please register for those next two sessions to save your seat as soon as possible, and I’ll also send that out to you with the survey after. I am going to switch over to Ira’s capable hands, and again, please chat us your questions and we will present those to Ira towards the end of the session. Ira Brown: Thank you very much, Melanie. I’d like to welcome everyone to our webinar. Let me go ahead and share my screen with you. Okay. Our topic for today is called exploring VBA, Microsoft Project’s macro language. Again, my name is Ira Brown, and it’s good to be back with you again. As Melanie mentioned, this is a three-part series. So, today is really just part one, which is all about introducing you to the fundamentals of working with macros and VBA. Ira Brown: I think that this should be a fun session and hopefully everybody will learn a lot of good stuff that you’ll be able to put to immediate use. Before we get started, just some quick background information about Project Widgets. We’re a Microsoft Project gold partner, specializing in project and portfolio management. Basically, everything that we do revolves around Microsoft Project and we focus on making project easier to use. We deliver consulting services and training, but our specialty is creating custom solutions for Microsoft Project, kind of like what we’re going to be doing today. Ira Brown: Often those turn into our add on products, which we refer to as our widgets. We work with companies in many different industries, contribute to a number of books on Microsoft Project and I’ve been a frequent presenter over the years at MPUG. Let’s get right into today’s session. Part one is going to be really just covering the basics of using macros. How do you get started with it? Some of you may have seen that button in Microsoft Project that says, record a macro, but maybe it was a little bit scary. You weren’t quite sure what to do or how it would work. So, you’ll see it’s actually quite easy and powerful and I think you’ll be able to start using it right after today’s session if you haven’t done so already. Ira Brown: We’re going to see how we go about recording and editing basic macros. Then once you’ve created that macro to make it even easier to run, how you can assign it to a button in the Microsoft Project ribbon, and then we’ll work on some specific examples of how to use a macro. For example, you creating a macro to set the baseline for all tasks in your project that do not yet have a baseline. So, we’ll talk more about that one in a minute. But we’ll also see how we can create a macro to zoom in and zoom out, basically to be able to make the text in your project bigger or smaller like other applications have, where you can zoom in and out of Excel, but project doesn’t really have anything like that built-in. Ira Brown: So, we’re going to see how we can make our own zoom in zoom out feature. Then we’ll see how we can take it a step further and actually start to create our own routines that are actually ones that we’re not going to record, that we can actually write some basic routines from scratch. I’ll show you how to do that. We’ll create one to see how we can adjust the duration for tasks in the project, as well as create a routine to inflate or deflate the work for a task by a specified factor. Ira Brown: Let’s get started. First of all, when would you use a macro? Well, the idea with macros is that you want to try to automate things in Microsoft Project that are often tedious, time-consuming, complicated, or error prone. You build a macro and then you click a button in the ribbon, and what used to take you several minutes or several hours to do, can literally be done in seconds. The one thing about macros is that, in order to be able to create a macro, it needs to be something that you could accomplish manually in Microsoft Project. Ira Brown: In other words, macros can’t get Microsoft Project to do things that it can’t ordinarily do. What you want to think about is, how would you accomplish that particular task manually? Because you have to know what the manual steps are. Then once you understand how you would go about doing it manually, then we would record a macro and actually go through those steps. And then as the macro is recording, all those actions that you’re taking in Microsoft Project are actually captured by the macro recorder, which then allow you to run that macro and have those steps repeated any time you need to repeat them. Ira Brown: Oftentimes when you record a macro, you need to edit the macro a little bit to make it work exactly the way you want it to. So, we’ll see how to do that. Then, once you’ve created the macro, just to make it even easier to run, one of the things that I like to do is I like to assign it to a button in the ribbon. That way, you click a button and now your macro runs. It makes the whole process much easier. Let’s build ourselves a widget. The first example we are going to take a look at is, how do you create a macro to set the baseline for all tasks in your project that do not have a baseline? Ira Brown: First of all, I guess, let’s talk about why you’d want to do something like this. As you may know, the baseline feature of Microsoft Project allows you to capture a snapshot of what your project looks like at a particular point in time. By that, I mean that, let’s say you build your schedule and it’s all ready to go and you’re about to start your project. Well, that’s the time you want to set your baseline because the baseline will capture, in a separate set of fields in Microsoft Project, what do all of my tasks start dates look like? What do the finish dates look like? What do the durations look like? And some other fields as well. Ira Brown: So, it basically captures that snapshot of what those fields look like at that point in time. Then, as your project progresses and the dates change over time, you can compare, how do the dates in my project now compare to what they look like when I captured that baseline. Then Microsoft Project also has variance fields, which automatically calculate the difference between, for example, baseline finish versus finish. And it tells you how many days, either ahead of schedule or behind schedule you are. Ira Brown: That’s why baselining is really useful because you can manage those variances within your project. When you capture that baseline, you’re doing it typically for the whole project when you’re about to begin the project. But let’s say, for example, you’ve started your project and then, a few weeks into your project, you realize that you need to add a couple of additional tasks to the project. Well, you want to set the baseline for those new tasks, but you don’t want to overwrite the baseline for the tasks that you already captured the baseline for. Ira Brown: So, we want to have a technique where we can easily only set the baseline for the newly added tasks. That’s the reason why we want to do this. This is why this technique would be useful. Now we have a good use case here. It’s something that is kind of tedious and manual, right? That’s a perfect use for a macro. Now you want to think about, how would you accomplish this manually? If you, maybe weren’t going to be building a macro, even you just wanted to go through the manual steps, how will you do it? Let’s actually go into Microsoft Project and go through that together. Ira Brown: I’m going to go ahead and go to Microsoft Project. Now, I happen to be using the standalone version of Microsoft Project right now. However, everything I’m going to be showing you today will not only work in standalone project, but if you’re using Project Online that you can create macros for use with Project Online, you can create macros for use with Project Server. And they work with basically any version of Microsoft Project that you’re currently using, other than these are not intended for use for project for the web, but for the traditional desktop Microsoft Project application, the macros will work in any version. Ira Brown: What I’m going to do is I’m going to start off by opening a template. I’ll choose file, new. I’m going to open up, let’s see here, a software development plan. That’s one of the templates that Microsoft makes available to you, so I’ll just say, go ahead and create a new project based upon that template. There we go. I’ll just drag that on up here. So, we’re just looking now at the Gantt chart for this project. I’m going to go ahead and expand out all tasks. I choose view, outline, all sub-tasks. So, that expands everything else. All right. Ira Brown: What I want to do first is I want to set the baseline for the project, but before I do that, let me just show you what actually happens when you set a baseline. Notice here, where you see the start and finish fields, I’m going to insert the column called baseline start. Now you have baseline start, start, and then right here, I’m going to insert the column called baseline finish. Baseline finish. There we go. Notice that they all say NA right now. When you are in a new project and you haven’t yet set the baseline, the values for baseline start and baseline finish will be NA. Ira Brown: Now I’m going to set the baseline for the project. How do I set the baseline for the entire project? Well, I click on project and then I click on set baseline. I bring up the set baseline dialog box. Set baseline for entire project, and then click okay. Notice what just happened. Notice that the baseline start field is now populated with the values from the start column and baseline finish is populated with the values from the finish column. Now let me insert just one more field here next to finish. I’m going to insert the column called finish variance. Ira Brown: Finish variance is that field I was telling you about that automatically calculates the difference between the baseline finish and the finish to show you whether that particular task has been delayed, or if it’s ahead of schedule. Anytime you see a finish variance greater than zero, that means that, that particular task is behind schedule by that number of days. Anytime you see a negative number in the finish variance column, that tells you that particular task is ahead of schedule by that number of days. Of course, when you first set the baseline, you’re going to see zero because the baseline and the current start and finishes are all exactly the same. Ira Brown: That’s how we go about setting the baseline for our project. But now the scenario is that, what if we add additional tasks to our project? Then I want to be able to capture the baseline only for those new tasks. Let’s actually do that. Let’s put a couple of new tasks right here. I’m just going to hit my insert key a couple of times. Notice, when I do that, it puts in a couple of blank rows. One of the tasks that I realized that we need that we don’t have is a task for our projects kickoff meeting. So, I’m just going to type in kickoff meeting here, and then I’ll also put in … I want to do some, maybe some core team training. Ira Brown: I want to provide some training to the people that will be part of the core team, some of that initial familiarization training. So, I’m going to put in those two new tasks. Maybe the training will happen over a couple of days, so I’ll put a duration in there, and the kickoff meeting can just happen over the course of one day. But notice that there are no baseline start and baseline finish dates for those two new tasks because we haven’t yet set the baseline for those tasks. Ira Brown: What I want to be able to do is I want to be able to go in, see how we could capture the baseline just for those two new tasks, but at the same time, I want to be able to capture that from the macro recorder so I can actually do that anytime in the future even more easily. Let’s actually start to record a macro now. How do we do that? Well, we click on the view tab. Over there on the right, there’s this button that says macros. I’m going to drop that down and I’m going to choose record macro. Ira Brown: It brings up the record macro dialogue. This is where you’ll want to give your macro and name. I’m going to call this set baseline for new tasks. Notice that I’m just capitalizing the first letter of each word, no spaces or anything, just to make it easier to read. I won’t worry about any of the other prompts here. We’ll just let them all default here and then I’m going to go ahead and click okay. Now the macro recorder is running. Now, anything that I do at this point will be captured by the macro recorder. Again, ask yourself, how would you accomplish this manually if I wanted to set the baseline for only new tasks in my project? Ira Brown: Well, one of the things I’m thinking we could do is, any task that was newly added is not yet going to have a baseline. Notice how it says NA here. I’m thinking that we need a technique to be able to say, only show me those tasks where there is no baseline start or baseline finish. In other words, where it’s equal NA. What does that sound like then? What will accomplish that in Microsoft Project? Well, building a filter would accomplish that one, won’t it? We can build a filter to say, only show me those tasks where the baseline finish equals NA, and that will isolate those tests. Ira Brown: Let’s actually do that. We go to where it says filter here. I’m going to drop the filter down. I’m going to say, let’s build a new filter, and I’m going to call this something like on baseline tasks. Now, we need to build the filter logic. I always like to think of this as only show me those tasks where, and you have to look at a certain field. Well, the field we can look at could be baseline finish. I could say, only show me those tasks where baseline finish, then the task is equals. Then what am I checking to see if it’s equal to? Well, NA. What this filter ought to do then is only show me those tasks where the baseline finish equals NA, and that should find the tasks that I want to baseline. Now I’m going to go ahead and click apply. Ira Brown: Notice what just happened. We’re only looking at those tasks that we just added. So, that’s exactly what I wanted. Now what I’m going to do is I’m going to click on this little corner button here that selects all the tasks. So, I’ve just selected all the tasks that are in this filtered list of tests. Now I’m going to go to the project tab. I’m going to choose set baseline. I’m going to pick the set baseline here, but then, rather than setting the baseline for the entire project, I’ll say, set the baseline only for selected tasks. That way, I’m not going to overwrite the baseline for those tasks that I’ve already baselined previously. I won’t worry about these other two options for now. So, now I’m just going to go ahead and click okay. Ira Brown: Now it gives me this little warning that the baseline has already been used. Are you sure? Well, technically it’s not going to override anything because I’m only doing it for the tasks that have not yet been baselined. I’ll just say yes here. All right. Notice what just happened. Notice that baseline start and baseline finish are now populated for these two new tasks that I’ve added. So far things are going really well. Now, am I done? Well, kind of. However, as a courtesy to the user who may be running this, I don’t want to leave the project filtered like this. Ira Brown: I want to remove the filter now that I’ve set the baseline. I’ll go to the view tab and I’ll drop down where it says filters, and then I’ll say clear the filter. Now we are back to having all tasks displayed in the project. So, it looks like we have done everything that we want to be able to do. Now what I want to do is I want to stop the recorder. So, we’ll click on macros, stop recording. Everything that I did there, hopefully now has been captured by the macro recorder. Now, how do we go and take a look at our macro? Well, we, once again, drop down this little macro button and we’re going to go to where it says visual basic. Ira Brown: So, we’re going to go ahead and select that. Here we go. Notice at the top of the screen here, under where it says project global, global MPT, we can expand out where it says modules and notice that we have something called module one. Let’s double click on module one. Sure enough, here is the macro that we recorded. Microsoft Project always puts macros into what are called modules. Think of a module as a container for a macro. And you can actually have more than one macro in a given module. And by default, it names it module one, but you can give that a different name if you want to. Ira Brown: Here’s the macro that got recorded. What we’re going to do now is let’s break it apart a little bit and see what it actually did. I’m going to just put some hard returns in here. I’m going to get rid of these first two comments here for now. I’ll just delete them, and I’m just going to put some spaces here, just to make this a little bit easier to read, and it doesn’t affect the ability to run the macro. Let’s take a look at what just happened. The first two lines here have to do with the filter. The first line, it starts off with filter, edit. Ira Brown: Notice it says, this is referred to, by the way, as the filter edit method. That’s what was captured by the macro recorder. And basically what this is, is create a new filter named unbaselined tasks. It’s a task type filter, so task filter, colon equals true. Create colon equals true means, create a new filter. But if there should already be a filter with that name, overwrite existing also says true. Then as we work our way across, you can see the field called baseline finish, and then it has the equals, and then it has NA. Ira Brown: These are basically all the different parameters that I entered as I was building the filter. It was all captured in that one line right there once the filter is built, then the next line says, filter apply. What that means is take this filter called unbaselined tasks and actually apply it once you’ve build it. Now that’s what applies the filter. Now, this line right here, baseline save, that’s where we went in and said, save the baseline for only selected tasks. That’s what that line does for me. Now, this line right here says select task field. Well, at one point when I was moving my cursor around, I clicked in that column called baseline start, just to illustrate a point to show you that field. Ira Brown: Technically, that is really not needed in my macro. We don’t really have to keep that in the macro. I’m just going to delete that line because we don’t need it. But once we save our baseline, remember I said, at the very end, we want to be able to clear the filter, well, that’s what these two lines do right here. This applies the all tasks filter, which basically says, show me all tasks rather than the on baseline tasks. I believe technically I probably don’t even need this last line right here. I think I’m just going to delete it. As you work with this more and more, you’ll get more comfortable with what you can delete versus what needs to be there. Ira Brown: If you don’t delete something, it’s probably fine as long … Because of course, you’re going to test this and make sure it works. As long as everything’s working the way you want it to, if there’s an extra line or two, it doesn’t really make any difference. One of the other things that I like to do is I like to put in comments here as well. It makes it much more easy to read, especially … I mean, right now it’s fresh in my mind because I just did this. But when I look at this in a few days from now, or a week from now, or a year from now, I may not remember why I did these things, so I like to put in comments. A comment always begins with an apostrophe character, and I’ll just type in here, build filter. Ira Brown: Notice when I arrow off the line, it shows up in this green text. So, it’s purely for documenting what it is that we’re doing just to make it easier to read. Here, I’m going to say, save the baseline for new tasks. Actually, I noticed there’s one other thing I probably want to add here that looks like maybe didn’t get recorded by the macro recorder. Sometimes it’s necessary to add additional commands to get it to do exactly what you want. Remember, one of the things, when I click that corner button, that’s the equivalent of this line right here, select all. Ira Brown: I’m going to say here, select all filtered tasks. Then we set the baseline and then I’ll just say, clear the filter. Now I have my comments saved in the macro so that down the road, when I look at this again, I can understand everything that I did. Let me just mention at this point that every single macro that I am creating during this session, as well as the other two sessions that we’ll be doing over the next couple of weeks, I will be happy to send every one of these macros to you. At the end of the session today, I’m going to just show my email address. Ira Brown: If you send me an email with your contact information, and you just say, please send me a copy of the presentation along with all the macros that we’ve created, I’ll get it right out to you. Don’t worry about taking all kinds of notes and everything. You’re going to get a copy of all these filters just for the asking. Or all these macros rather. So, we’ve just built our macro. One of the things I will do at this point is I’m going to just go ahead and hit the little save button at the top. By the way, the macros will automatically get saved upon closing Microsoft Project. But if you want to save them prior to closing out of project, you would just click the save button at the top. Ira Brown: I’m going to go ahead now and click on file, and I’m going to click close and return to Microsoft Project. That closes VBA and takes me back to my project. All right. What we want to do now is we want to make it even easier to run this macro by assigning it to a button in the ribbon. Now, before I do that, let me just show you that I don’t have to assign it to a button in the ribbon. If I hit my little macros dropdown, and then I say, view macros, notice there’s the macro. I can run it right from there. Just select it and then click run, and I can be done there. But I like to take it a step further and assign it to a button in the ribbon. Let me show you how to do that. Ira Brown: Okay. What I’m going to do is I’m going to right click in the ribbon area, and that brings up this little menu. I’m going to choose customize the ribbon. What I’m going to do is I’m going, where it says, choose commands from, it defaults to popular commands, I’m going to drop that down and I’m going to pick macros. This brings up your list of macros. Right now, this is the only one. Now we want to be able to add the macro to the ribbon. What I have to do is I have to create a new tab within the ribbon. Ira Brown: I’m going to go ahead and I’m going to click this button here that says new tab. Notice how it creates a new tab and a new group. I’m going to give it a little bit of a friendlier name. I’m going to click on the tab first and I’m going to click on where it says rename, and let’s call this tab, MPUG. Now, where it says new group, I’m going to call that widgets. And click okay. Notice that we have a tab and then we have a group. Now we’re going to add the button to the group. All I have to do now is select the macro, click the add button. Notice how I just put that in right underneath the widgets group. What I like to do, just to take it a little bit further, is I’m going to click on where it says rename. Ira Brown: I basically highlight the macro and then click rename. We want to give it a little bit of a friendlier name, so that, because this is what the user’s going to see in the button, so that’s called the display name. I’m just going to put some spaces in here, set baseline for new tasks. This is where I can give the button whatever image I want. Sometimes this is … Takes the most time is figuring out what image would be a good one to put on your button. I’m just figuring that out on the fly here. How about we go with that one right there. Ira Brown: I’ll just pick that image, click okay, and then click okay. Notice, if we look at the ribbon, we now have a tab called MPUG, with a group called widgets, with a button called set baseline for new tasks. All right. Now we get to test things out. We need to test the macro. What I’m going to do is I’m going to add one more new task to my project. Again, I’m just going to hit the insert key here and I’m going to call this test macro for MPUG. This is where we have to hope that everything worked well. But if not, we’ll fix it. All right. Notice, by the way, that new task that we just added, doesn’t have a baseline start or baseline finish. Ira Brown: Let’s click the button and let’s see if it works. So, we’re going to click the button. Look at that. It set the baseline just for the new task and did not change the baseline information for any of my other tasks. I would say this was a success. There you go, folks. That’s how you do it. Let’s move on to our next example. By the way, we’re going to take some questions at the end. Remember, I think you can put your questions in the chat and we’ll allow some time at the end for some questions. Ira Brown: Okay. The next one we’re going to create, this is going to be creating the zoom in, zoom out functionality. Let’s talk about again, why would you want to do this? Well, we go back to Microsoft Project. Sometimes when you look at the text, the text in Microsoft Project, the tabular information is a little bit small, and especially maybe if you are presenting it on a Teams meeting or if you’re projecting it on the screen somewhere and there’s people sitting in the back of the room, it’s kind of small. And we’d like to have a convenient way to make the text larger. Ira Brown: We can do something like that for the Gantt bars down here. We can kind of zoom our slider, but when we use that slider, it only impacts the Gantt bars and really just the timescale. We want to be able to focus on the text though. Microsoft Word has a feature similar to this, but we want to see if we can do it in Microsoft Project. Again, when building a macro, you have to ask yourself the question, how would you do it manually? Well, the technique is we could go to the Gantt chart format tab here, and then notice this very first button is called text styles. This is how we can change the style of the text, so we’re going to click that button. Ira Brown: All right. Notice you can say, item to change all. Right now, let me just cancel this one second. If you take a look in the task tab, notice that everything looks like we’re starting off at 12. That’s kind of what the … Or I’m sorry. The summary tasks are 12. Looks like the regular subtasks are 11. That’s kind of our starting point. If I go to Gantt chart format text styles, I could say let’s bump it up to oh, 14. When I do that, I’ll click okay, and notice what just happened. It just increased the font size to 14 for all my tasks. That’s how you would go about doing it manually. Ira Brown: Let me just undo that by the way. I’ll hit the little undo button there, because what I want to do now is I want to do the same thing, but I want to record the macro to capture the actual macro code that was generated when you do your tex styles. Remember we go to the view tab. We then click on macros, record macro. I’m going to focus initially on the ability to zoom in. I’m going to call this macro, zoom text in. Again, all capital letters on the first character. No spaces. I’ll click okay. Ira Brown: I will go to Gantt short format. I’ll choose text styles, and let me choose the size 12, and then click okay. You noticed how it bumped up the text a little bit? Let me do it one more time. This time I’ll bump it up to maybe 14. Click okay. Even larger. All right. I think that should be enough code to capture in the macro for what we want to do. So, I am going to now stop the recorder, view macros, stop recording. Okay. Now we want to to take a look at the macro code. Let’s go back to macros visual basic. Notice that we now have something called module two. Ira Brown: That’s what the macro recorder created. I’ll just delete out those comments there. All right. Let’s see what we got this time. Well, there’s two lines of code here. The first one says, it’s called the text styles 32X method. Now, you never have to remember that. That has not committed to my memory either after doing this for a long time, but that’s not really important. The important thing is that it recorded it, so we now know what the command is, and then it goes on to say size, colon, equals 12, size, colon, equals 14. Remember the first time I did it, I set it to 12. The second time I did it, I set it to 14. Ira Brown: It looks to me like all we’re really changing here when we want to make things bigger is we just change that number. I would imagine, if we wanted to increment this, can you picture we have a button called zoom text? First time you click it, it increments it by one, click it again, increments it again by another one, click it again, increments it again by another. That’s how I’d like this to work. Then if you go the other direction, maybe we’ll have a zoom text out, and I want to take it down one point at a time, one size at a time. Ira Brown: That’s how I would like this to work. How can I do that? Well, this is where we’re going to use the macro recorder to get the basic command of what we want to do, but now we have to write a little bit of our own VBA code. So, you’ll see, this is not very difficult. I’m going to take you through it every step of the way here. The first thing I’m going to do is I am going to create, right above this routine here at the top, I’m going to create what’s referred to as a variable, and I’m going to say, dim IntSize as integer. Ira Brown: What does that really mean? What that means is essentially I’m declaring this. I’m creating this variable, the name of the variable. I can call it whatever I want, but it’s going to be an integer variable. A good practice would be, when you’re creating an integer variable to use INT as the prefix. Just that way, you know what it is, it’s an integer. IntSize, and then I say, as integer. I can basically put a number in that variable IntSize, and I can put an integer number. It has to be a one, two, three, can’t be a fractional or decimal or anything like that. I declare that variable there. Ira Brown: Now what I’m going to do is I am going to put in this statement here that says, if IntSize equals zero, then set IntSize equal to 12. That’s how I’m initializing that variable. The first time you run this macro in any given session of Microsoft Project, IntSize will be equal to zero. It says, if it’s zero, that’s the starting point, then let’s set it equal to 12. Now what I want to do is I want to increment that by one if so, if it’s not zero, if it’s some number other than zero, so remember the it’s going to actually get set to 12 initially, now I want to say, I’m going to put a comment here, increment by one. Ira Brown: Now I’m going to say, IntSize equals, whatever IntSize is currently, plus one. That’s going to increment it by the one that I want. Then the other thing that I want to do here is, where it says IntSize colon equals, instead of hard-coding the 12 there, I’m going to get rid of that, and I’m going to replace it with the variable called IntSize, because that is what makes this thing actually flexible, and then I can get to delete that last line because I don’t need that at all. Ira Brown: I’m going to just put a comment here. I’m going to say, initialize variable to 12, then I can increment it by one, by adding one to that, and then I change the font size. Put the comment in there. There we go. All right. That should be how we can zoom the text in. Now, what if we want it to do the opposite of that? Because remember we’re going to want to zoom out as well. Let’s see how we can do that. Well, what I’m going to do, just to make this even easier, I’m going to take this whole macro here. I’m going to copy it. I’m going to highlight it. Control C to copy, and then right below it, I’m going to hasted in again, but I’m going to call this other macro zoom text out. Ira Brown: What do you suppose this one’s going to do? Well, it’s going to do the exact same thing except, instead of plus one, we’re going to make this a minus one. That’s going to take it down by one size at a time. I think that ought to give me what I need. I’m going to go ahead and hit the little plus, sorry, the little save button rather. Let me close out a VBA. Remember we say file, close and return to Microsoft Project, and let’s add some buttons again. I’m going to go up to the ribbon. I’m going to right click and say customize the ribbon just like we did before, and then I’ll choose … I’m sorry, I’ll choose macros. There’s our two little macros right there. Ira Brown: I’ll highlight where it says widgets and then I’ll take zoom text in and add it and zoom text out and add it. I’ll then highlight the zoom text in. I’m going to choose rename, and I’m just going to call it, I’ll get rid of where it says text, I’ll just call it zoom in. What button do we want to give it here? Well, how about if we go with maybe this little up arrow here, and then I’ll do the same thing for this button here, rename it, and I’m going to call it zoom out. For this one, I’ll use the down arrow. Click okay. Click okay again. Ira Brown: We have two new buttons here now. One says zoom in one says zoom out. Shall we give it a try, see what happens? I’m going to click the zoom in button. Each time I click it, notice how the text is growing in size. Isn’t that exactly what we want? Now the people in the of the room, they can see that just fine, but I want to take it down a little bit. I’ll click on zoom out, click it again, click it again, click it again, and it takes it back down one size at a time. Hey, we’re doing okay here so far folks. This is doing exactly what we want it to do. We now have ourselves three brand new widgets in our ribbon. The baseline and zoom in zoom out. Pretty cool. Ira Brown: Let’s continue on with our next example. We’re just going to review a couple of additional terms here before we build ourselves in other macro. This is called working with sub procedures. What is a sub procedure? Well, a sub procedure is really just a set of an instructions to perform a specific action. So, everything that we’ve created here so far today, where we set the baseline for new tasks, or where we zoomed in or zoomed out, each one of those is referred to as a sub procedure. Ira Brown: In fact, when you record a macro, you are actually creating a sub procedure. That’s the technical name for it. Notice it says here, you can write your own sub procedures to perform more complex operations that is possible with the macro recorder. As you get more sophisticated with this and as your ambitions get even greater, as far as what you’d like to automate, well, you can create your own sub procedures, and pretty much anything you can do manually in Microsoft Project with a couple of exceptions, you can accomplish through automation through this macro language. So, it’s really pretty powerful stuff. Ira Brown: In fact, the various widgets that you can download for free from our website, as well as the widgets that we have available for licensing or sale are actually written in this language, or much of them are written in this language, but we can get it to do some pretty sophisticated things. Let’s get into our next example. This is adjusting the duration of tasks. Why would we want to do something like this? Well, suppose we have a project, let’s say we’re starting off with a template, and that template is for a medium size project, maybe a medium complexity project. Ira Brown: And you’re about to begin a new project where it is much more complicated. In general, you would like to be able to go in and say, take the duration of every task in my project and double it. We start off with the template, and we say, take the duration of each task in the template and double it compared to what it normally is due to the complexity of the project. That’s the reason why you might want to do something like this. Let’s go about seeing how we would do it. I’m going to go back to Microsoft Project. Ira Brown: We’ll we’ll stay in the same project that we’re in right now. But what I’m going to do is I am going to insert a column next to the duration column. I’m going to insert the column called duration one, and I’ll show you why I’m doing that in a second. What I’m going to do is I am going to click on the duration column, control C to copy, click on duration one, and then paste it in. What I wanted to do here is I wanted to capture what was my duration originally, so that when we run the macro, we’ll actually to be able to see that it really did work. That, for example, if I want to double the duration, I would expect this one day to turn to two, and this two days to turn to four, and this four hours to turn to eight hours. Ira Brown: So, we want to double each of those. How do we go about doing that? Well, for this example, rather than recording a macro, we are actually going to build our own sub-procedure, and it’s not going to be very difficult, and I’ll show you how to do it. First of all, we’re going to go back to the VBA environment. We’ll click on view, we’ll click on macros and then we’ll click on visual basic. This takes us right back to where our other macros are. Ira Brown: I’m just going to right click on the module area here, where it says modules. I’ll right click, and I’ll say, insert a module. Notice that we now have module three. It’s just an empty module. I’m going to go ahead and I’m going to going to name this sub routine. Remember we talked about sub procedures or sub routines. Sub, you always have to start off with the word sub, and then I’ll call it, set task durations. When I type that, it automatically put in end sub at the end. So, whenever you’re creating a sub routine or sub procedure, as it’s called, it starts off with sub, then the name of the routine, just like we named our macro. It’s really the same thing. And then you finish it off with end sub. Ira Brown: The approach that we’re going to take is we are going to loop through every task in the project and double the duration. Well, in order to work with tasks in the project, just like I created that variable, that integer variable, I need to create a task variable. To do that, I type in dim. I’ll just keep it simple. I’ll just use the letter T for task, dim T as task, because it has to know what T is. Well, we’re telling it T is going to be a task. Okay. Now I’d like to loop through all the tasks in my project. Ira Brown: Well, the technique to do that is, it’s called a for each loop. I’m going to type it in here, for each T in active project, so that’s the project that we’re in. That’s considered the active project, dot and then the word tasks. That basically says we’re going to be working with each task in the active project. Now, what do I want to be able to do with that task? Well, I want to be able to set the duration, I want to double it basically. Ira Brown: I’m going to put this line of code here and it’s going to say T, so that’s the task object as it’s called, then I type in a period, and then it shows me all the different properties of that task that I can control programmatically. If you scroll down through here, these field names should look pretty familiar to you. These are all the fields that are available in Microsoft Project for a task. So, I want to pick the field called duration. There it is. So, t.duration, and what do I want to do with it? Well, I want to set it equal to whatever the duration currently is times, that’s the little asterisk there, two. Ira Brown: If I take the duration, multiply it by two, won’t that double the duration? It should. Now, I have to close off my loop here, so where this says for each T, I now have to type in next T. Basically what this will do, when we run it, is it will stop on every single task within my project, one at a time, for each, for each, and go through task one, two, three, four, and for each task, it’s going to take the duration and double it. Now, before I run it, I want you to think about, I can go ahead and run it, but I can tell you I’ll get an error message if I run it right now. The reason why I’m going to get an error message is because remember, always think about, what would happen if you tried to do this manually? Ira Brown: Well, if I go back to Microsoft Project for a second and I go to a summary task. Summary tasks, you really don’t want to double the duration of the summary tasks. You only want to double the duration of the subtask. Because remember, the summary task is intended to be the roll up of the subtask below it. In my logic, I want to say skip the summary task. Don’t try to set the duration of the summary task. Let’s go back to the VBA window. By the way, since I did enclose it, I can still get to it from the bottom of my screen here. I’m going to just put in this one little extra test, and this is going to say, if t.summary equals, and then in quotes, I have to type in the word, no, then … All right. Ira Brown: Let me just finish typing this in, then I’ll explain to you what we’re doing here. We’re basically putting in a little bit of extra logic that says, loop through every task in the project. If t.summary, meaning, if the task is not a summary task, if t.summary equals no, not a summary task, then take whatever the duration is of that task and multiply it by two. That looks like it ought to work. I’m going to go ahead and save this. I’m going to close the window. By the way, the other way you can close the VBA window is just by clicking the little X in the corner. Ira Brown: I’m going to add it to the ribbon, so I’m going to right click and I’m going to say, customize the ribbon, and I’ll pick macros, and I’ll pick set task durations, and then I’ll add it with my other buttons. Just click the ad button, and I’m going to then select it, say rename. Technically, I would probably want to call this something like double task durations. And we have to give it a good little picture there. Maybe I’ll just pick … This is always the hardest part of this is figuring out which picture to use. How about we go with that one right there? And I’ll click okay and then I’ll click okay. Ira Brown: All right. Shall we give it a try? If this works, what I would expect to see is all of these durations to double. Let’s go ahead and click double task durations and see what happens. And it didn’t work. Okay. Well, that sometimes happens. Let’s go back to our code. I think I know what the problem is. Let me see if I set that to false rather than no. Let’s see if that fixes it. What I’m going to do now, I’m going to try running it again. Ah, there it worked. Okay, good. Ira Brown: T turns out that in the macro language, when you’re checking to see if, whether something is a summary task, you would actually use true or false rather than yes or no. I just learned that in front of hundreds of people. That’s okay. It worked. You can see that if we take a look at the duration, it is now double compared to what it was. I think we have ourselves a functioning widget here. So, this is probably a good place to pause. I’m going to pull up this last slide here. By the way, we will continue next time with some additional examples. Ira Brown: We got lots more to do. Make sure you come back next week. I believe it’s at the same time. What I wanted to mention is we are going to be giving away today three free copies of one of our really popular widgets and a shout out to Ken Jameson who created it, the driving path widget. So, we’re going to give away three free copies. Remember I said, if you email me and request a copy of the code, the macros, along with the presentation, you’ll be eligible for this drawing, and we’re giving away three of these driving path widgets. Here’s my contact information. I’ll leave this up on the screen, and this how you get in touch with me. We’ll send you the materials from this presentation. Melanie, let’s use the last few minutes that we have to see if there’s any questions from anybody [inaudible 00:56:38] any question. Melanie: Ira, we do have questions. And thank you for teaching us about the false versus no, because everyone will remember that now. Ira Brown: Yeah. Melanie: I’m sure you planned that. Ira Brown: Of course. Melanie: All of our questions are back to the zoom in and zoom out macro. So, the first one from Dan, the zoom in macro sets all tasks to the same text size. What if you want to treat each task separately? Ira Brown: Okay. Well, it comes back to, how would you do it manually in Microsoft Project? If you could figure out the way to do it manually, then you would work that into your automation. If I go back to Microsoft Project for a moment and I go back to where we did the text styles, where it says item to change, up top, instead of all, I could have picked summary tasks, or I could have picked, let’s see, critical tasks. Basically, these are all the different options you have and that’s how you can refine which kinds of tasks you’d want to apply that logic to. Ira Brown: That’s really the first place I would look. Then, if that didn’t give you what you need, then we could get a little more sophisticated, but it depends on exactly what it is you’re trying to accomplish. Melanie: All right. So, second question. This is from Don. Can you capture the current font size to use to initialize the IntSize variable instead in the zoom in and zoom out macros? Ira Brown: That is a really great question, and I was actually trying to figure out a way to do that yesterday before I put together this example. I have not found anything with built into Microsoft Project to do that. I’m going to keep on researching it to see if there’s a way to get that. But what I did basically was I made an assumption that if I know the view that I’m in is starting off with a font size of 11, then that’s a pretty good starting point, and then from there, I can just increment it by one or take away one. Ira Brown: But in terms of knowing what the font size was when you started, knowing that programmatically, I haven’t come up with a way to do that yet. If any of you figure that out, you can let me know. It might be possible to read what’s called the registry to get that, but I haven’t tried to do that yet, but if I do come up with that technique, I’ll be sure to let everybody know. Melanie: Thank you. And then one more zoom in, zoom out. Regarding the zoom in, zoom out macro, is there a way to preserve the different font size at different summary task levels? Example, higher level task had target font size, now they’re all the same. Ira Brown: Yes. Again, there could be different ways of doing it. I’ll tell you a couple of things that come to mind. One is that, remember there’s a field in a Microsoft Project. If I insert the column called outline level, this tells me what the outline level is of a given task. Then I could also insert the column here called summary. That would tell me whether or not something’s a summary task or not by the yes or the no. So, I could develop some kind of extra the logic that says, if the outline level is one and summary equals true, then do this. Ira Brown: But if the outline level is two and summary equals true, do this. I would probably use that kind of technique. And then rather than using the text styles feature, there is another way of doing it, which is literally saying t., no, not t. Just font 32X, and then you can say size colon, equals, and then put in the number that you want. Notice there’s all these other options here around whether you want it to be bold or whether you want it to be Italic, etc. Anyway, that’s the technique I would probably use. Melanie: We have one great from Wilhelm, and then he also jumps on, in regards to the earlier question, you can select the same size while recording the macro, and then you’ll know the original size. A little help with that. Then we have two more questions if you’re open for it. Ira Brown: Yeah. Let’s do it. Melanie: Okay. When creating a new macro, this is by mark, within any given project, do these new macros automatically save to a master of some sort that is available in any future new or existing product? Are there ways to save macros to individual projects? Ira Brown: Yes. So, it’s possible to save a macro as part of a project. If you only ever intend to run that macro with that project, you can do it. The more common technique is to not save it with the project, but to save it the way we were doing it here and making it part of the global MPT. If you make it part of the global MPT, then it’s available for use with any project. Then, for those of you that are using Project Online, we can actually load these modules into what’s called the enterprise global, which then, not only makes it available for you, but could make it available for any other user of Microsoft Project that’s connecting to that Project Online environment. Melanie: All right. I think that is it. I will text out some of these other comments we had and answers to some of the questions. Ira, thank you so much for this wonderful presentation. I know I learned a lot. Attendees, MPUG community, thank you for attending today’s session. I will chat out the PDU code and I will have it up here on the screen shortly. Thank you for growing your skills today. We’ll send you a survey link as well as the link to the next session shortly, and have a great weekend. […]