Author: Ira Brown

Ira Brown is a leader in the field of project management and a recognized Microsoft Project expert, Project Widgets is well-known for offering add-on products for Microsoft Project and Project Online, as well as for creating custom solutions that meet their client's unique business requirements.  This company continues to extend the scope and breadth of their offerings, thereby increasing the value they provide to customers, by creating Microsoft Project solutions that are tailored to an organization's unique needs.  They even have several free, downloadable widgets available on their website that you can begin using right away.

Exploring VBA Part III: Using VBA to Integrate Microsoft Project with other Microsoft Tools – Transcription

Exploring VBA Part III: Using VBA to Integrate Microsoft Project with other Microsoft Tools – Transcription

Please find below a transcription of the audio portion of Ira Brown’s session, Exploring VBA: Microsoft Project’s Macro Language Part III, 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: Welcome, everyone. We are going to start our session today. It’s Melanie here with team MPUG. Welcome again to VBA. Today we are doing part three, Integration with Other Applications, which is something all of our MPUGers are always looking for. I’d like to welcome back our VBA widget and solution expert, Ira Brown, to continue this excellent series. If you missed part one or part two, I’ll chat out the link to the recordings, and I’ll also send it after in an email. You can also find these sessions and 600 other hours of training and courses on demand on mpug.com anytime. If you have questions today, please type these into the questions chat window, and we will hold those to the end to present to Ira. We’ve gotten some great questions from last two sessions, so I’m looking forward to that. Again, today’s session is part three of the three part series. If you missed one and two, they’re available on demand on mpug.com, just go to our homepage and click on on demand videos and I will send those out to you as well. I am now going to hand the presentation over to Ira. Ira Brown: Thank you very much, Melanie. I’d like to welcome everyone to part three of our series called Exploring VBA: The Microsoft Project Macro Language. So thank you all for attending today’s session and hopefully many of you attended the previous two sessions as well. As Melanie mentioned, they are available on the MPUG website, if you want to get a refresher or if you didn’t have a chance to attend. This will be the final part of the series and this is where it all comes together and we have probably the most fun. That is, we’re going to take a look at how we can integrate Microsoft Project with other office applications. What if you wanted to create some solution to create an automated status report in Microsoft Word by pulling in tasks from your project, or what if you wanted to create some PowerPoint presentation with the timeline in your project? So all kinds of interesting examples we’ll look at together, and I think that you’ll find these to be really useful. Ira Brown: Just a quick reminder about Project widgets. We are a Microsoft Project gold partner specializing in project and portfolio management. In fact, that’s really all we do. Everything we do is based on Microsoft Project. We specialize in creating Microsoft Project custom solutions, which frequently turn into our widgets. As you probably learned from the previous two sessions, that even something like recording a macro can serve as the foundation for a new widget or something much more sophisticated than just what that macro recording provided. So with that, let’s get into today’s session. Ira Brown: Okay. So in today’s session where we’re going to be integrating Microsoft Project with other office applications, the first thing we’re going to take a look at is how do we go about exporting our project to Microsoft Excel? Then we’ll take a look at how we can integrate Project with Microsoft Word by creating an automated status report. And then finally, we’ll see how we’re able to integrate Microsoft Project with PowerPoint where we could take our timeline that we have in Microsoft Project and actually create a PowerPoint presentation that includes our project timeline. Ira Brown: So just as a run reminder, we’re going to be giving away two free copies of our Driving Path Widget at the end of today’s session. Just like we’ve been doing in all of these other sessions, today we’re going to give away two more copies. That’s absolutely free. All you have to do is send me an email at the end of the session, and then you’ll be entered into that drawing. Then also, all of the macros that we’re going to be creating during today’s session, I will make available to you, including the presentation. If you just shoot me an email, I’ll give you that information at the end of the presentation and I’ll be happy to send you everything that we did today. Ira Brown: Okay. So just as a bit of a refresher, we talked about this during the first session about why would you want to create a macro? What’s the benefit? The idea would be that it’s really helpful if you have activities you perform in Microsoft Project that tend to be time consuming, error prone and repetitive, those are perfect candidates for creating a macro. You create the macro, very often you can just record the macro and maybe with a little bit of editing, you can get exactly what you want. You can even assign that macro to a button in the ribbon in Microsoft Project so that when you want to run it, you just click the button. You can share your macros with other people within your organization. Especially if you’re running Project Online, you have the ability to put your macros in what’s referred to as the enterprise global, and then they become available for all users of Project Online. Ira Brown: Today we’re going to be focused on standalone Microsoft Project. That’s what I’ll be using, but everything that we’re talking about today can also be done in Project Server, as well as Project Online and basically any version that you’re running. So the first example, we’re going to build ourselves a widget, and the first example is exporting a project to Microsoft Excel. I guess the question I always like to ask is, well, what’s the business reason why you might want to do this? Well, frequently, I hear from a lot of my clients that not everybody has Microsoft Project on their computer or not everyone has access to the Project web app or maybe people just don’t, or they’re not as comfortable using Project. Maybe you want to be able to share your schedule, but you want to give someone an Excel copy of it just to be able to view the schedule. That’s why you might want to be able to do something like this. Ira Brown: You remember how we said that, well, the first thing is in order to build any macro or automation, you’d have to say, how would I accomplish this manually if I had to do it manually? You need to understand that first and then you can automate it. The way we would go about doing that is, and I’m going to demonstrate it for you right now, I’m going to go ahead and start off by opening up one of the templates that comes with Microsoft Project, this software development plan. So we’ll just create that. I’ll just close that up a little bit there, and then we’ll just expand out all the tasks. So we go to View, Outline, show all sub-tasks. Ira Brown: Suppose I wanted to be able to provide someone with a copy of what we’re looking at right now, basically this schedule, but I want to be able to give it to them in an Excel file? Now, the Excel file is not going to display the Gantt bars. Although I should say that I have come up with some techniques to emulate a Gantt chart in Microsoft Excel. Because if you think of maybe a time scale as a bunch of columns and then maybe you can shade some of the cells. So you could emulate what a Gantt chart looks like, but that’s not what we’re going to try to do today. Ira Brown: I just want to keep it pretty basic. We want to be able to take this project and we want to be able to export it to Excel. I’m going to walk you through the steps to do, but as I walk you through the steps, I am going to first turn on the macro recorder so that everything that I’m doing is being captured by the macro recorder. Remember how we do that? We click on the View tab here, then we go to Macros, drop that down and we say record macro. I’m just going to call this, ExportToExcelDemo. So notice how I’m just putting all those words together, no spaces. I’m capitalizing the first letter of each word there just to make it more readable. Now I click Okay. Our macro recorder is now running. Ira Brown: So how do we go about what’s the first step in exporting this to Excel? Well, it turns out there is a built-in feature in Microsoft Project that lets you export your project to Excel. We’re going to take a look at that right now. We’re going to click on File, Save as, I’m going to go ahead and browse and then I’ll go to the folder where I’d like to be able to save this. I’m just going to go to my C drive and I have this data folder here. Notice where it says Save as type. Well, by default, it wants to save this as an MPP file. However, if I drop that down, notice there’s lots of other options here. Some of these you may not have seen before, and I think it might be worth your while to check out some of the other formats that are available. But we’re going to pick this one right here called Excel Workbook. I’m going to go ahead and give this a name, I’ll just go ahead and call this Export Project MPUG, and then we’ll click Save. Ira Brown: When I do that, it knows I want to save this as an Excel file. So it brings up what’s called the export wizard. This is a built-in feature in Microsoft Project. So I’m going to go ahead and click the next button, and this is going to step me through a series of steps for exporting my project to Excel. It says, what is the format of the data? I’m just going to go ahead and say, I just want to export the selected data, meaning the data that’s in my project, click Next. Then it says, do you want to create a new map or use an existing map? A map is something we can create in Microsoft Project that if I want to be able to do this on an ongoing basis, I can utilize this map over and over again. Essentially the map app says, which fields would you like to be able to export from Microsoft Project to? In this case Excel. Ira Brown: All right. We’re just going to create a brand new map, so the default here is fine. I’ll click Next. Then it says, what type of data do you want to export? In our case, we just want to export tasks. So I’ll check that off. And then it goes on to say, if you’re going to Excel, do you want to also include the headers? In other words, the names of the columns, the names of the field. Yes I do. So we’ll keep that checked, and I’ll just go ahead and click Next again. All right. So this screen here says, okay, well tell me a little bit more about which fields you’d like to be able to export. This is where I could go through and basically just pick the fields one by one. Just by hitting my dropdown here, I see a whole list of fields and I can pick the ones I want. But there’s actually an easier way to do this. Ira Brown: Remember, when you’re in Microsoft Project and when you’re looking at the Gantt chart view as we were a moment ago, well, that structure that you see on the left side, that tabular structure is actually referred to as a table in Microsoft Project. In this case, it happens to be called the entry table. So when you’re creating a map, instead of having to specify each individual field one at a time, I can just say base on table. I’ll click that button, and then it says, which table would you like to base the map on? So I’m going to pick the table called Entry. That’s just a list of all the tables. I’ll pick the one I want and then click Okay. Notice what happens, it brings in all of the columns that are defined as part of that table. That really will save you a bunch of time when you’re doing this. Because that’s exactly what I want. Ira Brown: Now, there are a couple of additional columns that I want to add to this table and there’s also a couple that I actually don’t need. I’m not going to bring over the indicators column since that doesn’t do me any good in Excel. That’s only, it’s that graphical indicator that you have in Project. That won’t come over the way we want it to. So I’m going to just hit my delete key here and that’ll get rid of that one. Also, task mode that tells me if a task is auto scheduled or manually scheduled. I don’t really need that one. So I’m going to delete that one. But I do want to bring over name, duration, start, finish, predecessors, resource names. I’m going to add two more fields here at the end. I want to bring over whether or not this is a summary task. Ira Brown: There’s a field in Microsoft Project called Summary and it’s really just a yes/no field that’s set automatically. If a task is a summary task, then Summary gets set to yes. And if it’s not a summary task, then the field called Summary gets set to no. So I want to bring that over and I’ll show you why in just a minute. I want to bring over one more field and that field is going to be called Outline Level. Notice if I just type in the first letter and then drop it down, it takes me to the Os and there’s that field called Outline Level that I want. Here’s a little preview of what the data will look like. Shows you the first couple of rows just to make sure that, yeah, that looks pretty much like what I’d expect. Now I’m going to go ahead and click Next, and it asks me, do I want to save my map for future use? In this particular case I don’t, so I won’t save the map. Now I’ll just go ahead and click Finish. Ira Brown: All right. Hopefully if that worked correctly and did what it was supposed to do, I would’ve just created an Excel file. Now I need to just go to the location where I told it to create the Excel file. So I’m just going to go to my data folder here on the C drive, and there’s the file I just created called Export Project MPUG. I’m going to go ahead and open that up, and there it is. That’s exactly what I asked for almost. All the data’s there, but it doesn’t look all that great. Does it? What I would like to be able to do is I’d like to make this look a little bit more presentable. I want it to look a little bit closer to the way Microsoft Project looks. I want to be able to preserve some of that formatting that we have in Microsoft Project. Ira Brown: Looking at this, well, what are the things that are wrong with this? Well, for one, the column widths are not what they need to be. We need to increase some of the column widths. How would we do that in Excel? Well, I have a pretty good idea of how to do that, but before I do any of these things, I’m going turn on the macro recorder in Excel. Remember how we recorded macros in Project? Well, we can record macros in Excel and it’s works the same way. What I’m going to do is I’m going to click the View tab. Sure enough, there’s that Macros button again. We’ll drop it down and we’ll say record macro, and we’ll give it a name. So how about if we call this something like Format Excel MPUG. Click Okay, and now the macro recorder is going. Ira Brown: The first thing I want to do is I want to adjust the column widths here so that everything is as big as it needs to be. So how would you do that manually in Excel? Remember the macro’s recording. So anything I do manually, it should pick up. I’m going to do that by clicking on this little corner button right here to select the entire worksheet. Then I’ll just double click right on top of one of the borders of a column. When I do that, it does what’s called a best fit. Notice how that every column now has been adjusted based upon the data that’s in that column. So it’s already looking much better. What else might I want to do? Maybe I want to bold the column headers. I can do that by selecting row one and then just clicking the Bold button. Right now. We’ve made that look a little bit better. Ira Brown: Notice there’s not really any hierarchy here. Everything is just this flat list. I’d like to try to deal with that in some way. Also, my summary tasks are not bold. But remember I said how we’re going to bring over the field called Summary and we’re going to bring over the field called Outline Level? Well, we brought those over because they are going to help us right now, do what we want to be able to do with our macro. But again, let’s see how we would do it manually. Essentially, any place where summary equals yes, I want to make the task bold. So let’s just do that manually for now. Let’s just click on Software Development. I’ll just focus on the task name column, and then I’ll just click the Bold button again. That looks pretty good. Ira Brown: Now, this scope, this is also a summary task. So I’ll make that bold. However, in Microsoft Project, that is actually indented underneath the Software Development, which is the project summary test, that top level, row zero. I’d like to indent that. Well, again, Excel has the ability to indent. Notice right there, there’s a little indent button. I’m just going to go ahead and click that, and notice that it just indented. Now the next task here is a sub-task that doesn’t need to be bold. It’s not going to be a summary, because summary equals no here, but you see how the outline level says two. Well, that outline level two tells me how many times do I need to indent the task to give it that appearance that we want? Well, in this case, it’s twice. So I’m going to hit the indent button twice. One, two. Ira Brown: You see how we’re now starting to have more of that outline look that we want. So I would imagine that if I were to go through every single task in Excel, and if my logic said something like, well, if I’m on a summary task, let’s make it bold. Then based upon this outline level, whatever that number is there, that’s how many times I want to indent the task name. That’s the approach that I think ought to accomplish this. So, I’m not going to take it any farther in terms of the macro recording, I’m going to stop the recorder and let’s take a look at the code, that macro code that was generated in Excel. Basically, everything I’ve done so far has been captured, hopefully. So let’s click on View and then go back to Macros and then say stop record. Ira Brown: Now we want to take a look at the macro code. Remember how we do that? We click on Macros again. It’s a little bit different in Excel than it was in Project. I don’t have the ability to go directly to the Visual Basic Editor here, but if I choose View Macros, here I can see the macro that I just recorded. And now if I click Edit, that will take me to the Visual Basic Editor. Here it is. I’ll maximize that screen. You can see right here, this is the macro that was just created. Now, let’s see if we can analyze some of this a little bit and see what it’s doing. Some of these lines here, we probably don’t really need. So it looks to me like this cells select, I may not need that one. So I’m probably just going to go ahead and delete it. Now, how do I know that? Ira Brown: Well, you’ll get more familiar with what can be deleted as you work with this over time. But remember, I was clicking around in Excel and every time I clicked in the worksheet somewhere, it generated that line of code that said cell select. So in this case, I’m going to get rid of it. This line right here that says cells.entirecolumn.autofit, well, what that is going to do, that is the line of code that actually did that best fit that made everything fit the way it was supposed to. In other words, adjusted all the column widths. That looks like it’s pretty important. Then notice, it says rows one, column one select. Remember I selected the first row. And then once I selected the first row, it says, now take that selection and let’s make the font bold. That’s what this line of code here does. Selection.font.bold equals true. Make that line bold. Ira Brown: Remember how we found that helpful last time when we put some comments in? Let me do that here also. Remember, I’ll just put an apostrophe character in and I’ll just say, best fit all columns. Notice it makes that green text. That’s just the way you can comment this to make it easier to read, to make it more understandable, especially when you’re looking at it down the road. This is going to say something like, make the first row bold. Now the next line here says, select cell B2. So B2 was that name column. Let me just drag this over a little bit so you can see it. When I clicked on Software Development, that was me selecting B2. And then what happened after I selected that cell? Well, again, selection.font.bold equals true. That made that cell bold. Then didn’t I do that again for B3? So it looks like that is the technique, where I can basically just select what I want. Then if I want to make it bold, this line here makes it bold. Okay. Ira Brown: Now let’s take a look at this next line. Selection.insertindent with a one. Well, remember how I indented this scope and I hit the indent button one time? Well, that’s the line of code that got generated. Then I went down to B4. Remember how I indented that twice? Well, it’s interesting what the macro recorder gave us. It actually gave us indent once, indent once again. But it turns out, once you dig a little deeper into the macro language, that this one here is actually what is referred to as an argument. What that means in this case, then I’ll just backspace over, hit my space key. Well, actually it doesn’t… Nevermind. But once you get into it, if you actually look at the documentation, you’ll discover that that one there actually represents the number of times that you want to indent. So instead of having to repeat this line twice, I could just change that to a two. That tells it to indent twice. Ira Brown: Well, isn’t that two really, can I get that number from the field called Outline Level? Because that’s tells me how many times that I want to press the Indent button. So let me just go back to VBA. It’s just here at the bottom again. All right. So I think I have… Oops, sorry. I’m in Microsoft Project. I need to be in Excel. I think I have a pretty good framework for what I want to be able to do here. So now what I would like to do is, using the code that I recorded as the framework, let’s see if we can make that a little more sophisticated to get the automation to work exactly the way we need to. Just in the interest of time here, I’ve actually already created that routine. I’m just going to copy it, and I am going to paste it on in here, getting rid of the one we did before. This is actually what we want. I’ve put some other logic in here. Ira Brown: I’m going to walk you through this right now, just to help you understand exactly how this works. Actually, before I do that, I guess it would make sense, because I’ve already made a lot of changes here. I’m wondering if it’s going to let me undo these. Let’s see. I might be able to get away with, say undo, basically everything that we’ve done. There we go. Hey, I got lucky. All right, so this is how it’s started. When we first opened Excel, this is what we saw. Let’s take a look at this routine here now and let’s see if we can figure out what each line is going to do here. I think it will seem quite logical to you as we go through it. So remember, whenever you create one of these macros, remember it’s also referred to as a subroutine, it starts off with the word sub and then you give it the name that you want. Ira Brown: In this case, we can call it whatever we want. I’m going to call this Format Excel. Now, these here, these are referred to as our variables. I have a few variables I had to create. In fact, all three of them are integer variables. Just whole numbers and [inaudible 00:27:48] just as a way of declaring those variables. Then this is the name of the variable, Int Row. So since it’s an integer, I started off with that prefix, INT. Int Indent Levels is another one I created, and then Int Last Row. Okay. So let’s step through the logic here. The first line here where I have a comment, it says, get the last row in Excel. So why do I want to get the last row? Well, I want to be able to essentially loop through this Excel file here row by row. Ira Brown: How do I know how many rows there are? Well, I can scroll down and I can see in this case, there’s 88 rows altogether. But I need to be able to figure that out within the macro language. So it turns out this is the line that will tell us that. Active workbook, that’s referring to the Excel workbook, .worksheet one. Well, worksheet one is this particular worksheet. That’s the one we’re referring to. And then there’s this object referred to as used range. In other words, like in Excel have thousands of rows, but most of them are blank. What is the used range? What are the used rows? Well, that’s what used range tells us, .rows.count. This is basically going to tell me how many rows are actually being used in Excel. When I get that number, I’m going to assign it to that Int Last Row variable. So now I know how many rows I have. Ira Brown: This section right here selects the first row where we have our column headers. So rows one, one select. While it’s selected, let’s make it bold. We already saw that one. I added one more line here, not only do I want to bold it, but I’d like to actually increase the font size a little bit just on that first row. So selection.font.size equals 12. This is where we can do a best fit for the entire worksheet. It looks like I actually did need that line here, cells.select. So that probably wasn’t a good idea that I deleted that before, but it’s back again. That selects the entire worksheet and then does that best fit. So it makes all the columns as wide as they need to be. Ira Brown: You can see how these comments are really helpful. Now, notice the next thing we’re going to say. Loop through each row in order to bold the summary tasks and to indent the sub-tasks. So this is what is referred to as a loop, specifically a four next loop. It is a way of iterating through each row within the Excel spreadsheet, one row at a time. And while you’re processing that row, let’s do some things to it. Well, for example, starting with row two, it’s going to say, let’s determine the number of times we’re going to need to actually click that Indent button. Well, notice what this says, int indent levels, that’s that variable is going to be equal to the cells, meaning looking at particular cell. Ira Brown: Here’s the particular cell to look at. Well, row says, remember, that’s the row, that’s the particular row that I’m processing. In this case, it would be equal to two when we’re processing row two. And then this nine here represents the column that I want. So what is in row two column nine? Well, let’s go take a look. There’s row two, and let’s just count our way over one, two, three, four, five, six, seven, eight, nine. Notice it’s the outline level. That’ll tell us how many times we need to indent. Then the next line says that if that number is greater than zero, because remember, the first row here which has a zero in it, we can’t indent zero times. That doesn’t make any sense. So we have to have this little extra check here. Ira Brown: I’ll put a comment in that if the int indent levels is greater than zero, then indent the name of the task. That’s what this is doing here. If that is greater than zero, what does it say to do? Well, again, in this case it would be row two. And then in column two, well, that’s where we have the name. Perform that indent command, and then how many times should it do it? Well, int indent levels tells us how many times the indent button should be pressed essentially, just like when we do this manually. Then this section right here says that if the column called summary, remember we brought over summary, if that equals yes, then make the name column bold. We basically repeat that for every single row in Excel. Then when we’re done, it just says, select the beginning cells, one, one, that’s basically the first row, first column, select it and then we’re done. Ira Brown: What I’m going to do here is I am going to do what’s referred to as stepping the code. Meaning that I can run this one line at a time. Just you can see it through its thing. So I do that by pressing the F8 function key, and notice it starts working its way through. I’m going to run that line of code right there. And if I hold my mouse over int last row, see how it shows me 88. That’s how many rows there are. Let’s keep going. We’re going to select the first row. Notice we can see on the left, exactly what’s happening. Let’s make it bold. There we go. Let’s increase the font size, made it a little bit bigger. Ira Brown: Let’s select the entire worksheet and let’s do that best fit. There we go. Now we’re going to loop through each row in Excel. We get the indent level. In this case, it’s zero. So if it’s zero, remember we don’t want to actually do any indent. That worked the way it was supposed to. And now if that summary is equal to yes, let’s make it bold. So watch what happens now in row two in that task name column, see how we just made it bold. Now, go back and do the next row. Work our way through. Now, watch it indent scope. See what just happened? Should we be bolding that also? I think, yes, let’s bold it. So it’s really doing exactly what we want. Ira Brown: This is pretty cool. Now we don’t have to do it one line at a time. When you actually are ready to run the whole thing, we can just press the F5 function key or press this button right here, which is the same thing. That’s what I’m going to do and watch what happens. We’re done. So notice it just made the formatting in Excel exactly the way we wanted it to look. So doesn’t this look a whole lot closer to what your Microsoft Project schedule look like? If someone wanted to look at your schedule in Excel, well, this is a pretty good thing to hand to them. It has that nice format to it. So you can see that we have this macro language in Excel, and it works very much the same way, except instead of working with tasks and resources and things like that, you’re working with rows and columns when you’re in Excel, but the basic techniques are the same. Ira Brown: All right, let’s take this one step further. So I’m just going to go ahead and close this. Let’s go back to Microsoft Project and it gets even better. In Microsoft Project, I have created a macro and I’m going to share this with you. Let’s go to the macro, the Visual Basic Editor. Remember we click on macros visual basic, and I will show you here in this MPUG part three. I created this routine called Export to Excel, but notice that everything is now running from within Microsoft Project. What I’m able to do is I can take that exact Excel macro that I created in Excel. I can copy and paste that Excel code, that VBA code from Excel into Microsoft Project. I can actually do all of that Excel formatting in Microsoft Project. Essentially Microsoft Project is controlling, is performing those commands on Microsoft Excel. And it all could be in one consolidated macro. Ira Brown: Let’s take a look at it real quick here. Notice this says export to Excel. Well, remember when we created that map. Remember, this part of the code was created in Microsoft Project, where we told Microsoft Project which fields do we want to be able to send over to Excel. Here’s the line of code here where it says file save as. I’ll just put a comment there, save Excel file. So all of that happens on the project side. Now this is where the Excel portion of it kicks in. This line of code right here that says create object, “Excel.application,” that is the technique for programmatically starting the Excel application just as if you opened it manually. This is how we can programmatically start Microsoft Excel. Notice that we have this line that says set OBJ Excel equal to that. Ira Brown: Well, OBJ Excel is what’s referred to as an object. In this particular case, the object is the Excel application and I’ve named it OBJ Excel. That’s how I can programmatically do these things in Excel from Microsoft Project. So let’s just take a look at what some of these things are doing. Well, this is how we can make Excel visible, because it is possible to do everything that we just did, but Excel doesn’t even have to be visible to the user. But in this case, I would like it to be visible. So we say .visible equals true. This is where we open the Excel file. Now, once we have an open, this code here should look very familiar to you because this is the exact code that we just reviewed over on the Excel side. The only difference is that every line had to be proceeded with OBJ Excel. Ira Brown: Remember how we selected the row and we made the font bold and we made it 12 points? Well, over on the Excel side, it just said, selection.font.bold. I had to put here OBJ Excel.selection, OBJ Excel. Every line of that VBA code has to have the prefix OBJ Excel. When you do that, it knows that that command runs against Excel rather than Microsoft Project. But other than that, it’s the exact logic that we just reviewed. Notice how this is called Export to Excel? Let’s remember that name. But what I did previously was I assigned that to a ribbon button. So let me go ahead and close this. There’s our ribbon that we created last time, MPUG, and you know what, maybe I did not assign it to the ribbon button. Ira Brown: Well, that’s okay. Let me do it right now. I’m going to right click, customize the ribbon. Remember, this is just a review from last time. This is exactly what we did last time. I’m going to choose Macros here, Export to Excel. That’s the one that I want. I want to add that to my list of widgets here. Remember that widgets group. So I’m going to click Add, and let me right click on Export to Excel. I’ll rename it, give it a little bit of a friendlier name. Maybe I’ll call it Export Project to Excel. And I give it a nice button. Like maybe that guy right there, click Okay, click Okay. And there it is, Export Project to Excel. Just want to make sure Excel is not running. It does not appear to be. Let’s give this a try. Ira Brown: I’m going to click that button right here and let’s see what happens. So what would we expect to happen? Well, the first part of the code is it’s going to take the project, save it to Excel, then start up Excel and then do all that really cool formatting. So in one click of a button, I’m hoping that we get everything that we need. Let’s give it a try. Let’s click the button. Here we go. Starts up Excel, does all my formatting. Done. How about that? That was pretty easy. That’s a perfect use for [inaudible 00:41:45]. Anytime somebody ever says to you, “Hey, can you give me a copy of your project in Excel?” You can just say, “Oh, sure, no problem.” You can click your button here and you send them the Excel file. It’s easy as that. I really like this example because I think you’d be able to put it to good use. Ira Brown: So, we have some time left here. Let’s take a look at a couple of other examples. This time we’re going to see how we can do something with Microsoft Word and how we can also do some automation with PowerPoint. The first Microsoft Word example we’re going to take a look at is we are going to create an automated status report. I’m going to go ahead and open up this project here called Status Report Widget Demo. By the way, again, I’m going to be sending this to you. All you have to do is just send me an email and I’ll send you everything that we’re doing here today. Let me go to VBA for a minute, click on View, Macros, Visual Basic, and on top here, I’m going to just select the macro called Show Status Report Widget. Ira Brown: When I run the line Show Status Report Widget, it actually says, okay, show me the form. So this is actually something that we do within a form. Just like we did last time, this is a little bit more sophisticated than the one we did last time. So we’re going to be able to run this form and the user will be able to supply a date range for the report. So we want to start our report on this date and the report on this date. I can put in an overall status, red, green, or yellow. I can specify who the project manager is. And then I click Okay, and that actually runs all this code right here. So we’re not going to go through every single line here, but just generally, what we’re doing is we’re going to launch Microsoft Word programmatically. Ira Brown: This is how we do it. One of the things I just want to point out is that another technique that you can use to make it even easier to create this VBA code is if you go to the Tools menu, once you’re in VBA, and choose References. Notice how I can specify a reference to Excel or Word or PowerPoint. They’re all listed here alphabetically until I check them. Then it moves them to the top. Well, when you create that reference, it makes it even easier to write the code because it’ll give you suggestions as you’re creating your code. I just want to mention that. In this case it says, start Microsoft Word, make it visible, open up, we’re going to start off with a Word template that I’m going to show you in a minute here. That’s going to be the template that we’ll use to build the status report. Ira Brown: We’re going to utilize this technique in Word. If you’re familiar with bookmarks in Word, bookmarks are really handy to go to a particular location in a Word document. Let me go ahead, before I keep going here in the code, let me show you what that template looks like. So I’m going to go to my data folder here, and I’m going to open up this project, WidgetsStatusReport.doc. This is essentially what the format of the status report is going to look like. It’s going to have several different sections in it, like who is the project manager? Sorry, who’s the project manager? What’s the name of the project? What’s the reporting period? What is the overall status of our project? And then we have a section for key tasks and milestones, tasks completed this tracking period, tasks that were scheduled to be completed but did not. Ira Brown: We could programmatically produce this status report by taking information from Microsoft Project and actually just writing it to this format that we have right here. One of the techniques that we use, how does it know where to write the data to? Well, in Microsoft Word, we have something called a bookmark. If I click on Insert and then bookmark, notice I have several bookmarks already listed here. So for example, see where it says project manager, if I were to click, Go To, watch what happens to my cursor in the Word document. I’ll click Go To and let me just click Close here. My cursor is now positioned right next to project manager. I use these different bookmarks strategically. So I say, before you write the key tasks and milestones to this table in Word, position yourself right there, and then write in the name, write the duration, write the start, write the finish, and write the percent complete. Ira Brown: Then to go to the next row, I have to send another tab command that will add an additional row here, and then I write the next task and the next milestone, et cetera. I do that for each section. So we go back to project. That’s what all of this code is doing, and it’s all documented for you. It goes to the bookmark that we specify, and then it does this thing called OBJWord.selection.typetext. And it’s as if you typed the text, but in this case, it’s the project name or it is the duration or start date or finish date or percent complete. Get the idea? So this will produce our status report for us. Let’s give it a try. Let me close this code. Let me close the Microsoft Word template. I am now going to click the Status Report Widget button, and there’s our form. Ira Brown: Let’s put in the start date. In this particular case, I want my start date to be the beginning of this week. That would be 9/27. Let’s put that in there, 9/27/2021. The report finish date, I’ll use 10/3 for that, 10/3/2021. Overall status, I’ll select, let’s give ourselves a green status here. And then project manager, let’s see, project manager, I’ll make myself the project manager. I wrote Brown. Okay. Now, I’m going to click Okay, and let’s see what happens here. I click Okay, it will programmatically launch Microsoft Word, and right before your eyes, it builds the status report. So Project is now controlling Word, and it’s writing this information to the key tasks, to the tasks that were completed this tracking period and the tasks that were supposed to be completed but did not. So notice that here, percent complete says 100% they were completed. Ira Brown: Notice the date range falls within our tracking period. Here are some more tasks that we’re supposed to finish, but they’re not 100%. So there you go. We just build ourselves a status report. You can see there’s the name of the project, there’s the project manager, there’s the reporting period, and there’s our green. We shaded that so green for our overall status. This is pretty cool. This is doing exactly what we want it to do. Let’s take a look at one more example, and then we’re going to open it up for some questions. So, the last example is going to be, I’m just going to close PowerPoint here, because I don’t want PowerPoint open in this case, we are going to programmatically control PowerPoint. What I want to do is I want to create a presentation in PowerPoint to take this timeline that I have here at the top of this view. Ira Brown: I want to be able to bring that timeline over into PowerPoint. So let’s take a look at VBA for a moment. View, Macros, Visual Basic. And then I’m going to show you right here, this routine called add timeline to PowerPoint. So just like we started Excel, programmatically and Word programmatically, this is how we start PowerPoint programmatically, pretty much the same concept. But before we start it, these lines of code here actually copy the timeline. So it basically puts that timeline in the Windows clipboard. Starts PowerPoint, makes PowerPoint visible. It opens up my presentation file. So I’m basically using a presentation as a template. Let me just go to that folder. I’ll show you that real quick. This is what the template looks like. It’s just the project widgets logo at the top and essentially a blank slide. And then once we open that template, we then do a paste special. Ira Brown: Essentially, we’re doing a paste of the timeline as a bitmap image. That’s what this does right here. Then we position it where we want it. We’re sending the left and the top so that it’s centered on the slide. And then finally, we actually run the slideshow. Shall we give this a try? Let’s go back to our ribbon. Let’s click Export Timeline to PowerPoint. Here we go. Click the button, fire up PowerPoint, bring over my timeline, put it in the slide, go into presentation mode. And there it is. So all that was done programmatically. I realized this is a very simple example or fairly simple example, but basically anything that you can do manually in PowerPoint for the most part, you can do programmatically. That’s the good news. The bad news about PowerPoint, it does not have a macro recorder. So you actually have to refer to some online documentation. Ira Brown: When I click on Macro, see there’s no recorder here. I can create my macros if I know what I’m doing. But the good news is once you get good at this with Project, once you get good at this with Excel, you can apply those same skills to PowerPoint. Instead of working with worksheets or tasks in PowerPoint, you work with things like presentations and slides and fonts and things like that. But again, no recorder, but you can still automate pretty much anything that you want in PowerPoint, just takes a little bit more work to do it. So with that, folks, I think we have completed the material. Let me just remind you that we’re going to open up for questions in a second. But let me just remind you that if you, I’ll just put this slide up here real quick, if you send me an email, I’m going to put my email address up right now. Ira Brown: You will be entered into our… Oh, looking at it. Somehow the quantity just went from two to three. Three free copies of the Driving Path of Widget. And then all you have to do is send me an email. My email address is ira.brown@projectwidgets.com. I encourage you to go out to our website, you can learn a lot about our services and all the different widgets that we have available. There’s even some free ones you can download as well. If you’re interested in seeing a demo of any of our other widgets that are on our website, please reach out to me. I’d be happy to arrange a time to meet with you. If there’s any that you’re interested in, I’d be happy to give you a demo. So again, thank you so much for joining the session today. Melanie, do we have any questions? Melanie: Ira, we do. Ira Brown: Okay. Melanie: Steve, “Would it be easier or harder to create a table in Excel to refer to column names rather than numbers?” Ira Brown: Yeah, I think that’s a good technique. Because oftentimes, if you’re just talking about column one, column two, you can easily say, well, A is one, B is two, et cetera. But there is a little technique that I find pretty useful in Excel, that if you go into your File options in Excel and then click on Formulas, you can see where this is option that’s unchecked, R1, C1 reference style. If you check that, notice what will happen is instead of seeing ABCDE at the top there, you actually see the column number. So I find that pretty useful if you’re trying to figure out what column to use. But yes, if you create a table and you can refer to that column by a name rather than the number or even the letter, then that could make this even easier. I agree. Melanie: Super thank you. Thank you, Ira. Thank you, Steve. So from Teresa, can you change the date from text to an actual date format? Ira Brown: Yes. That’s a great question. Well, the answer is yes. However, it’s maybe not quite as easy as you’d like it to be. Let me open up this Excel file for a second. This is the one we used. Notice that when we do our export, this is how it brings it over. Now, there is that technique in Excel where you can say, let’s apply a formatting to this. But the problem is that even if we format this as a date, I’ll pick date here and then I’ll say, let’s show it like that for example, it doesn’t do it because it thinks this is text. So what I’ve had to do is I’ve had to strip out the time element here. Once I get rid of the time, like I get rid of where it says 8:00 AM, then all of a sudden Excel sees it as an actual date. Ira Brown: What I’ve been able to do is, there’s lots of ways of doing it, but an easy technique might be to do a find and replace. Because most of what we’re looking to replace you can see is either eight o’clock, one o’clock or 12 o’clock. Well, there’s a three o’clock there too. So this is not the perfect solution. There’s other techniques as well. Well, you can look at maybe the number of spaces and basically you just have to do some string manipulation here. But if I search for space eight o’clock, space am, and replace it with nothing and then do a replace all, you see how I’ve just fixed the problem for 20 of them, and then I’d have to do it for the other time. So there are other techniques as well, but essentially once you converted from text to a date, then you can apply whatever format you want. Melanie: That was a great question. Okay. So from Mike, he wants to know, can you automate project data into Visio and then Gantt chart template? Ira Brown: Yes. Maybe in a future session, we’ll have one that actually explores how VBA from Project can integrate with Visio. The bottom line is, I don’t know if I have Visio installed on my computer or not. Let’s find out if I do. It looks like I probably do not, but Visio also has VBA built into it. I don’t remember if it has a recorder or not, but it has VBA. So you can programmatically manipulate and integrate with Visio as well. Melanie: On that note, Ira, I have had people asking as we’re in the session, can we do another session, a full course with you even deeper on this subject? So I will be harassing you about that. Ira Brown: Want to do a part four and a part five, you think? Melanie: We’re just going to keep going. Ira Brown: All right. Melanie: Another question from Dan, in the PowerPoint code, you declared OBJ PowerPoint as the PowerPoint application rather than as an object as you did in the Excel code. What is the difference between the two approaches? Ira Brown: That’s a great question. Well, remember how I talked for a moment about setting a reference when you go to Tools references, and then I picked PowerPoint? If I set a reference to PowerPoint, then I can declare, I can start the application differently. Instead of saying, create object. I can actually say equals new PowerPoint.application. It basically does the same thing. But because I’ve declared an object, sorry, because I declared a reference to PowerPoint, now as I am typing my code, like if I say OBJ PowerPoint, I hit a period here, see how it gives me these choices. By having the reference, it gives me all the possible commands that I can choose from. So if you just declared it as an object by doing create object, you don’t get that added benefit. So this would be the preferred technique. Once you get a little better at it, once you get more experience, I would say, this would be the preferred technique to make the code writing even easier. Melanie: Excellent. Excellent. That looks like it for the questions. Ira Brown: All right. Very good. Well, I know we’re at the top of the hour here. So again, I just want to thank everybody for attending the session. Again, feel free to reach out, ira.brown@projectwidgets.com. I’ll send you the presentation. I’ll send you all the VBA code we generated. Again, I want to thank Melanie for her hospitality. This was a lot of fun. Hopefully we’ll do it again sometime. Melanie: Thank you. A big, big thank you to Ira. This is an excellent series, and we will try to get him back to go even farther. A big thank you to the MPUG community. Thank you for choosing MPUG to grow your skills with today. Again, the PDU code, I have up on the screen here. Following today’s event, I will send a link to the recording. I’ve chatted those out as well. And we’re going to send a quiz, covering the entire series. The quiz is solely to test your knowledge and for your records, you can take it as many times as you want. So please save a copy of your results. If you’d like to keep it along with your transcripts, we will not save that information. So that is all up to you if you keep that. Melanie: I will also send out a super short survey shortly. Please let us know what you think. We invite you as well to join us back here for some other sessions. We’ve asked you what your frustrations are. Some of these sessions that are coming up specifically answer those frustrations. So our first session in October, that’s coming up, is about modifying custom fields and project for the web. You can do that, and we’re going to answer your frustrations in a session covering all you need to know about task types. That is pretty exciting. You can sign up for any of these today and save your seat MPUG.com/events. Again, thank you for joining us today. I will leave the PDU code up on the screen so you don’t miss that. We’ll look forward to hearing from you in our surveys and seeing you at a future event. Thank you again.

