Lesson Objectives
Upon completion of this lesson, the participant will be able to:
Some Topics to be introduced in this lesson include:
Task Object
Of course, the main objective of the Project object is to hold the scheduling information for the project, namely tasks and resources.
Referencing a Task
As with Project objects, tasks can be referenced in several different ways. A task object can be referenced via the Tasks collection either by ID, Unique ID, or Name.
Tasks(1)
Tasks.UniqueID(4)
Tasks("Juggle the cat")
There are advantages and disadvantages to each of these methods. Referencing by ID (or row number) is the most straightforward, since these numbers are displayed to the user during the normal operation of Microsoft Project. Since inserting and deleting tasks can cause the ID of a task to change, referencing with the ID will not be reliable if the macro must always refer to the same task.
Referencing by UniqueID allows you to always refer to the same task, but is conceptually more difficult because this information is not normally viewed.
Lastly, referencing by name makes for very readable code since you are able to refer to the task by name. The drawback is that Microsoft Project supports multiple tasks with the same name, so referencing by name would only allow you to refer to the first task with a particular name.
Task Information
There are basically three different kinds of information contained in a task - Fields, Schedule Logic, and Structure. While there is some overlap between these types, it serves as a model for organizing the information contained in a task.
Fields (Properties)
Almost all of the information in a task can be found in one of the fields Microsoft Project supports and so access to fields is an important part of Visual Basic for Applications in Microsoft Project. Microsoft Project supports many different task fields. These fields are simply accessed as a property of the task object which corresponds to the field name.
For example, to change the duration of a task, you would do something like:
ActiveProject.Tasks(3).Duration = "3d"
The Duration field (or the Duration property of a Task) is a good example of the use of a field in Visual Basic for Applications. As shown in the example, the Duration property accepts a string. It will also accept a number which is interpreted as the number of minutes in the duration:
ActiveProject.Tasks(3).Duration = 1440 '3d * 8h/d * 60m/h
In the normal use of the product, there are some cases where the duration of a task is calculated and therefore cannot be modified. The most common occurrence is when the task is a summary task. Summary tasks summarize the information of the subtasks indented below them in the outline. Since Microsoft Project also supports a Summary field, it is easy to determine whether or not the a task is capable of having its duration changed:
If Not ActiveProject.Tasks(3).Summary Then
ActiveProject.Tasks(3).Duration = "3d"
End If
Additionally, there are many fields which one normally associates with a task, such as Duration and BaselineStart, which are also applicable to the project itself. For this reason, Microsoft Project supports all the task fields in the Project object as well. As with a summary task, most of these fields are calculated and cannot be changed.
MsgBox ActiveProject.Duration
This example would show the length of the project in a dialog box.
Another interesting example of task information is the resource assignments. Microsoft Project supports several task fields which can display the resources assigned to a task –Resource Names displays the names of the resources working on a task, Resource Initials displays the initials of the resources, and Resource Group displays the groups containing the resources.
As with any other field, these are all exposed as properties of a task.
MsgBox ActiveProject.Tasks(3).ResourceNames
This example would display the names of the resources working on task 3 in the same way as the Resource Names column in the Gantt Chart does.
Additionally, we have added objects which give users access to the same information:
MsgBox ActiveProject.Tasks(3).Resources(1).Name
would display the name of the first resource assigned to task 3.
Lastly, both the Task and the Resource objects contain an Assignment object which details the information about the assignment of a resource to a task. The details of the Assignment object are discussed later.
Schedule Logic
Another important body of information in a task is the schedule logic. In Microsoft Project, the logic is determined by creating predecessor and successor relationships. One method of examining the logic of a task is to refer to the fields which display this information as a string:
MsgBox ActiveProject.Tasks(3).Predecessors
This example would display something like "2FS+3d" to indicate that task 2 is a predecessor with a lag of 3 days.
As with resource assignments, Microsoft Project provides some of this information in an object form as well. PredecessorTasks and SuccessorTasks are collections of tasks which are the predecessors and successors of the task in question. So:
MsgBox ActiveProject.Tasks(3).PredecessorTasks(1).Duration
would display the duration of the first predecessor task of task 3.
Microsoft Project also supplies methods for manipulating task relationships. The LinkPredecessors and LinkSuccessors methods allow the macro writer to create either predecessor or successor relationships. Similarly, UnlinkPredecessors and UnlinkSuccessors allow the removal of relationships.
The following macro would unlink task 3 from all its successors:
Set x = ActiveProject.Tasks(3)
x.UnlinkSuccessors(x.SuccessorTasks)
Structure
The last kind of information contained in a task is the structure or organization of the tasks. In Microsoft Project, tasks are organized into an outline where summary tasks summarize the information of the subtasks indented below them. This is also known as a Work Breakdown Structure or WBS.
Again, this information can be derived from fields that are accessible in the product. OutlineLevel gives the outline level of a task with 1 being the top of the outline and 10 being the lowest outline level. OulineNumber gives the automatically generated WBS Code based on the outline. The second task under the third summary task would have an Outline Number of "3.2".
As with some of the other fields, Microsoft Project also provides this information in an object form. OutlineChildren is a collection of the subtasks of a given task. OutlineParent is the reverse - the summary task of a given task. With these two collections, it is possible to navigate the outline structure directly.
Sub Siblings()
Set objParent = ActiveCell.Task.OutlineParent
For Each objTemp In objParent.OutlineChildren
strTemp = objTemp.Name & ListSeparator & strTemp
Next objTemp
'Remove the last List Separator
strTemp = Left$(strTemp, Len(strTemp) - Len(ListSeparator))
MsgBox strTemp
End Sub
This example displays the names of all the tasks which have the same parent as the currently selected task.
To modify the outline, methods are exposed which move the task between outline levels. OutlineIndent moves a task down one level in the outline structure. OutlineOutdent does the reverse and moves the task up in the outline structure. Additional methods are available which change the appearance of the outline on screen. OutlineHideSubtasks will hide the subtasks of a summary task. OutlineShowSubtasks will show any hidden subtasks of a summary task. OutlineShowAllTasks will show all hidden subtasks of any summary task.
To link and unlink tasks together you can use the LinkPredecessors or LinkSuccessors methods. The following example prompts the user for the name of a task and then makes the task a predecessor of the selected tasks.
>Sub LinkTasksFromPredecessor()
>Dim Entry ' Task name entered by user
>Dim T ' Task object used in For Each loop
>Dim Exists ' Whether or not the resource exists
>' Prompt the user for the name of a task to turn into a predecessor.
>Entry = InputBox("Enter the name of a task:")
>Exists = False ' Assume task doesn't exist.
>' Search active project for the specified task.
>For Each T in ActiveProject.Tasks
>If T.Name = Entry Then
>Exists = True
>' Make the task a predecessor of the selected tasks.
>ActiveSelection.Tasks(1).LinkPredecessors Tasks := T
>End If
>Next T
>' If task doesn't exist, display error and quit Sub procedure.
>If Not Exists Then
>MsgBox("Task not found.")
>Exit Sub
>End If
>End Sub
Exercise1: (TaskTraverse)
Write a macro that traverses a listing of tasks. If the duration is greater than 5 days then remove 10% of the duration from that task. Also check to see if a summary task has more than 5 children. If so then collapses that outline.
>Sub TaskTraverse()
>For Each t In ActiveProject.Tasks
>If t.Duration > 5 * 480 Then t.Duration = t.Duration * 0.9
>If t.Summary Then
>If t.OutlineChildren > 5 Then t.OutlineHideSubtasks
>End If
>Next t
>End Sub
Exercise2: (TaskLink)
Write a macro that will automatically link only the first two tasks under a summary task.
>Sub TaskLink()
>For Each t In ActiveProject.Tasks
>i = 0
>If t.Summary Then
>If t.OutlineChildren > 1 Then
>idTmp1 = t.ID + 1
>idTmp2 = t.ID + 2
>ActiveProject.Tasks(idTmp2).LinkPredecessors Tasks:=ActiveProject.Tasks(idTmp1)
>End If
>End If
>Next t
>End Sub
Resource Object
The Resource object follows the same guidelines as the Task object. Access to all of the fields are available directly as properties of the object.
Referencing a Resource
As with the Task object, there are several ways of referencing a Resource object. A Resource object can be referenced via the Resources collection either by ID, Unique ID, or name.
Resources(3)
Resources.UniqueID(10)
Resources("Ken")
The same advantages and disadvantages of these methods that are seen in the Task object apply to the Resource object as well. The only difference is that you do not normally have multiple resources with the same name, so indexing by name is more reliable with Resource objects than it is with Task objects.
Resource Information Fields (Properties)
Just like with the Task object, the fields associated with a resource are accessible as properties of the Resource object:
ActiveProject.Resources("Susan").StandardRate = "$25.00/h"
ActiveProject.Resources(3).BaseCalendar = "Night Shift"
This is the easiest and most understandable way of accessing the information for a resource.
GetField and SetField Methods
Another set of methods is also available for both Task and Resource objects which allow you to access field information. These are the GetField and SetField methods.
GetField takes a field number and returns the contents of the field as a string. This is useful if you want to ensure that the value returned is a string. It is also useful if you want to programmatically access fields at run-time.
This example prompts for the name of a field and then displays the information contained in that field on a resource view:
Sub DisplayField()
strTemp = InputBox("Enter the name of the field you want to see")
strTemp = LCase(strTemp)
Select Case strTemp
Case "name"
intFieldID = pjResourceName
Case "initials"
intFieldID = pjResourceInitials
Case "standard rate"
intFieldID = pjResourceStandardRate
Case ""
End
Case Else
MsgBox "You entered a field I don’t understand"
End
End Select
MsgBox(ActiveCell.Resource.GetField(FieldID:=intFieldID))
End Sub
The SetField method is the reciprocal of GetField. It takes a field number and the string value to which you want to set the field.
ActiveCell.Resource.SetField FieldID:=pjResourceName,_
Value:="Jimbo"
Exercise1:
Write a macro that will prompt the user to enter the name of a resourse and then display the value for the Max units and ask them if they would like change the value. If yes then get the new value and make the change.
>Sub ResMaxUnits()
>RName = InputBox("Enter the name of the resource to edit")
>RMax = InputBox("The Max Units for " & RName & " is " & _ ActiveProject.Resources(RName).GetField(FieldID:=pjMaxUnits) & " Do you want to Change it? Y/N")
>If RMax = "y" Then
>ActiveProject.Resources(RName).MaxUnits = InputBox("Enter new value")
>End If
>End Sub
Assignment Object
The Assignment object holds information about how work on a task is being accomplished by a resource. The information includes the amount of work, the cost, and the start and finish dates of the work on the task. In Microsoft Project you can see this information through either the Task Form or Resource Form views.
Referencing an Assignment
Assignment objects are referenced by a Unique ID value associated with each assignment. Because these values are not necessarily sequential, it is probably easier to iterate over the Assignments collection with For Each...Next to find the particular assignment you want to work with.
Fields (Properties)
As with Task and Resource objects, Assignment objects provide access to the information contained in the assignment fields. This includes fields such as Actual Cost, Overtime Work, and Units.
This example provides a list of all the tasks a resource is working on in the resource’s Text1 field in much the same way that the Resource Names field of a task displays all the resources working on the task:
Sub TaskNamesField()
Dim Sep, strTemp As String
Dim r, a As Object
Sep = ListSeparator
For Each r In ActiveProject.Resources
strTemp = ""
For Each a In r.Assignments
strTemp = a.TaskName & Sep & strTemp
Next a
'Remove the last List Separator
strTemp = Left$(strTemp, Len(strTemp) - Len(Sep))
r.Text1 = strTemp
Next r
End Sub
Assignment Information Over Time
Because the work on a task occurs over a period of time, it is often useful to view the allocation of resources over time. While the Assignment object does not contain this information in a time-phased manner, the TimescaledData method of the Application object does allow you to access it programmatically.
The TimescaledData method takes several arguments to specify the task and resource combination you wish to examine, the date range, the type of periods (such as Week vs. Day), and the type of information (Work vs. Cost). It returns a string containing the values for each period separated by the List Separator. You can then parse the string to determine the values in each period.
This example displays a dialog box of the return timescaled data. It is the assignment of resource 1’s work broken down in hours across the entire project.
MsgBox TimescaledData(ActiveCell.Task.ID, 1, ActiveProject.ProjectStart, _
ActiveProject.ProjectFinish, pjWork, pjTimescaleHours)
Exercise1: (AssignWork1)
Write a macro that finds all assignments of an inputed resource and removes 1 hour of work from each assignment.
>Sub AssignWork1()
>RName = InputBox("Enter the name of the resourse to remove work from:")
>For Each t In ActiveProject.Tasks
>For Each r In t.Assignments
>If r.ResourceName = RName Then r.Work = r.Work - 60
>Next r
>Next t
>End Sub
Exercise2: (AssignWork2)
Write a macro that finds all assignments of all task with the name equal to the text1 field of the task and adds 20% more work than it already has.
>Sub AssignWork2()
>For Each t In ActiveProject.Tasks
>For Each r In t.Assignments
>If r.ResourceName = t.Text1 Then r.Work = r.Work + r.Work * 0.2
>Next r
>Next t
>End Sub