Lesson Objectives
Upon completion of this lesson, the participant will be able to:
Some Topics to be introduced in this lesson include:
Calendar Object
For the objects discussed so far, almost all of the information that is available via the object model was accessible through Dynamic Data Exchange (DDE) in Microsoft Project 3.0. The Calendar object presents information which was previously only accessible through the Microsoft Project Exchange (MPX) file format.
The Calendar object contains information about when work can take place during the project including the days and the times during the day that are available for work. Additionally, each resource has its own calendar which can contain information about differences, such as vacations, from the normal calendar.
Calendar Object Hierarchy
Period Method and Object
The easiest way to access information in a calendar is to use the Period method. The Period method takes a range of dates and returns a Period object. A Period object is like a collection of days except that you cannot iterate over the days in a Period. For example, to specify Independence Day in the United States as a nonworking day, one could do:
ActiveProject.BaseCalendars("Standard").Period("7/4/94") _
.Working = False
Note that if the finish date of a range is not given, as in this example, the period returned is the one day.
The following example sets July 4th and July 5th as nonworking:
With ActiveProject.BaseCalendars("Standard")
.Period("7/4/94", "7/5/94").Working = False
End With
This method is very useful when the range you want to work with cannot be easily described by the Year, Month, and Day objects.
Year, Month, Day Objects
A more object oriented way of manipulating information in a calendar is to access the Year, Month and Day objects contained in the calendar. With these objects and the collections which contain them, one can perform more powerful operations on the calendar.
For Each y in ActiveProject.BaseCalendars("Standard").Years
y.Months(7).Days(4).Working = False
Next y
By using the objects, one can make the 4th of July a nonworking day every year. To make the code a little more readable, one could instead specify the name of the month like:
For Each y in ActiveProject.Calendar.Years
y.Months("July").Days(4).Working = False
Next y
Weekday Objects
The final way of manipulating the calendar is to access the Weekday object. Microsoft Project allows the user to specify a day of the week as a working day or as a day off. This makes it easy to make a 4 day work week instead of the standard Monday through Friday.
One could make all Fridays nonworking like:
ActiveProject.BaseCalendars("Standard").Weekdays(6).Working = False
To make the code more readable, as was done with the Month object, there are two choices. One could use a defined constant:
ActiveProject.BaseCalendars("Standard").Weekdays(pjFriday) _
.Working = False
Or use the name of the day:
ActiveProject.BaseCalendars("Standard").Weekdays("Friday") _
.Working = False
Properties and Methods
The Period, Year, Month, Day, and Weekday objects share most of the same properties and methods.
The Working property that was used in the above examples sets whether or not work can occur on the range of the calendar specified by the object.
The Shift1, Shift2, and Shift3 properties return a Shift object which allows you to specify the Start and Finish time for a shift
The Default method applies to resource calendars. It returns the object to the state of its associated Base Calendar.
An example which uses more of these properties would be one to make Fridays a half-day of work:
With ActiveProject.BaseCalendars("Standard").Weekdays("Fri")
.Working = True
.Shift1.Start = "8:00"
.Shift1.Finish = "12:00"
.Shift2.Clear
.Shift3.Clear
End With
Scheduling Methods
One of the most interesting set of methods exposed by Microsoft Project are those which give the macro writer access to scheduling capabilities. Microsoft Project provides methods to add and subtract dates as well as display dates and durations in different formats.
Date Arithmetic
By exposing date arithmetic methods in Microsoft Project, a realm of different macros are possible. It also makes it possible to include Microsoft Project as a part of an integrated scheduling solution. With these methods, a Solution Provider could use Microsoft Project as a scheduling engine by either entering the information into Microsoft Project, or by just using the date arithmetic methods directly.
DateAdd
The DateAdd method accepts a start date and a duration and returns the finish date. Optionally, you can specify a calendar to be used in the calculation. If a calendar is not specified, then the project calendar is used.
x = DateAdd("7/11/94 8:00am", "3d")
In this example, x would be 7/13/94 at 5:00pm, since a 3d activity beginning on July 11th at 8:00am would end at the end of July 13th.
DateSubtract
The DateSubtract method is the reciprocal of the DateAdd method. It accepts a finish date and a duration and returns the start date. As with the DateAdd method, you can optionally specify a calendar.
x = DateSubtract("7/13/94 5:00pm", "3d")
Being the complement of DateAdd, this example would return 7/11/94 at 8:00am.
DateDifference
The DateDifference method is related to both DateAdd and DateSubtract method. It takes two dates and returns the duration between them. Again, a calendar can be optionally specified.
x = DateDifference("7/11/94 8:00am", "7/13/94 5:00pm")
Here, DateDifference would return 1440 minutes which is 3 days.
Date Arithmetic Example
Using these methods, you can create a fairly simple macro which will adjust all of the dates in a project when your project start date slips:
Sub AdjustDates()
nCurDateFormat = Application.DefaultDateFormat
Application.DefaultDateFormat = pjDate_mm_dd_yy_hh_mmAM
sStartDate = ActiveProject.ProjectStart
sNewStartDate = InputBox(Prompt:="Enter New Start Date", Default:=sStartDate)
If sNewStartDate = "" Then End 'Canceled the InputBox
nDelta = DateDifference(sStartDate, sNewStartDate)
If nDelta = 0 Then
MsgBox "New start date must be greater than current start date."
End
End If
For Each t In ActiveProject.Tasks
If t.ConstraintDate <> "" Then
Select Case t.ConstraintType
Case pjMFO, pjFNLT, pjFNET
‘Finish Constraints are fine
t.ConstraintDate = DateAdd(t.ConstraintDate, nDelta)
Case Else
'Start Constraints are problematic.
'8:00am + 1d gives 5:00pm. Since we want 8:00am the
'next day, we add 1d+1m and then subtract the 1m
t.ConstraintDate = DateAdd(t.ConstraintDate,nDelta + 1)
t.ConstraintDate = DateSubtract(t.ConstraintDate, 1)
End Select
End If
Next t
ActiveProject.ProjectStart = sNewStartDate
Application.DefaultDateFormat = intCurDateFormat
End Sub
Date & Duration Formatting
Related to the Date Arithmetic methods are some methods which allow you to display dates and durations in different formats.
DateFormat
DateFormat takes a date and a constant specifying the format in which you want the date to appear. The available formats are the same as the default formats for displaying information in Microsoft Project.
MsgBox DateFormat("12/31/94", pjDate_mmmm_dd_yyyy)
This example would display ‘December 31, 1994’ in a dialog box.
DurationFormat
Similar to the DateFormat method, DurationFormat takes a duration and a constant specifying the units in which you want the duration to appear. The available units are minutes, hours, days, and weeks.
MsgBox DurationFormat("3d", pjWeeks)
This example would display ‘.6w’ in a dialog box.
DurationValue
Although the DurationValue method does not affect the formatting of a duration, it is closely related to the DurationFormat method. DurationValue take a string and returns the value of that string as a duration. Microsoft Project expresses durations in minutes, so 1 day = 8 hours = 480 minutes.
x = DurationValue("2d")
Here, x would be set to 960 which is the number of minutes in 2 days.
Because durations are expressed in minutes, you can add durations using normal arithmetic. For example, the following code would add the entered duration to the selected task.
x = DurationValue(InputBox("Enter amount by which to increase _ duration"))
ActiveCell.Task.Duration = ActiveCell.Task.Duration + x
strTemp = "The new duration in weeks is "
strTemp = strTemp & DurationFormat(ActiveCell.Task.Duration, pjWeeks)
MsgBox strTemp
Exercise1: (CalRangeSet)
Write a macro that schedules a Christmas break. First propmt the user for a date range, and set those days in that range to nonworking on the base calendar.
Sub CalRangeSet()
DRangeStart = InputBox("Enter the Start date of the break")
DRangeFinish = InputBox("Enter the Finish date of the break")
ActiveProject.BaseCalendars("Standard").Period(DRangeStart, DRangeFinish).Working = False
End Sub
Exercise2: (CalTaskMove)
Write a macro that prompts the user for an amount of time ("3d") and then creates a new task that starts that many days after the previous task.
Sub CalTaskMove()
Dim newTime As String
newTime = InputBox("Enter the a duration")
x = ActiveProject.Tasks(ActiveCell.Task.ID - 1).Start
ActiveCell.Task.Start = DateAdd(x, newTime)
End Sub
Selection Objects
Even though Microsoft Project does not supply objects for all of the user interface elements in the product, selection of information is provided so that macro writers can manipulate information based on the users selection.
Theory
Microsoft Project supports two variations of the active selection: a Cell object accessible from the ActiveCell property of the Application, and a Selection object available through the ActiveSelection property of the Application.
Since Microsoft Project can be thought of more as a database with records and fields than a spreadsheet with rows and columns, both the Cell and Selection object provide information about the selected records (tasks or resources) and the selected fields.
Cell Object
The Cell object allows access to the active record and field in the selection. This is sometimes a subset of the selection, if the selection contains more than one cell.
To determine the record, the Cell object contains a Task and a Resource object. This allows the macro writer to access all of the fields of the selected record. For example:
MsgBox "You have task " & ActiveCell.Task.Name & " selected."
will display the name of the task regardless of the field selected.
There are occasions where a task will not be selected, for instance in a resource view. In this case, ActiveCell.Task will return Nothing. So a more robust version of this example would be:
If Not (ActiveCell.Task Is Nothing) Then
MsgBox "You have task " & ActiveCell.Task.Name & " selected."
End If
To determine the field selected, the Cell object provides two properties. The FieldName property contains the name of the field as a string. So we could do:
If Not (ActiveCell.Task is Nothing) Then
strTemp = "You have the "
strTemp = strTemp & ActiveCell.FieldName
strTemp = strTemp & " field of "
strTemp = strTemp & ActiveCell.Task.Name
strTemp = strTemp & " selected."
MsgBox strTemp
End If
The FieldID property returns the ID number of the field which can be used with either the GetField or the SetField methods discussed earlier.
If Not (ActiveCell.Task is Nothing) Then
strTemp = InputBox("Enter value to be placed in the Active Cell")
ActiveCell.Task.SetField(FieldID:=ActiveCell.FieldID, Value:=strTemp)
End If
Selection Object
The Selection object is an extension of the Cell object. Instead of the Task and Resource objects contained in the Cell object, the Selection object contains Tasks and Resources collections. Similarly, in place of the FieldName and FieldID properties are FieldNameList and FieldIDList List collections.
With the Selection object one could implement a version of the Fill Down command found in Microsoft Project and Excel.
If Not (ActiveCell.Task is Nothing) Then
strTemp = InputBox("Enter value to be placed in all the cells in the same column as the active cell")
For Each t in ActiveSelection.Tasks
t.SetField(FieldID:=ActiveCell.FieldID, Value:=strTemp)
Next t
End If
Exercise
Write a macro that will increment a counter in a cell. For example, if you type a 1 in a cell and select it plus cells below and then run the macro, you would end up with these cells being filled with 1, 2, 3, 4, 5. See if you can also get it to work with text. Ex. Phase1, Phase2, Phase3, etc.
Window & Pane Objects
The Window and Pane objects offer a limited set of properties, but are useful when you want to manipulate some of the visual properties of your project windows.
A Window object provides a Close method, allowing you to close a window. This is useful when you have multiple windows open for the same project. This example closes the active window:
ActiveWindow.Close
To change the height of a window:
ActiveWindow.Height = 400
To change the state of the current window, use the WindowState property. There are three states to choose from, pjMaximized, pjMinimized, and pjNormal. To maximize the window use:
ActiveWindow.WindowState = pjMaximized
Project also allows control over each individual pane within the active window through the pane object. To determine which pane is active in a window:
ActiveWindow.ActivePane
This returns 1 for the top pane, and 2 for the bottom pane.
You can manipulate the panes in a window as objects directly:
ActiveWindow.TopPane.Activate
This activates the top pane. You could also use the Application object property PaneNext which Activates the lower pane if the upper pane is active or activates the upper pane if the lower pane is active.
ActiveWindow.BottomPane.Close
This closes the bottom pane.
Another way to close the bottom pane is to use the Application objects PaneClose method. This is the same as choosing remove split from the window menu. If you want to split the screen, then use PaneCreate.