Exploring VBA: Microsoft Project’s Macro Language: Part III Using VBA to Integrate Microsoft Project with other Microsoft Tools

Exploring VBA: Microsoft Project’s Macro Language: Part III Using VBA to Integrate Microsoft Project with other Microsoft Tools

Project Management Institute (PMI)® Professional Development Units (PDUs): This Webinar is eligible for 1 PMI® PDU in the Technical category of the Talent Triangle. Event Description: The session is geared toward a Microsoft Project business user and will provide practical, real-world illustrations for solving common challenges a user faces with Microsoft Project. In this third and final session of this series, you will learn how to use VBA to integrate Microsoft Project with other Microsoft Office applications, including Word and Excel. Learning Objectives: Part 3: Using VBA to Integrate Microsoft Project with other Microsoft applications * Using VBA to integrate Microsoft Project with Excel * Using VBA to integrate Microsoft Project with Word * Using VBA to integrate Microsoft Project with PowerPoint Presenter: Ira Brown is a leader in the field of project management and a recognized Microsoft Project expert, Project Widgets is well-known for offering add-on products for Microsoft Project and Project Online, as well as for creating custom solutions that meet their client’s unique business requirements.  This company continues to extend the scope and breadth of their offerings, thereby increasing the value they provide to customers, by creating Microsoft Project solutions that are tailored to an organization’s unique needs.  They even have several free, downloadable widgets available on their website that you can begin using right away. Have you watched this webinar recording? Tell MPUG viewers what you think! [WPCR_INSERT]

