Project Management

Using VBA to tie a Project task start date to a certain day of the week

Some tasks must be done on a particular day of the week. Saturday, for example, may be the only reasonable time for a major system test. Luckily, you can avoid missing the window of opportunity by tying such tasks to a specific weekday in Project.


Sometimes, a task must start on a particular day of the week. Such tasks might include upgrades requiring a server outage, testing that can be done only when users are not online, or tasks that have to be done on a Saturday or Sunday. Unfortunately, if the task gets moved because of a delay in a predecessor task, you’ve lost your chance to perform the task on the specified weekday.

Microsoft Project provides ways to make sure that a task starts on a particular date but not on a given day of the week. This is a perfect example of how you can use VBA to add functionality to Project. This article will look at a macro that lets you specify a day of the week for a task to begin. When run, this macro will make sure that the task starts on that day of the week.

How it works
This macro requires you to use the Text1 custom field. For each task that must start on a given day of the week, you enter that day in this field. For example, the task in Figure A needs to start on Monday, which is reflected by the contents of the Text1 field.

Figure A
You enter the required weekday in the Text1 field.


When you run the macro, it sends the start date of each task whose Text1 field contains any text through the Weekday VBA function. This function returns the day of the week that the date given occurs on. The macro then compares this value to the contents of the Text1 field. If they are the same, nothing happens. The macro moves to the next task. But if they’re different, the macro adds one day to the start date until they match.

The big thing to remember here is that the day you enter into the Text1 field for any of these tasks must be marked as a working day in the default calendar for the project. If the day you enter is not a working day, the macro will return an error message. Listing A shows our macro.

Running the macro
Next, you need to consider the timing: When should the macro run? You have a few options.

The easiest but least safe approach is to run the macro when you know your tasks have moved—or you think they might have. This is just a matter of running the macro manually, but it’s somewhat risky because it relies on you to remember to run it.

The next option is to run the macro every time the project changes or is calculated. You can do this by inserting a call to the macro in the project’s Change (or Calculate) event. Figure B shows the Change and Calculate events for the ThisProject object with the code to call the macro.

Figure B
Calling the macro via a project’s Calculate and Change events


The downside to this technique is that it runs the macro often, so if your project is large, Project may run slowly. You’ll want to select an approach that’s tailored to a particular project and based on how often you think the macro will need to be run.

Seize the day…of the week
There’s no reason for your project schedule to suffer because you missed a window for some type of critical testing or upgrade. Use this macro to link those tasks to a specific day of the week, and that will be one more thing the system can manage and one less thing for you to worry about.

How do you use Microsoft Project?
As a developer, how much do you use Project? What tips and tricks do you have for keeping tabs on your projects? Send us an e-mail with your thoughts and suggestions or post a comment below.

 

Editor's Picks