Calendar Exceptions and Effective Work Weeks
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.
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.
In the first release of Project 2007, saving a project to an XML file does not correctly save the WorkWeeks and WeekDay elements. For information about correcting those elements in the XML file, see the mspdi_pj12.xsd schema in the Project 2007 SDK download.
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
For ... Next statement iterates on WeekDays using the eWeekDay variable. The variable must be a WorkWeekDay because "Spring vacation" is a WorkWeek.
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