Exploring VBA Part II: Using VBA Forms to Create a Custom Interface – Transcription

Exploring VBA Part II: Using VBA Forms to Create a Custom Interface – Transcription

Please find below a transcription of the audio portion of Ira Brown’s session, Exploring VBA: Microsoft Project’s Macro Language Part II, 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: This is Melanie here with Team MPUG. Thank you for joining us today. We are going to get started with our session today. We are covering VBA: Microsoft Project’s Macro Language, using VBA forms to create a custom user interface, part two. Again, this session is eligible for one PMI PDU in the technical category. Melanie: I’ve chatted the code out. I’d like to welcome back VBA widget and solution expert Ira Brown to continue his excellent series. We had great feedback from part one. If you missed part one, I’ll chat out the link to the recording and I’ll also send it in the follow-up email today. You can find part one and 600 other hours of training and courses on mpug.com anytime. Melanie: If you have questions today, please type those in on the questions chat window. I’ll hold those till towards later in the session and present those to Ira then. I’ll also be making a random selection today. Let’s see, we have 47 on so far. Attendee number 50 that signs on is going to get an always appropriate, always cute MPUG pug as a thank-you today. Melanie: Again, today’s session is part two of a three-part series. If you haven’t registered for three, please save your seat by registering today. You could do that from the homepage mpug.com. I am now going to hand the presentation over to Ira’s capable hands. And thank you again for joining us today. Ira, welcome. Ira Brown: Thank you, Melanie. And welcome back everyone who is joining us for part two. Let me go ahead and share my screen with you. Melanie, I think you may need to make me the presenter. There we go. Perfect. All right. So I am sharing my screen, so everybody should be able to see my PowerPoint deck here. Ira Brown: That’s kind of interesting. Yeah, it’s not letting me go in full presentation mode. Okay. Well, we’ll just have to deal with it the way it is. Ira Brown: So let me get started here. So this is part two of our session on Exploring VBA: Microsoft Project’s Macro Language. Last time we met about a week ago, we covered a lot of the fundamentals of using VBA, including how to go about recording macros and assigning the macros that you record to a button in the ribbon so that it makes it easier to run. Ira Brown: And we saw how we can go about editing those macros and even writing some very basic routines ourselves without even using the recorder. So that recording is available, in case you missed that session. And today is also being recorded. Ira Brown: I want to just mention upfront that all of the macros that I’ll be creating during today’s session, I will happily make available to you. You just have to send me an email with your contact information and we’ll get back to you with a copy of today’s presentation, along with all of the macros that we’re going to create during this session. Okay. Ira Brown: Just quick, something about Project Widgets, we are a Microsoft Project Gold Partner and everything that we do revolves around the use of Microsoft Project. And I’ve been at this now for well over 25 years, back before there was even a Project Server or Project Online, just using standalone Microsoft Project. So we could do macros way back when, and we can still use them in Microsoft Project today. Ira Brown: I work with companies and Project Widgets works with companies in many different industries, and we specialize in doing full Microsoft Project implementations, Project Server, Project Online, Project for the web. We also do consulting, training. Ira Brown: Primarily, I guess the main thing that we tend to do more often is create custom solutions for Microsoft Project, which then often turn into our widgets. And that’s what we’re going to be doing today. We’re going to be creating custom Microsoft Project solutions. And who knows? Maybe one day one of these will turn into a widget as well, that will be available for Project Widgets. Ira Brown: So let’s get right into the content for today. We’re going to start off by just going over some helpful hints for recording macros and even working with macros in general. Just a little extra information that is helpful to throw in when you’re doing this to make your macros even better and run more efficiently and that sort of thing. Ira Brown: But where the real fun begins today is when we get into how we go about creating our own user-friendly interfaces via what’s called a VBA form. We’ll be able to design a user interface and actually create a really nice, friendly form to be able to run our custom functionality. So we’re going to take a look at how we do that. Ira Brown: Once we’ve created that form, I’ll show you how you can then create a very simple routine to say, “Now go ahead and present the form to the user,” so they can actually the functionality. And how we’re able to then even assign that to a button in the ribbon as well. All right. So that’s what we’re going to be doing today over the next hour. Ira Brown: And just as a reminder, just like we did last time, we’re going to be giving away three free copies of our Driving Path Widget, pretty useful tool. If you’re trying to figure out what is causing a particular task in your schedule to be scheduled the way it is, this will locate what that driving path is and present it to you right on the screen within your project. So then you can better analyze what’s going on with your schedule. Ira Brown: So we’re giving away three free copies of that. You just need to send me an email at the end, again, requesting the content, and that will automatically enter you in this drawing. Okay. So with that, let’s get started. Ira Brown: Okay. So helpful hints for recording macros. The first one, and it’s really important to get into this habit, is to always back up your project or work with your project in read-only mode before you run any macros against it. Ira Brown: Oftentimes you may be running a macro that may adjust some of the data in your project. Maybe you’re changing durations, maybe you’re changing work, maybe you’re changing dates, whatever it may be. And especially for a brand new macro that you haven’t actually tested yet, you don’t want to actually run it against a project where you could maybe lose some important work that you’ve already done. Ira Brown: So always good to first back up your project, or the very least, open it up in read-only mode so that you’re not actually going to be making any permanent changes to your project. Okay. Ira Brown: Next is, if you are using a filter table or view as part of your macro, it is a good idea to actually build that filter table or view as part of the macro that you are recording. And we’re going to actually go through an example of what I mean by that so you can understand it a little bit better. Ira Brown: So with that, let me go to Microsoft Project. And I’m going to go ahead and open up one of the templates that comes with Microsoft Project, this software development plan. So let’s go ahead and create a new project from that template. There we go. And then we’ll go ahead and expand out all the tasks like that. Okay. Ira Brown: So one of the things that you may do from time to time is when you’re in a view like this is maybe you don’t always want to see the names of the resources along with the Gantt bar. Maybe you might want to suppress that. Ira Brown: So like we talked about last time, in order to create a macro to do that, you would first have to understand how do I accomplish that manually? Right? So the technique for accomplishing it manually is to, if you right-click anywhere over in the Gantt area and you bring up the Bar Styles dialogue box, then the first row here which says Task up top, if you go down to the bottom and you click on the Text tab… Ira Brown: That’s sort of weird. I’m clicking on the Text tab and nothing’s happening. There we go. Okay, good. Notice that you can see resource names that are appearing to the right of the Gantt bar. So we would just really delete out where it says Resource Names there, and that would remove the resource names from the bar. So that’s how we would accomplish it manually. All right. Ira Brown: So now we want to actually create a macro to do that. So how do we create the macro? Well, remember last time what we talked about is you would just click on the View tab, go to the Macros button, and then just select Record Macro. Okay. Ira Brown: And then you want to name your macro. So I’ll just call this something like “Remove Names From Gantt Bars.” So notice all the different words there, I’m capitalizing the first letter just to make it a little bit easier to read. Okay. And then I’ll just go ahead and click Okay. And now the macro is recording. All right. Ira Brown: So now we will go through those same steps that I showed you a moment ago. While the macro recorder is running, I’m going to right-click and I’ll choose Bar Styles. Right? And then I’ll click on Task and then I’ll select Text down below. And once again, I’m clicking on it and nothing’s happening. There we go. All right. Ira Brown: And then I’m going to just click on Resource Names. I’ll hit my Delete key. Gone. Click Okay. Notice that the resource names are no longer displaying next to the bar. So I did exactly what I was hoping to do. Now I’m going to go ahead and stop my recorder. So I just click on Macros, Stop Recording. Okay. Ira Brown: And let’s take a look at the macro that we just created. So how do you do that? Well, you click on the Macros button again. And I’ll show you a little bit of a different technique this time. I’ll choose View Macros, and notice it brings up all the macros that we created during our last session, as well as here’s the new one that I just created. Ira Brown: So if I want to run that macro, I can actually click Run. But if I want to view the code, look at the actual macro code that was created, I can just click the Edit button. That’s what I’m going to do. All right. Ira Brown: And notice that the macro was created within Module 1. So it creates that module for me, which is that container for the macros. And then you can see that it named this, what’s called a sub-routine. So it starts off with the word Sub, Remove Names From Gantt Bars. It shows a comment that it was recorded by me. Ira Brown: And then you can see, these are the lines that were recorded, where we’re basically removing the right text associated with item number one in the list. All right. The first line here, I’m thinking actually may not even be necessary, but I’ll leave it there just for now. And I don’t think it’ll hurt anything. All right. Ira Brown: So there’s my macro. And it’s a pretty good macro. It’s doing exactly what I want it to do. So let’s say I now distribute this to other users and now I want them to be able to benefit from that same macro. So let me close this project, and I’ll recreate it from the template again. There we go. And we’ll pick the software development plan. All right. Ira Brown: But one of the things that we really don’t know is what view is this user going to be in when they decide they want to run the macro? They may not be in the Gantt chart view, right? They might be in the Task Sheet view, they might be in the Resource sheet. We have no clue. Ira Brown: So I’m going to just simulate that by saying, well, maybe the user is in the Resource sheet. Okay. And now we see all the resources in the project. And then they decide they want to run that macro. So how about if we just review real quick, remember how we assigned it to the ribbon? So let’s assign this to the ribbon again like we did last time. Ira Brown: So how did we do that? Well, we right-click in the ribbon area. We click Customize The Ribbon. This brings up this Customize The Ribbon screen. And then I’ll choose Macros here in that dropdown. There’s my Remove Names From Gantt Bars. And then I’ll just add it into that same tab, MPUG, and that same group I created last time. Right? So I’ll just click the Add button. There it is. Ira Brown: If I select it, I can say Rename. Now I can give it a little bit of a friendlier name. So really what I’ll do for now is I’ll just add some spaces there so it looks a little bit nicer in the ribbon; Remove Names From Gantt Bars, like that. And then I just have to decide what image do I want to associate. So maybe that looks like it might be a good one. So I’ll pick that image there. Click Okay. Click Okay. There we go. So now we have a button called Remove Names From Gantt Bar. Ira Brown: Shall we give it a try? Let’s see what happens. So we want to click the button. Oh no, what happened? We got an error message. The method is not available in this situation. Okay. Yeah, it kind of makes sense, right? Because we are not in a Gantt view right now, so there are no bars to adjust. So the macro would fail, and we certainly don’t want it to fail. Ira Brown: So what can we do to ensure that the macro is much more foolproof, that it’s not going to fail when the user runs it? Well, one thing we can do is we can apply the view or possibly even create a brand new view to ensure that the user is in the correct view when they go to run this particular macro. So I’m going to go ahead and I’m going to click End here. Okay. And I’m going to record a new macro. Ira Brown: So I’m going to bring up the macro recorder again, record macro. And I’ll just say Remove Names V2. Right? Something like that. So that’ll be the name. Click Okay. Now what I’m going to do is I’m actually going to apply the Gantt chart. So I’ll click the button here that says Gantt chart. Notice it puts me in the Gantt chart view. Now I can basically just go through those same series of steps again. Ira Brown: I’ll go to Bar Styles. I’ll click on Task. I’ll click on Text. Hope it works this time. It does not. That’s interesting. But if I click here for some reason and then click back, it seems to work. Happy to have discovered that during a Live session. Ira Brown: I’m going to just go ahead now and click Delete. Remove the resource names. Click Okay. And then I can turn off the recorder. There we go. So now if we go and take a look at the macro code, it’ll look a little bit different. Ira Brown: Let’s go back and say View Macros. We’ll pick the new version that we just created, Remove Names V2. Click Edit. All right. And notice this time we have one additional line of code there called ViewApplyEx Name:= “Gantt chart”. So that was now part of the code that got captured by the macro recorder, where it’s actually applying the Gantt chart view. Ira Brown: And now that it’s applied the Gantt chart view, now we’re in a much better position to be able to adjust the bars. So let’s give it a quick test. Okay. So once again, I’m going to close out of this project. Ira Brown: Let’s go up to the ribbon. Right-click, Customize The Ribbon. I’m going to click on Remove Names From Gantt Bars. I’ll click Rename here just to grab that text. And then I’ll just say… I’ll right-click it and I’ll say Remove. All right. Ira Brown: So now I’ll click the other macro that we just created, the V2 one, and I’ll bring that one over. There we go. And now if I say Rename, I can just paste in that same text that I had before. I’ll pick the image that I want, which I believe was that person right there. Click Okay. Click Okay. Ira Brown: Okay, so now we just assigned the new macro to the button. Let’s open up the project again, or the template rather. We’ll create that new project. Well, I don’t know what happened there, but we’ll try one more time. We’ll pick Software Development Plan, click Create. And that time it really believed me that I wanted to do it. Good. Ira Brown: So now what I’ll do is I’ll expand out all the tasks. And you can see there’s the resource names. Let me put myself in some view other than the Gantt chart, like the Resource sheet. Okay. And now let’s try running our macro, Remove Names From Gantt Bars. We’ll click the button. And look at that, it works. So this time, no error message. The macro ensured that we were in the correct view and then removed the names from the Gantt bars. Okay. Ira Brown: So just always keep that in mind when you’re creating a macro, is there any reason that this macro would fail? Do I have to be in a particular view? Is Microsoft Project expecting a certain table to be applied? Any of those kinds of things. And if so, then include those steps in the macro that you’re recording to ensure that the user is in that correct mode so that the macro doesn’t fail. Ira Brown: Now, there are other things we can do as well as we get more advanced with using the macro language. We can put in what are called error handlers, where we can actually trap for some particular error that could happen, and then we could take the appropriate steps. Ira Brown: For example, we could trap if the user was not in the Gantt chart view and then apply the Gantt chart view on their behalf, and then continue running our macro. So those are some things that we can do once we get a little bit more sophisticated down the road here. Okay. All right. Ira Brown: Let’s go through a couple more helpful hints. So the next one, when you are creating a macro, sometimes you’ll be navigating through tasks and you want to make sure that after the macro runs, that you return the user back to the beginning of the project so that their cursor is not positioned on some task somewhere in the middle or end of the project. Ira Brown: So it’s a nice thing to say, “Okay, yeah. The macro runs. They’re in a nice view.” First task in the project is selected, and it’s a cleaner way of creating your macros. Ira Brown: Now, when you record the macro, Microsoft Project doesn’t know to do that automatically. So what we’ll do is, as part of the macro that we create, we’ll edit it to just add that one extra command at the end, which is called SelectBeginning. Ira Brown: So let me just go back to Microsoft Project for a moment. And we’ll go to the VBA environment. This time I’ll choose View Macros, and this time I’ll just pick Visual Basic. That’s how you can get to the Visual Basic Editor. Okay. And then here you can see all the macros we created. They are the ones we did last time. Here’s the one we just created. Ira Brown: So what I’m going to add here is just this one line at the very end, and I’m just going to type in SelectBeginning. Now, here’s a little trick for you. I kind of like this. I actually want it to say SelectBeginning, but if I just type in “SelectBeg,” hold down my Ctrl key, and then hit the space bar, look what just happened. Ira Brown: So it basically… I think that’s called IntelliSense technology where if you just type in the few characters, it will finish it off for you. Now, if I just typed in the word Select like that and did the same thing, there’s a whole bunch of commands that start off with the word Select. Ira Brown: So it brings up this little menu here that you can then pick the command you want. Now I can pick SelectBeginning. Just double-click on it, and then it puts it in there for me. Okay. So that’s a good idea to put that at the end of your macro to return the user to the top row of your project. Okay. Ira Brown: And one more helpful hint, and that is, when you’re running a macro, oftentimes you are manipulating the information within your project. Maybe you are changing durations, changing dates, changing work, maybe adding tasks. Who knows? Right? Pretty much anything that you can do manually in Project, you can do programmatically through the macro language. Ira Brown: Now, let’s just say you’re in a 5,000-line project, so you have 5,000 tasks, and you want to go through and you’re applying some sort of logic to change the work, maybe applying a certain factor to the work on every task. Well, that’s going to take time to run. And part of the reason is that every single time you make a change to a task, it’s going to trigger the automatic recalculation of the project. Because by default, Microsoft Project will recalculate your project with every change. Ira Brown: Now, that’s normally not a big deal if you’re just making one change at a time through the normal use of Microsoft Project, but when you’re running a macro, it actually can be quite a big deal. Because again, if you have many, many tasks and you’re going through and manipulating a lot of those tasks, it could add many, many seconds, even minutes to the processing time. Ira Brown: But one of the ways that you can really improve the efficiency in most cases is by adding this extra line at the top of your macro called Calculation=pjManual. And then at the very end, you say Calculation=pjAutomatic. Ira Brown: So what’s going to happen is, before you manipulate any of your tasks let’s say, in the example I used, we’re going to turn calculation off, basically. So it’s not going to recalculate the project every time you make a change. And then at the very end of the processing, we’ll turn it back on by saying Calculation=pjAutomatic. All right. Ira Brown: So let’s just quickly take a look at how to do that. I’ll go back to the Visual Basic Editor. And again, at the top of your macro – now this particular macro here won’t make too much of a difference really, but conceptually, it still makes sense – I can just simply say Calculation, and I’ll use that little trick I told you. Ctrl, space bar, right? Then I can pick Calculation and I’ll just hit the tab key, I just picked it. And then I’ll type in an equals sign. Ira Brown: And notice when I type in the equals sign, it actually gives me a choice of either pjManual or pjAutomatic. These are referred to as constants in VBA. I’m going to pick pjManual. Just double-click it. All right. And then at the very end, we’ll do the same thing. Calculation=, and this time I’ll say pjAutomatic. Okay. Ira Brown: So that’s what you’ll want to do, particularly in any kind of macro that you create, where you’re changing a lot of information in your project, it will run a whole lot faster. Okay. Perfect. All right. Ira Brown: So now let’s move on to the next part of our session for today. Okay. So this is creating a user form. And the example that we’re going to take a look at is we’re going to create a user form to set the resource group field. Okay. So for that, we’re going to go through this little case study that I hope you find interesting. Ira Brown: Many of you that know me and have been to these seminars before know that I’m a very big Beatles fan. So a lot of people wonder how were the Beatles so prolific in the years they worked together to create all those songs in such a short period of time. Well, I found out that they actually used Microsoft Project to manage the creation of their albums. So, little-known fact. And I read it on the internet so it must be true. So we’re going to see how they might’ve done that here. Ira Brown: So I’m going to create a few tasks. So I’m going to create a task here called Write Song, Record Song, and Release Single. How about that? For those of you that know what that is. Okay. Ira Brown: So we’ll have a simple little project plan here. I’m going to make these auto-scheduled tasks, it’s defaulting to manual right now. So let me just switch that to auto-scheduled and then I’ll switch each of these to auto-scheduled. There we go. All right. Ira Brown: And we’ll say maybe we’ll take a couple of days to write the song, a couple of days to record the song, and then maybe we’ll make this a milestone, zero days. Right? And let’s go ahead and link up these tasks. All right. So we’ll click the little link button there. And now we have ourselves a little project that we can use for this example. Ira Brown: So I’m going to switch over to the resource sheet. All right. And we’re going to put our resources in. Well, our resources, of course, will be… And I hope you know what their names are. That would be a good thing. John Lennon, Paul McCartney, George Harrison, and of course, Ringo Starr. Okay. Ira Brown: So we put our four Beatles in the project. And notice there’s a field in Microsoft Project called Group. Right? So what I would like to be able to do is I’d like to be able to create some automation to automatically populate that Group field or do it in a more friendly way. Ira Brown: So the idea would be that I want to be able to prompt the user to enter the name of the group on a form that we’re going to create, and then we’re going to fill that Group field in automatically as part of this automation. Okay. So how do we do that? Ira Brown: Well, we have to go to VBA. So again, we click on View, Visual Basic Editor, this guy right here. Okay. And I’m going to create what is called a user form. That’s going to be the user interface to collect this information. So we do that by right-clicking where it says Modules, Insert, User Form. Okay. Ira Brown: So that is our form that just got created. And at this point, not much going on with that form. I’m going to just grab hold of that little anchor there and just drag it to make the form just a bit bigger. Okay. Ira Brown: So now we want to start putting what are called controls on the form. So remember, I want to be able to allow the user to enter in the name of the group that we want to apply to the Group field. So a good control to do that would be referred to as a text box. So I’m just going to find it. There it is right there. See? That says text box. Ira Brown: So this is referred to as the toolbox. As soon as you click on the form, the toolbox appears. And I’m going to just select that little control there called Text Box. And then I’m just basically going to go to the form, I’m going to draw it on the form. I just click and drag and get it to the approximate size that I want. That’s good enough for now. Okay. So that is our Text Box control, and that is where the user will type in the group name. Okay. Ira Brown: Now, one of the things that we want to do is we want to click on that control. And if you go to the View menu, there’s this window you can bring up called the Properties window. I’m going to select that, and I’m going to name that control. Ira Brown: So notice that right now, it’s just named very generically, TextBox1. But I want to give it a more descriptive name that tells me really what it’s for. And there is a good naming convention you can follow that when it’s a text box, you normally start off with the prefix “txt” like that. And then I’m just going to follow that up with the word “Group.” Okay. So the name of that control is now called txtGroup, and that’s how I can refer to it when I’m writing my code. Okay. Ira Brown: Well, I’m going to click on the form itself. So I’m no longer clicked on that control. I’m just clicked on the form. Notice the form itself is called UserForm1. Let’s give that a little bit of a friendlier name as well. Ira Brown: I’m going to call it “frm” for form, and then maybe I’ll call it frmGroup, because that’s what it’s going to be doing. All right. Well, what else do we want? Well, right now it’s just called UserForm1, that is referred to as the caption of the form. Ira Brown: Well, I don’t want it to say UserForm1. I want it to say something a little bit better than that. So I’m going to type in Project Widgets there. Okay. So now notice when I do that, you can see it appear at the top of the form. All right. So this is starting to come together. No pun intended. Ira Brown: And now I’m going to click on the form. And what else might we want to do? Well, we probably want to… We don’t just want to put that text box there. We want to provide some sort of instructions to the user. Ira Brown: So I’m going to put on what’s called a label. Right? So I’m going to click on Label, I’m going to draw a label right above that text box, just clicking and dragging and positioning it approximately where I want it, something like that. Hey, that looks pretty good. Okay. Ira Brown: And now with that label highlighted just like that, I’m going to… I won’t have to worry about giving it a name so much because that’s not all that important in this case, but you certainly can. But the caption is really what’s important. This is how we’re going to prompt the user. Ira Brown: So I’m going to say something like, “Please enter the resource group,” and then press Enter. And a colon actually as well, and then press Enter. And notice that now appears right above the text box. So that’s what the user will see. Okay. Ira Brown: So what else do we need to put on this form? Well, most forms like this, you would have probably an Okay button and a Cancel button. Right? Cancel if they don’t want it, if they decide they don’t want to do it. And Okay to basically say, “Okay, go ahead and process what I just did.” Ira Brown: So we’re going to put what are called command buttons on the form. So let’s do it. Let’s see. Let’s find the command button. Is it there? Yes, there it is. Command button. I’m going to click that one and I’m going to draw myself a button now, get it to the approximate shape that I want it. I think that looks pretty good. Ira Brown: And then I’m going to name this. So instead of CommandButton1, I’m going to go to the name here. I’m going to type in cmdOkay. Just like that. And then for the caption, remember, that’s the text that’s going to appear on the button, I’m going to type in OK, just like that. All right. Ira Brown: Well, I’m going to now need to put a Cancel button on the form. So let me show you a little trick. You can do exactly what I just did and just simply draw another button or you could select this button, I can right-click and say Copy, and then I can click on the form and say Paste. Right? Ira Brown: So now I have a second button that also says OK. And now I can just… But I know at least it’s the same size. Right? Now I can drag it to where I want it. I’ll give it a name. So let’s call this one cmdCancel. It’s a pretty good name for it. And then instead of displaying the text OK, I’m going to just change the caption to the word Cancel, like that. Ira Brown: Well, this is looking pretty good. We have ourselves a form. Now, what I’m going to do is just to make it a little bit nicer. Allow me to… I want to move this a little bit, right? So notice how I’m going to select those two elements together, if I just click and drag over both of them and then let go. Now I can move both of them relative to each other and just maybe position them where I want them on the form. Right? Ira Brown: And I’ll do the same thing with the OK and Cancel buttons. I’m going to select both of them and I’ll drag them so they’re right-aligned with that text box there. And now I can just make the form a bit smaller just by dragging the edge like that. And then I’ll make it a little less tall by dragging it like that. And that looks pretty good. Not bad, right? Ira Brown: So this will be our user interface for allowing the user to enter the group. Okay. But so far, all we’ve done is create the user interface. It doesn’t know what to do with the information that we’re going to type in. So we actually have to tell it now what to do. What happens when the user clicks OK, what do you want the form to do? Or what happens when the user clicks Cancel, what do you want the form to do? Ira Brown: Well, let’s talk about the Cancel one first. Okay. So what I’m going to do is I’m going to double-click on the Cancel button. Watch what happens when I do that. It takes me to what’s referred to as the code window associated with what happens when the user clicks on the Cancel button. Ira Brown: Notice that I’m in this little sub-routine now that’s called cmdCancel… That’s what I named the button, right? cmdCancel_Click. Right? So this is referred to as the click event. In other words, when the user runs the form, if they were to click the Cancel button, what would happen? Well, it would take them to this event right here. And any code we put in here is going to run when the user clicks the Cancel button. All right. Isn’t this kind of fun? Ira Brown: So what I’m going to do is I need to put in this… I’m going to hit the tab key. And the command that I need to type in here is something called Unload, and then a space. Notice how it’s prompting me for the object, it’s really the name of the object. So in this particular case, I can just say Me. Unload Me. Me being the form. Okay. It knows that Me refers to itself basically. Ira Brown: So this says when the user clicks the Cancel button, basically close the form. That’s what Unload Me is going to do. Ira Brown: Let me once again double-click on where it says frmGroup. And now what happens when they click the OK button? Well, this takes a little bit more thought. And we actually have to write a little bit more code to actually do what we want it to do. So I’m going to double-click on the OK button here, just like we did before on the Cancel button. Let’s double-click it. And it takes me to the click event associated with the OK button. So basically, when the user clicks OK, the code that I have here is what is going to run. Ira Brown: So let me go ahead and… First, let’s think about what we want it to do. We basically want it to say for every resource that I have in my project, let’s assign the group that the person entered on this form to that group field. Right? So I need some way of being able to do that for every resource. Ira Brown: So the way we’re going to do it is, first of all, I’m going to type in something here. This is going to be called declaring a variable. I’m going to say Dim r As Resource. Okay. So we did something very similar during the last session when we were working with tasks. Ira Brown: So r is going to be the name of the variable. And I can name it anything I want. But Microsoft Project needs to know what is r. Well, r is going to be a resource. Okay. Now what we want to do is we basically want to loop through every resource in our project and set the group. So here is the syntax to do it. Ira Brown: We do what is called a for-each loop. And the for-each is nice because it doesn’t matter how many resources you have. It’ll work if there’s one, it’ll work if there’s a hundred. It doesn’t matter. It’s going to process each single resource line. Ira Brown: So we’ll say For Each r in… So where is it going to look for the resources? Well, it’s going to look for them in the ActiveProject.Resources. Right? And then I’ll close that off by saying Next r. Ira Brown: So this is basically the syntax to say, loop through every resource in my project, in this active project. And then as you’re working with the first resource, do something with it. And when you do something with it, you can refer to that resource by r because that’s what we named our variable here. Right? Ira Brown: So what I want to say is now, r., and as soon as I press dot, notice it brings up, again, this little menu that I can select from all the different fields that are available for a resource. And I’m sure you’re familiar with many of those fields because they’re the same fields that are available if you were actually in the Microsoft Project user interface. Right? Ira Brown: So what field do we want? Well, we want the field called Group. So I’m just going to type in the letter G. Notice it takes me to the Gs. And there’s Group, and I’ll just double-click it. And now what do I want to do with that? Well, I want to say r.group=, okay? What do I want to set it equal to? Ira Brown: Well, I don’t want to hard-code “The Beatles.” I want to set it to what the user typed in the text box when they ran that form. Right? So how do I do that? How do I refer to that text box? Well, I refer to it by name. I will say txtGroup. I did that little trick again, Ctrl space bar. And I just say, set it equal to whatever’s in txtGroup. Then I hit a dot again. And the particular property, as it’s referred to, that I want to reference now is called the Text property. The Text property is the text that the user typed in the text box when they ran this form. Ira Brown: So what this says is, for every resource in my project, whatever they typed in that text box, set the Group field for that resource equal to that value. And then maybe we’ll do something at the end and we’ll put in a message box like we did last time. MsgBox, and then in quotes, “The resource group has been set successfully.” And we end it in a quote again. And we’re going to type in a comma. Ira Brown: And then this is the icon that I can display in a message box. And I’ll pick the icon that’s referred to as vbInformation. That’s the little icon that has an “i” on it. There we go. Ira Brown: So this is looking pretty good. Now, as a good general rule is before we run this routine here, we want to back it up first. Just in case something happens, we don’t want to lose all of our work. Ira Brown: So to back it up, I right-click on the form, I say Export File. And now I just pick a location, and I’ll just use my desktop for now. And I’ll just save it on my desktop, and I just click Save. Right? So now I’ve backed it up. All right. So we’re going to do one more thing here. Remember I said that we need some ability to assign this to the ribbon. Right? So that when the user clicks the button, it knows to display this form. Ira Brown: Well, what we need to do is we need to write this other little routine. So I’m going to go into one of the modules we created. Actually, I’ll create a new module. Let’s just say Insert Module. And I’ll name this module modPWMPUGPart2. There we go. Ira Brown: Name conflicts. Oh, I typed a one, I guess. I meant to type a two. Let me try that one more time. modPWMPUGPart2. Much better. Okay, good. So there’s our module. And now I can just create this one other routine. Let me just create it, and then I’ll explain what it’s doing. Ira Brown: I’m going to say Sub ShowGroupForm. Press Enter. Notice when I do that, it automatically puts in an End Sub. So it’s smart enough to do that on its own. I’ll hit my tab key. And now I’m going to reference the form by name. I’m going to say frmGroup.Show. There we go. Ira Brown: So this becomes the macro that I can assign to the ribbon. And then its job is to show this form that we just created here. All right. Looks like this is all falling into place very nicely. Let me export that module that I just created. Okay. Ira Brown: So now let me go back to the main Microsoft Project. I’m going to just minimize the VBA window. All right. So I’m going to go back to here and I am going to add that as a button to the ribbon. Okay. So remember how we do that? We right-click, customize the ribbon, we pick Macros. There’s our macro called ShowGroupForm that we just created. Ira Brown: I’ll add that to the Widgets tab by clicking the Add button. And then I’m going to click Rename and I’m going to maybe call this something like Set Resource Group. Okay. Ira Brown: And now we just have to pick a nice icon. Let’s see if we can find one here. Maybe that one. That’s not a bad one. And I’ll click Okay, and then click Okay. All right. Ira Brown: And notice, by the way, I’m not exactly sure why it made the decision to do that now, but it made these buttons here a little bit smaller. But that’s okay, we can still run it. So shall we give this a try? So let’s click the button. Let’s see what happens. Ira Brown: So it just brought up our form. You see that? Now, what if I change my mind? What if I don’t want to do it? I want to press the Cancel button. Let’s try it and see what happens. Form goes away, just like we want. Right? Ira Brown: We really do want to do it, right? So let’s click the button again. And it says, “Please enter the resource group.” So I am going to enter Beatles and then click Okay. And now we see what happens. “The resource group has been set successfully.” And notice that Beatles now displays in the group field. Pretty cool. Ira Brown: Now, I realize I forgot to do one thing. We don’t want the user to still see the form here, do we? We want to make the form go away once it’s done its job. So let me hit Cancel for now. We don’t want the user to have to do that as a second step. Ira Brown: So what I’m going to do is I’m going to go back to my Visual Basic Editor. And let me go back to the code associated with the OK button. I’m just going to double-click it. And you see right after that message box here? I’m going to put in one more line, just like we did for the Cancel. I’m going to type in Unload Me. Right? Ira Brown: So after it does its job, that will make the form go away. So I think we should be in good shape. Now, of course, we’d want to test it. Let me clear The Beatles from here. Let’s run it again. Set resource group, put in Beatles, click Okay. It gives me my message box. Click Okay. And now the form goes away. So this is looking pretty good. This is exactly what I wanted it to do. Okay. Ira Brown: So we have one more case study that we want to do around setting the user interface. But I think I’m going to save that for next time because we probably won’t be able to get through it adequately this time. But that’ll give us a little bit more time for questions at the end here. Ira Brown: So next time we get together, in addition to showing you ways that we can integrate Microsoft Project with other Microsoft Office applications… The next session is really an exciting one because we’re going to see how can we get Microsoft Project to interact with Microsoft Word, or maybe to get Microsoft Project to create a PowerPoint presentation, or maybe pull in information from Excel all through VBA. Ira Brown: So that’s what we’re going to be covering next time. But we’re also going to be covering one more example here where we’re going to see how we can build a user interface to properly set the resource initials. Ira Brown: Notice that when you put in someone’s name in Microsoft Project, it only puts in the first initial of their first name. It, for whatever reason, doesn’t put in the first initial of their last name. Well, we can overcome that limitation by creating our own functionality to set the initials appropriately. So we’re going to see how to do that next time, okay, which I hope you all decide to join us next time, it would be great. Ira Brown: As a reminder, we are going to be giving away three more copies. We gave away three last time and we’re going to do the same thing this time, three more free copies of the Driving Path Widget. So with that, I would like to thank you very much for attending today’s session. And I’m going to turn it back over to Melanie and see if we have any questions from anybody. Melanie: We do, Ira. So I’ll leave you control of the screen in case there’s anything you want to show. One of our questions is from Jeremy. What is the advantage/disadvantage of having multiple macros in the same module? Ira Brown: Jeremy, that’s an excellent question. I would say that when you’re using the macro recorder, by default, every time you create a new macro, every time you record a macro, it puts it in its own module. That’s just the default behavior of Microsoft Project. Ira Brown: But what I tend to do is I tend to take those routines, those macros, and I tend to put them into a module of other similar or related kinds of routines that I’ve written. So you don’t really just have to have one routine per module. You can have 10,. You can have 50. It doesn’t matter. But it’s a way of organizing the routines that you’ve created. Ira Brown: So you can put maybe macros that are designed to manipulate resources, maybe they can go into one module. And then macros that are designed to manipulate tasks can go into a different module, and whatever else you may want to come up with. So I think you want to organize them in a way that’s logical, that makes sense for whatever it is that you’re creating. Melanie: Thank you. All right. So this question is from Joan. This may have been covered in the first demo, but if you’re not familiar with VBA code, what do you recommend for learning? Ira Brown: Well, that’s a very good question. Well, there are a number of resources that I’ve run across from time to time that are available. There are a few people that have written books about VBA and a couple of people have even written books about VBA for Microsoft Project. Ira Brown: One of the things I didn’t mention yet, I kind of alluded to it a little bit today, but this macro language that we’re using, VBA (Visual Basic for Applications), that’s the same macro language that Word uses, that PowerPoint uses, that Excel uses, that Outlook uses. So it’s a common macro language. Ira Brown: So once you learn the macro language for Project let’s say, then you’ll understand how to create macros in Word. But instead of… You can imagine if you’re in Project, you’re working with tasks and resources and assignments and calendars, whereas when you’re working in the macro language with Word, you’re working with things like documents and paragraphs and words and fonts. When you’re in Excel, you’re working with things like rows and columns and cells. Right? And formulas. But it’s the same macro language just applied to that particular application. Ira Brown: So getting back to how do you learn about it, I recommend probably just going out and doing a Google search on Microsoft Project VBA. One of my colleagues that’s been in the business for a long time, a guy named Rod Gill, we’ll give a shout-out to Rod, I know he’s written a really good book about Microsoft Project VBA. You can try to see if you can get a copy of that one. And I know there’s numerous other resources that are available, that you can find if you do a search for them. Melanie: And Rod, by the way, has done some sessions for MPUG as well on VBA. So in that 600 hours of training we have out there, you will find some articles and sessions by Rod. Another question, is Microsoft moving away from VBA? Ira Brown: Well, that’s an interesting question also. Microsoft VBA has been around since, I believe, Version 4.0 of Microsoft Project, which is going back to the early 1990s, something like that. And it’s been with every version of Microsoft Project since, including when we started with standalone Project, then all these different versions of Project Server came along, then all these different versions of Project Online came along. Ira Brown: And the macro language has been a part of the client application. Remember, we’re working with inside of the main client Microsoft Project application now, so that’s where you would use VBA. Ira Brown: Now, Microsoft’s latest offering, which is called Project for the Web, is a web-based application to do some basic Project management work. Well, that doesn’t use VBA. That will use some other techniques for working with Project for the Web programmatically. Ira Brown: You can build things like Power Apps and Power Automate and other techniques for automating Project for the Web. But as long as we have a traditional Microsoft Project application – and I don’t see that going away anytime soon, I would imagine it’s going to be around for many, many more years to come – then we will have VBA. Melanie: Thank you. I think that is it for our questions today. A big thank you, Ira, for an excellent session again. A big thank-you to… Oh, go ahead. Sorry. Ira Brown: No, I was just saying you’re very welcome. Thanks, everybody, for attending. Melanie: Wonderful. So we would like to also thank our MPUG community here today as well. Thank you for coming today and thank you for choosing MPUG to grow your skills today. The PDU activity code, again, it’s up on the screen here for you. And I will chat that out once again. Melanie: We will send a link to this recording and a super, super short survey afterwards. Please always let us know what you think. We hope you join us next week for part three, using VBA to integrate Microsoft Projects with other applications. As Ira mentioned, that is a pain point we hear across our audience, so we’re excited for that. Melanie: And I also just want to say congrats to Lori in Wisconsin for winning an MPUG pug today. Again, thank you. And I will keep the PDU code up here and these ending slides for a little bit in case anyone needs to copy that down. Thank you again.

