Project Management

Stop Microsoft Project's 'day spanning' on short tasks with this macro

If you've used Microsoft Project, you've noticed that it tends to split some tasks into the next day. While not always a problem, this can cause your schedule to inaccurately reflect what your day will be like. This macro shows you how to work around it.

With IT projects, you'll often find it necessary to schedule a string of very short tasks. If you use MS Project, you’ve noticed that the application tends to split these tasks, forcing half of a task to span over to the following day.

Distributing tasks in this way is not always a problem, but some tasks don't lend themselves well to this division. For example, a task such as “Install Service Pack on Server” is one that could not be done half one day and half the next day. This task would need to be started the next day. But through Microsoft Visual Basic for Applications (VBA), you can automate the moving of such tasks. I'll describe a macro you can run that will help you prevent your short tasks from spanning days.

First of two macros
This is the first of two articles that feature macros that can help you better organize tasks in Microsoft Project.

Removing "day span"
This macro will remove “day span” from any task in your project that has the Flag1 field set to True and is equal to or less than 1 day in duration and spans a day (that is, it starts on one day and ends on the next day).
Sub AntiDaySpan()
Dim tskT As Task
Dim lngHours As Long
On Error GoTo ErrorHandler
lngHours = ActiveProject.HoursPerDay * 60
For Each tskT In ActiveProject.Tasks
    If Not (tskT Is Nothing) Then
        If (tskT.Flag1 = True And tskT.Duration = lngHours) _
        Or (tskT.Flag1 = True And tskT.Duration < lngHours) Then
            If Format(tskT.Start, "Short Date") < _
            Format(tskT.Finish, "Short Date") Then
                tskT.Start = Format(tskT.Finish, "Short Date")
            End If
        End If
    End If
Next tskT
Exit Sub
    MsgBox Prompt:="AntiDaySpan Macro Error: " & Err.Number, _
    buttons:=vbExclamation, Title:="AntiDaySpan Error"
End Sub

Explanation of the code
Lines 3 and 4 set up the two variables the macro will need: one Task object variable and one Long number variable. Line 5 sets up the error trap so our users will not get any “rude” errors.

Line 7 sets the value of the lngHours variable to equal the number on minutes in the active project’s default working day. We need to convert to minutes because the contents of the Duration field in Project are stored in minutes.

Line 9 starts our loop through each task in the project, while line 10 tests to make sure the line is not blank. The macro must test for this, because otherwise, it would error out on the first blank row in the task list.

Lines 11 and 12 are the main test of the macro. This line tests each task to see if the Flag1 field is set to True and if the Duration field is less than or equal to the value of the lngHours variable (the number of minutes in the default working day). If Flag1 is True and the duration is equal to or less than lngHours, lines 13 and 14 test to see if the task finishes the day after it starts. If it does, line 15 sets the start of the task to the default starting time on the day the task now finishes.

Lines 21 through 24 will display an error message and end the macro in case of an error.

This macro can be run manually or can be inserted into one of the VBA events, such as Change, so that it runs each time the project is changed.

Note: It is important to remember to set the Flag1 field for those tasks that you know should not span days.

Send us your tips
Our members are always looking for more information on Microsoft Project. If you have suggestions or tips that you think would benefit others, send them to us. We’ll publish them in later articles.


Editor's Picks