Project Management

Use our macro to stop MS Project's day-span function

If you've been frustrated by Microsoft Project's tendency to split some project tasks into the next day, we have a macro that can stop that from happening. We'll show you how.

If you use Microsoft Project to schedule tasks, you’ll notice that the application can sometimes force tasks to span unnecessarily into the following day. In this article, we’ll provide a macro that removes “day span” from your project tasks.

Second of two macros
This is the second of two macros that shows Microsoft Project users how to disable the application’s day-span function. To learn more, check out last week’s macro.

How it works
This macro is a variation of the macro I told you about in my last article that can be run against specific tasks. The macro shown below is a function that accepts one argument: TskID, which is the ID of the task the macro should be run against.

Unlike the previous macro, the one below does not require the Flag1 field to be set because it assumes that any Task ID you give it is a task for which it should try to remove the span.
Function funcAntiDaySpan(TskID As Long) As Long
'1 = Span Removed
'2 = Task Not Spanning Days
'3 = Task Duration > 1 day
'4 = Task ID Invalid
'Or The Error number of the error thrown
On Error GoTo ErrorHandler
Dim tskT As Task
Dim lngMperDay as long
Set tskT = ActiveProject.Tasks(TskID)
lngMperDay = ActiveProject.HoursPerDay * 60
If (tskT.Duration < lngMperDay) Or tskT.Duration = lngMperDay Then
    If Format(tskT.Start, "Short Date") < _
    Format(tskT.Finish, "Short Date") Then
        tskT.Start = Format(tskT.Finish, "Short Date")
        funcAntiDaySpan = 1
        funcAntiDaySpan = 2
    End If
    funcAntiDaySpan = 3
End If
Exit Function
    If Err.Number = 1101 Then
        funcAntiDaySpan = 4
        Exit Function
        funcAntiDaySpan = Err.Number
    End If
End Function

The macro explained
This function returns one of four possible values: 1, 2, 3, or 4.
  • 1 means that the Span was removed from the task.
  • 2 means that the task was not spanning days, so nothing was done.
  • 3 means that the task’s duration was greater than one day, so nothing was done.
  • 4 means that the supplied Task ID was not valid, so nothing was done.

The only other possible values would be the error number of any error that might get thrown by the function.

Line 7 sets the error trap, and lines 8 and 9 define the two variables used by tskT, which will be the task that is passed to the macro, and lngMperDay, which will be the number of minutes in the default working day of the active project.

Lines 11 and 12 set the value of these variables.

Line 14 checks to see if the Task duration is less than or equal to the variable lngMperDay. If it is not, then Line 24 sets the return value of the Function to 3, which means the task duration was more than lngMperDay. If the duration is equal to or less than lngMperDay, lines 15 and 16 check to see if the task is spanning days. If it is not spanning, then Line 20 sets the return value to 2, which means the task was not spanning. If the task is spanning, Line 17 sets the Start of the task to the start of the following day.

Lines 28 through 34 are the error handler. They check the raised error, and if it is error 1101, Line 30 then sets the return value of the function to 4. This error number is the error raised when an invalid Task ID has been passed to the function. If any other error is raised, Line 33 sets the return value to equal that error number.

You would call this function as shown in the example below:
Sub CallAntiDaySpan()
Dim lngL As Long
lngL = funcAntiDaySpan(TskID:=1)
MsgBox lngL
End Sub

This macro sets the value of the lngL variable as the value returned by the funcAntiDaySpan function. This line passes the TskID value of 1, so it will try to remove the span from Task ID 1. It then displays the resulting value in a message box using the msgbox function.

The subroutine covered first in this article is useful if you want to mark your tasks and then remove the Span from the whole project. The function is good if you want to programmatically specify which tasks should have their span removed. This macro is also a good example of how you can have the same functionality provided by both a subroutine and a function for use in different situations.

Do you have a tip?
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