Webinar : Exploring VBA: Microsoft Project’s Macro Language: Part II Using VBA Forms to Create a Custom Interface

Webinar : Exploring VBA: Microsoft Project’s Macro Language: Part II Using VBA Forms to Create a Custom Interface

Project Management Institute (PMI)® Professional Development Units (PDUs): This Webinar is eligible for 1 PMI® PDU in the Technical category of the Talent Triangle. Event Description: In this session Ira will demonstrate how to use VBA to create custom, user-friendly forms as a front end for your VBA functionality. Learning Objectives: Part 2: Using VBA Forms to Create a Custom User Interface * Introduction to building a user-friendly interface for your VBA functionality * Ability to create a custom VBA form * Ability to launch your VBA form from a ribbon button Presenter: Ira Brown is a leader in the field of project management and a recognized Microsoft Project expert, Project Widgets is well-known for offering add-on products for Microsoft Project and Project Online, as well as for creating custom solutions that meet their client’s unique business requirements.  This company continues to extend the scope and breadth of their offerings, thereby increasing the value they provide to customers, by creating Microsoft Project solutions that are tailored to an organization’s unique needs.  They even have several free, downloadable widgets available on their website that you can begin using right away. Have you watched this webinar recording? Tell MPUG viewers what you think! [WPCR_INSERT]

