You may have read or heard about Microsoft Visual Basic for Applications (VBA) and its value for users of Microsoft Project; however, you might be saying to yourself, “Yikes! I’m not a programmer.” You figure since you do support, why should you know VBA? Well, the more you know about an application, the better you can support it. Knowing VBA gives you a deeper understanding of how Project works, which can help you when you troubleshoot problems.
How many times have you asked a user to check the value of a field, only to learn later that they gave you the wrong value because they misunderstood or misread the field name? With an understanding of VBA, you could write a macro that reads the values you need. You could send it to users, have them run it, and then have the users read the values to you from the screen. But before we can do that, you need a basic understanding of VBA.
Understanding objects and properties
The heart of Microsoft Project, as far as VBA is concerned, is made up of objects. Objects represent all the important parts of Project. Each part of the application is broken down into one or more objects. These objects are then described by properties, which are bits of information like the name of a task or the state of a setting. One type of object is the application object, which represents the part of MS Project that is general to all individual projects and not in or under the project object. The project object is the parent object for all things within a given project. The most frequently used objects in MS Project are tasks, resources, and assignments. These objects make up 80 percent of any programming done in MS Project. The set of objects in any application is represented in an object model.
The object model
The object model is like an outline of a document. An outline breaks down a document into sections, just as an object model breaks down an application into sections and components. The easiest way to find this information in the MS Project Help system is to search your machine for a file called “VBAPJ9.CHM” for Project 2000 users. This will show you the whole Project 2000 object model.
Once you understand how the objects are put together, you can start with the nuts and bolts. These are the tools for reading data, changing data, and acting on objects. Examples of these tasks include reading the value of an option setting, or a task field, changing such a value, or acting on an object by adding a new task or creating a new calendar. These tasks are done with properties and methods. We have discussed properties a bit already. A method is something a bit more complex. It’s an action you can use to change an object or even add new objects. For example, you would use the Add method to add new tasks, resources, assignments, and even projects.
The macro recorder
Now that we have a basic lay of the land, we can get started. The best way to gain an understanding of how VBA corresponds to the user interface of Project is to use the Macro Recorder feature of MSP. Using this feature, you can perform actions in Project, and then open the VBA Editor and view the VBA code that would be used to perform those same actions. It is a great way to learn the basics of VBA.
To make the best use of this feature, you should have a plan for what actions you want to record. First, make the Visual Basic toolbar visible. This toolbar will make it easier to access the recorder and the VBA editor. Right-click on any toolbar, and then pick Visual Basic from the menu. To start the recorder, just click on the Record Macro button on the Visual Basic toolbar. It is the blue circle. This will bring up the Record Macro dialog.
|Record Macro dialog box|
|The Record Macro dialog lets you specify information about your new macro.|
“Macro name” is self-explanatory. The “Store macro in:” field determines if the macro should be kept in the Global.mpt file or in the currently active project file. You can use the Description area to explain what you expect the macro to do for you. Row and Column settings control how the macro should interpret changes in the active selection in the views. For example, if row 5 were selected when you started recording and, in the course of recording, you selected row 10, then when the macro was run, if the selection was on row 2, the macro would move the selection to row 7 because you previously changed the row by plus 5. Be aware of this when you record macros where you change row selection.
Recording a macro
Now let’s record a macro and look at the code. Once you do this, you will be able to really start looking at the application in a different way. We will start by recording a macro that will change the view to the Tracking Gantt, print five copies for a date range from 9/10/00 to 9/15/00, and then change the view back to the Gantt Chart view.
- Click the Record Macro button.
- In the Macro Name field, type “PrintTest.”
- In the Description field, type some description.
- Click OK.
- Change the view to the Tracking Gantt.
- Click File | Print.
- Choose the 9/10 to 9/15 date range.
- Enter 5 in the Copies field.
- Click Print.
- Change the view to the Gantt Chart view.
- Click Stop on the VBA toolbar.
Now click on the Visual Basic Editor button on the VBA toolbar. When it opens, you will see something like this:
In the Project Explorer, expand the ProjectGlobal (Global.mpt) entry by clicking on the plus sign. Then expand the Modules entry, and you should see something like this:
|The Project Explorer portion of the VBA Editor showing the expanded ProjectGlobal Project|
Double-click on the Module1 entry (or the Module entry with the highest number following it). You should now see your recorded macro as it appears in the Code window below.
|The Code window with your new macro|
There are three parts to this macro. The first is the ViewApply method. This method acts upon the application object. It tells the application to apply the view specified with the Name parameter. Next is the FilePrint method. This is the heart of this macro. It takes several parameters that tell the application object how to print the view. In this case, we specify five copies and a date range. We then see the ViewApply method again.
Now you have the basic tools necessary to record macros and examine them in the VBA editor. Try it out. Record your common tasks into macros, and look at the code. This process is the first step in learning to use VBA to increase your effectiveness within MS Project. Future articles will cover some more advanced VBA topics that can provide you with the tools needed to start writing macros you can use to troubleshoot user problems.
Now that you’ve read the article, let us know what you think. Should support techs use VBA to troubleshoot MS Project? Post a comment or send us an e-mail.