Project Management

Make the most of Microsoft Project with these tips

If your project resources are geographically diverse, you can use these two Microsoft Project macros to automatically assign resources to tasks based on location and to change the default duration for new tasks.

Contributor Brian Kennemer often writes about Microsoft Project for TechRepublic’s IT communities. Here are two tips to help you make the most of Microsoft Project.

Using VBA to work with Tasks and Resources
Here is a macro that illustrates how to work with Microsoft Project’s Tasks and Resources object collections to perform a useful operation. This macro automatically assigns resources to tasks based on the contents of a text field.

It will loop through every task and look at the Text5 field. Then it will loop through every resource in the project, and if the Text5 field for a resource matches the Text5 field for the task, the resource will be assigned to the task.

For example, if your tasks and resources are spread across the country, you can use it to automatically assign all of the resources from the right location to your tasks. One way this would work would be if the location of the task was put in the task’s Text5 field and the location of the resource was put in the resource’s Text5 field.

You could then go through the assigned resources and remove those that you do not want to use. It would save you from having to look for the resources.

Most of all, this macro is a good illustration of how to work with loops and how to compare values from different objects. See how it works for you.
<Start Code>
Sub AutoAssign()
Dim tskT As Task
Dim rcsR As Resource
Dim asnA As Assignment
Dim lngUnits As Long
Dim blnFound As Boolean
lngUnits = 1
For Each tskT In ActiveProject.Tasks
    If Not (tskT Is Nothing) Then
        For Each rcsR In ActiveProject.Resources
            If Not (rcsR Is Nothing) Then
                If (rcsR.Text5 = tskT.Text5) And _
                (rcsR.Text10 = tskT.Text10) Then
                    For Each asnA In tskT.Assignments
                        If asnA.ResourceID = rcsR.ID Then
                            blnFound = True
                        End If
                    Next asnA
                    If blnFound = False Then
                        tskT.Assignments.Add TaskID:=tskT.ID, _
                        ResourceID:=rcsR.ID, Units:=lngUnits
                        blnFound = False
                   End If
                End If
            End If
        Next rcsR
    End If
Next tskT
End Sub
<End Code>

Creating new tasks with Zero duration (or not)
Here is a macro that will allow you to create a new task for the duration you choose. You can just assign this macro to a toolbar button and use it to create your tasks.

This can come in handy when, for example, you have tasks that may be five days in duration and you want your default to be longer than one day. In this instance, it would be a good idea to have a way of changing the default duration of new tasks.

Here is the code for the macro:
<Start Code>
Sub NewTask()
Dim tskNew As Task
Dim lngDuration As Long
On Error GoTo ErrorHandle
lngDuration = 960
If Application.ActiveSelection.Tasks(ActiveSelection.Tasks.Count) _
< ActiveProject.Tasks.Count Then
    Set tskNew = ActiveProject.Tasks.Add _
    (Before:=(ActiveSelection.Tasks _
    (ActiveSelection.Tasks.Count).ID + 1))
    tskNew.Duration = lngDuration
    Set tskNew = ActiveProject.Tasks.Add
    tskNew.Duration = lngDuration
End If
Exit Sub
    If Err.Number = 424 Then
        Set tskNew = ActiveProject.Tasks.Add
        tskNew.Duration = lngDuration
        MsgBox "Error: " & Err.Number & " " & Err.Description
    End If
End Sub
<End Code>

To change the duration, just edit the line that starts: “lngDuration = “

Change the number in this line to equal the duration of the tasks that will be created by this macro. Remember that the duration for a one-day task is 480.

Share your advice with us
Are there suggestions you have for getting the most from Microsoft Project that have worked well for you or your clients? Tell us about them in your comments below.


Editor's Picks

Free Newsletters, In your Inbox