Exploring VBA: Microsoft Project’s Macro Language – Transcription

Exploring VBA: Microsoft Project’s Macro Language – Transcription

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. […]

Webinar : Exploring VBA: Microsoft Project’s Macro Language

Webinar : Exploring VBA: Microsoft Project’s Macro Language

Project Management Institute (PMI)® Professional Development Units (PDUs): This Webinar is eligible for 1 PMI® PDU in the Technical category of the Talent Triangle. Event Description: The initial session will provide an overview of working within the VBA environment, including the basics of recording and editing VBA macros. * Part 1: Working with Macros and VBA Fundamentals * Ability to create basic VBA macros * Ability to edit VBA macros * Proficiency in assigning a macro button to a custom ribbon Learning Objectives: • Learn how to produce several useful macros • Become proficient in how to assign a macro to a custom ribbon button Presenter: Ira Brown is a leader in the field of project management and a recognized Microsoft Project expert, Project Widgets is well-known for offering add-on products for Microsoft Project and Project Online, as well as for creating custom solutions that meet their client’s unique business requirements.  This company continues to extend the scope and breadth of their offerings, thereby increasing the value they provide to customers, by creating Microsoft Project solutions that are tailored to an organization’s unique needs.  They even have several free, downloadable widgets available on their website that you can begin using right away. Have you watched this webinar recording? Tell MPUG viewers what you think! [WPCR_INSERT]

