Creating a Macro That Works in Different Situations

The macro you recorded in the preceding section will work in every situation because the objects it operates on are not specific to any one project. Zooming for an entire project will work for any project. With a simple change in the options, however, you can create a macro that can be recorded with no problems but does not work in all cases. This section walks through a similar example and shows how to correct the problems that occur when you record a specific macro.

This time, rather than using the Zoom, Entire Project command, you will use Zoom, Selected Tasks. This might be a more useful option when you want to look at a set of particular tasks you are working on and the duration of those tasks is short compared to the entire project.

The process is the same as recording the previous macro:

1. Start the macro recorder and name the macro ZoomSelected . Store it in the Global template as before.

2. This time, there is an additional step. Because the command acts on a selection, you must first select the tasks you are going to zoom. Select a few tasks by dragging your cursor across them so that they are highlighted, and then select View, Zoom, Selected Tasks, and then click OK.

3. Stop the recorder.

To test the new macro, reset the timescale so that the whole project is showing, and then select a few tasks (select different ones from the ones you selected while recording the macro) and run the new macro. Project zooms, but it zooms to show the tasks you selected when recording the macro and ignores the tasks you have just selected. Because this macro is expected to zoom the timescale for the selected tasks and it ignores your current selection, it is clearly not working correctly.

The problem with this macro is what the recorder is recording. To see what is recorded, open the Visual Basic Editor (VBE). To open the VBE, press Alt+F11 or select Tools, Macros, Visual Basic Editor.

When you open the VBE, you see something similar to Figure 1. In the upper-left corner is the Project Explorer. Because you saved your macro in the GLOBAL.MPT file, you need to expand that file in Project Explorer and look in the Modules folder. Project creates a module for each macro you record. The highest-numbered module is the last one you recorded. If this is your first time recording macros, you should see Module1 , which contains the first version, and Module2 , which contains the second version. Double-click the highest-numbered module (it is likely to be Module2 ) to open it to display the code window.

Figure 1. The default view of the Visual Basic editing screen is divided into three main areasthe Project window and Properties window on the left and the Code window on the right.

TIP

Project does not do a good job of naming modules for you. It supplies names such as Module1 and Module5 . If you have some macros you want to use or work on later, you should change the name of the module to something more appropriate so that you can easily find it. You can't change the name in the Project Explorer window, but you can change it in the Properties window. It opens by default just under the Project Explorer window, but if you don't see it, you can display it by pressing F4 or selecting it in the View menu. To rename the module, just type over the existing name.

When a module opens, you see what was recorded by the recorder. A number of separate actions are grouped together as a macro or, more precisely, a procedure. Each line in that procedure is a comment, a single statement, or both. Every macro you record begins with a line stating that it is a subroutine (that is, a type of procedure), followed by the name of the macro and any parameters that the macro operates on. In the case of the ZoomSelected macro, you see the following code:

 Sub ZoomSelected() ' This macro will zoom the timescale to show all the selected tasks     SelectRow Row:=1     ZoomTimescale Selection:=True End Sub 

The first line contains the name you gave the macro. Immediately after this line is the description that you entered or that Project entered for you. The description is in the form of a comment, so it begins with an apostrophe ( ' ). Everything following on that line is interpreted as a comment. You can create as many comments as you like, and a comment can be on the same line as another statement, but it must follow the statement. Following this are the actions to occur and at the end: A line states end sub , which indicates the end of the macro. You can see from this code that you selected a task and then performed an action on the timescale based on that selected task.

The problem here is that you made the selection of the task part of the macro, so whenever the macro is run, that task is again selected. Although you could go back and rerecord the macro, the VBE enables you to simply delete the parts of the macro you don't want. In this case, you need to delete the line that starts with SelectRow .

After you make changes in the VBE, switch back to your project, select a few tasks, and run the macro again. At this point it should work fine. Try not selecting any tasks, and then run the macro again. Project generates an error (see Figure 2). In this case, click End. Debugging is covered later in this chapter.

Figure 2. Error windows present you with some detail about what the error is and allow you to end, debug, or go to help.

The problem is that you are asking Project to perform an action, but you haven't selected anything that it can use to perform that action.

The Object Model

Visual Basic works with a set of objects. Objects are the elements of the application such as tasks, resources, views, or specific cells . Groups of objects are called collections. A set of selected tasks is a collection of tasks, but a collection does not need to be made up of similar items. The application object is a collection of tasks, resources, assignments, and other objects.

When you are writing statements, you must first specify the object you are going to do something to or with, and then you state what you want to do with it. Most commonly you will want to add or delete an object or change something about it. The various aspects of the object are called properties .

Whereas a property of a car would be the color or the weight, a property of a task would be the duration, the cost, the task ID, or another value that could be associated with a particular task. In most cases you can change the properties of an object, just as you can change the duration of a task in Project. However, in some cases, you cannot change the properties of an object because they are the result of a calculation that Project has done. Total Slack is an example of a read-only property. You cannot change it by setting it to a specific value because the value of Total Slack is the result of a calculation by the scheduling engine. (In general, if you can directly edit the value of a field in one of the project tables, then you can set that same value by using VBA.)

Objects also have methods . If we use the car analogy again, starting a car would be a method, and so would accelerating. Using a method with an object generally has an effect on one or more properties. For example, accelerating a car would increase the speed, which is a property. Some important methods handle the creation and destruction of the objects themselves . You add a new task to a project by using the add method of the Project object.

Knowing the object model is the key to becoming proficient at programming in VBA.