Calendar Exceptions and Effective Work Weeks

This content is outdated and is no longer being maintained. It is provided as a courtesy for individuals who are still using these technologies. This page may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist.

Microsoft Office Project 2007 introduces effective work weeks and calendar exceptions, where all calendar data is associated with a set of "valid from/to" dates. Each calendar object has default values that are set when you define the calendar. You can add exception sub-calendars at any time. This article includes examples of macros that create calendar exceptions and work week dates and shows how to handle errors that can result.

If you do not use effective work weeks, calendars behave essentially the same as in earlier versions. In earlier versions of Project, it is sometimes difficult to accurately specify working calendars when a resource has many different working periods on different days or weeks.

Effective work weeks can begin on any arbitrary date, not just the week start date. Figure 1 shows the Exceptions tab for resource working time. Figure 2 shows the Details dialog box for creating a named calendar exception.

Figure 1. Working time exceptions for effective work weeks

For example, the month of March, 2006, includes exception days for the named exceptions Winter and Spring. The Winter exception is valid on Mondays through Wednesdays, and the Spring exception is valid on Thursdays and Fridays. In addition, March 20–22 is a non-default work week where Monday and Tuesday are non-working days and Wednesday is a short working day.

Figure 2. Exception Details dialog box

The Project 2007 object model includes the following new objects, properties, and methods for creating and managing calendar exceptions. For a list and descriptions of all the new class members, see Table 2 in Tables of VBA Object Model Changes.

• Calendar.WorkWeeks property contains the WorkWeeks collection

• Calendar.Exceptions property contains the Exceptions collection

• Calendar.OMID property is used for sharing objects across multilanguage versions

• WorkWeek object and WorkWeeks collection object: 6 properties and the WorkWeeks.Add and WorkWeek.Delete methods

• WorkWeekday object and WorkWeekdays collection object: 12 properties and the WorkWeekday.Default method

• Exception object and Exceptions collection object: 19 properties and the Exceptions.Add method

The following sample macros add a calendar exception and a non-default work week for a specified resource. The results match the calendar exceptions in Figure 1.

Sub AddException()
    Dim cal As Calendar
    Set cal = ActiveProject.Resources(1).Calendar
    
    ' Exception bitmask for Mon, Tue, Wed is 00001110, or 0xE
    ' Exception bitmask for Thurs, Fri is 00110000, or 0x30
    cal.Exceptions.Add Type:=pjWeekly, _
        Start:=#12/21/2005#, Finish:=#3/17/2006#, _
        Name:="Winter", DaysOfWeek:=&HE&
    cal.Exceptions.Add Type:=pjWeekly, _
        Start:=#3/20/2006#, Finish:=#6/16/2006#, _
        Name:="Spring", DaysOfWeek:=&H30&
        
    cal.Exceptions("Winter").Shift1.Start = #8:00:00 AM#
    cal.Exceptions("Winter").Shift1.Finish = #12:00:00 PM#
    cal.Exceptions("Winter").Shift2.Start = #1:00:00 PM#
    cal.Exceptions("Winter").Shift2.Finish = #5:00:00 PM#
    
    cal.Exceptions("Spring").Shift1.Start = #7:00:00 AM#
    cal.Exceptions("Spring").Shift1.Finish = #11:00:00 PM#
    cal.Exceptions("Spring").Shift2.Start = #12:00:00 PM#
    cal.Exceptions("Spring").Shift2.Finish = #4:00:00 PM#
End Sub

Sub AddWorkWeek()
    Dim cal As Calendar
    Dim day As String
    Dim eWeekDay As WorkWeekDay
    
    Set cal = ActiveProject.Resources(1).Calendar
    cal.WorkWeeks.Add #3/20/2006#, #3/23/2006#, "Spring vacation"
    
    For Each eWeekDay In cal.WorkWeeks.Item("Spring vacation").WeekDays
        day = eWeekDay.Name
        Select Case day
            Case "Mon"
                eWeekDay.Working = False
            Case "Tue"
                eWeekDay.Working = False
            Case "Wed"
                eWeekDay.Working = True
                eWeekDay.Shift1.Start = #9:00:00 AM#
                eWeekDay.Shift1.Finish = #12:00:00 PM#
                Exit For
        End Select
    Next
End Sub

The For ... Next statement iterates on WeekDays using the eWeekDay variable. The variable must be a WorkWeekDay because "Spring vacation" is a WorkWeek.

Error Handling

Error 1101, "Runtime error," is common when you are developing and testing calendar exceptions and work weeks. Following are some examples that result in an 1101 error.

• Assign a shift start time that is later than the finish time.

• Create overlapping work weeks.

• Run the same macro more than once to create a work week. You must remove the work week item before you can run the same code again, or the code would try to add a work week with the same dates.

You can add code to help make the error messages more useful. To see the error message (Figure 3) with the following code, in the Visual Basic Editor, click Options on the Tools menu, and then click the General tab. Click the option Break on unhandled errors.

' Error message test. Try adding calendar exceptions where required 
' arguments are missing, or there are conflicting arguments such as 
' a Start date after the Finish date.
Sub ErrorTest()
    On Error GoTo Err
    ActiveProject.Resources(1).Calendar.Exceptions.Add _
        Type:=pjDaily, Start:=#3/20/2006#
    
Err:
    Dim errMess As String
    errMess = "Error description -> " & Err.Description & vbCrLf & vbCrLf _
        & "Help file -> " & Err.HelpFile & vbCrLf & vbCrLf _
        & "Help context ID --> " & Err.HelpContext
    If Err Then
        MsgBox errMess, vbApplicationModal, "Error"
    End If
End Sub

Figure 3. Error message box example

See Also

Reference

Tables of VBA Object Model Changes