Streamline Project Reporting with Microsoft Project Macros

Streamline Project Reporting with Microsoft Project Macros

Project Management technology should never be more complicated then managing a project! Did you know that using macros can enhance the efficiency of Microsoft Project significantly? Of course, the thought of writing and recording macros to meet your unique needs can often prove challenging to those of us who are not technical programmers. I’d like to suggest that the average business user can easily customize Microsoft Project with macros to maximize its value. Record a Macro Macros help you to automate tasks that may be tedious and time consuming, while also reducing the potential for errors. We’ll use a simple example to demonstrate how this works – the creation and printing of a status report that shows all completed tasks in your project. When preparing this report manually, follow these steps: Apply the Gantt Chart view to your project. From the View tab in the ribbon, click the Filter dropdown list, and then select Completed Tasks. 3. Print the displayed list of completed tasks: – From the File menu, click Print. – Customize the printed view by clicking Page Setup, modifying the layout as follows, and then clicking Print. 4. To remove the applied filter, change the Completed Tasks filter to [No Filter]. Now it’s time to automate the four steps above.  From the View menu, click Macros > Record Macro. In the Record Macro dialog, name your macro: PrintCompletedTasks, and click OK. Note that once you click OK, all following steps will be recorded until you click Macros > Stop Recording. Run through Steps 1 – 4 above, following the process you manually use to create and print this report. Then click Macros > Stop Recording. Assign a Macro to a Button in the Ribbon Adding a button to the Microsoft Project Ribbon to initiate this macro ensures that creating your report is as simple as clicking a button. Right-click in the ribbon area, and select Customize the Ribbon. In the left column of the Project Options dialog, select the recently created macro and move it to the right column (select your preferred location there) by clicking Add. Once the macro has been added to the ribbon, you can select it and click Rename… to provide a more user-friendly name and associated icon. 4. Click OK and you will see your new macro.  Simply click the Print Completed Tasks button and print your report. I will dive deeper into this topic in my upcoming three-part course, Exploring VBA: Microsoft Project’s Macro Language. We’ll cover how to effectively use Microsoft Project’s macro capabilities for various tasks.

Working with Microsoft Project Master Schedules and Subprojects

Working with Microsoft Project Master Schedules and Subprojects

  Project Management Institute (PMI)® Professional Development Units (PDUs): This Webinar is eligible for 1 PMI® PDU in the Technical category of the Talent Triangle.   Event Description: Microsoft Project provides the ability to integrate individual projects into a single master schedule. A master project, which is often used to represent a program, provides numerous capabilities to users: View the master project and each of the subprojects it contains in a single schedule Perform consolidated project reporting across the master project Create interproject dependencies among the tasks in the master project In this webinar, Ira Brown will demonstrate the fundamentals of creating and using master schedules in Microsoft Project. You will learn how to create interproject dependencies and how to configure your master project properly so that you can specify how linked tasks are updated across subprojects. And finally, you will learn how to use graphical indicators to highlight the status of critical activities across the program.   Presenter Info: Ira Brown, the founder and president of Project Widgets, is a leader in the field of project management, and is a recognized Microsoft Project expert. Project Widgets is well-known for offering add-on products for Microsoft Project and Project Online, as well as for creating custom solutions that meet their clients’ unique business requirements. This company continues to extend the scope and breadth of their offerings, thereby increasing the value they provide to customers by creating Microsoft Project solutions that are tailored to an organization’s unique needs. They even have several free, downloadable widgets available on their website that you can begin using right away   Have you watched this webinar recording? Tell MPUG viewers what you think! [WPCR_INSERT]