Project Management

Use VBA to find late finish dates in Microsoft Project 2010

Reduce your project administration burden by using VBA code to identify late tasks within the context of your overall Microsoft Project 2010 schedule.
In my previous Microsoft Project 2010 Visual Basic programming tutorial, I demonstrated how to identify missing baseline dates from the project schedule. (Part one of this series covered how to use VBA code to extend the Ribbon UI and create a simple message.) Identifying late tasks in the project schedule is a common task that often requires filtering on baseline dates and sorting percent complete values. I prefer to gain insight into my project with a single click of a button versus setting more filters and views. In this tutorial, I show how to identify all the late tasks in your project schedule with the click of a button (Figure A). Figure A

Project schedule with late tasks (Click the image to enlarge.)

The logic includes the following steps:

1. Select the Gantt Chart view and activate the Entry table.

2. Confirm the Project Status date Microsoft Project has been set.

3. For each task in the schedule, check the Baseline Finish date to the Project Status date.

4. If the Baseline Finish date is less than the Project Status date and the Task % Complete is not 100%, then toggle the background color to yellow.

5. Select the 1st row in the schedule when the checking is complete.

To build this custom task field highlighting feature, create the VBA routine as follows in these step-by-step instructions.

Step 1: Add the menu item to the Navigation bar

Using the Visual Basic editor in the Developer tab, I'll add the Toggle Late Tasks button to the Utilities group that I created in the previous tutorial:

myNavBar = myNavBar + "          <mso:button id=""toggleLateFinish"" label=""Toggle Late Finish"" "
myNavBar = myNavBar + "imageMso=""DiagramTargetInsertClassic"" onAction=""ToggleLateFinish""/>"
This snippet of code will add another button to the MyTools group of buttons on the Utility tab (Figure B). Figure B
VBA editor (Click the image to enlarge.)

Step 2: Build the VBA subroutine

In the Visual Basic editor, create a new subroutine called toggleLateFinish and paste the following code:

Sub ToggleLateFinish()
'Check Project Status Date is set
If ActiveProject.StatusDate = "" Then
MsgBox "Please set the Project Status Date to toggle late tasks"
Exit Sub
End If
Dim tsks As Tasks
Dim t As Task
Dim rgbColor As Long
Dim missingBaselineCt
Set tsks = ActiveProject.Tasks
'Switch to Gantt Chart Entry View
ViewApplyEx Name:="&Gantt Chart", ApplyTo:=0
TableApply Name:="&Entry"
missingBaselineCt = 0
For Each t In tsks
If (Not t Is Nothing) And (Not t.Summary) Then
SelectTaskField Row:=t.ID, Column:="Name", RowRelative:=False
rgbColor = ActiveCell.CellColorEx
'Check for missing baseline and increment counter
If t.BaselineFinish = "NA" Then
missingBaselineCt = missingBaselineCt + 1
Else
If t.BaselineFinish < ActiveProject.StatusDate And t.PercentComplete <> 100 Then
' Check whether the task color is white.
If rgbColor = &HFFFFFF Then
Font32Ex CellColor:=&H66FFFF      ' Change the background to yellow.
Else
Font32Ex CellColor:=&HFFFFFF  ' Change the background to white.
End If
Else
'change the background to white.
Font32Ex CellColor:=&HFFFFFF
End If
End If
End If
Next t
'Selects the top row in the table
SelectRow Row:=0, RowRelative:=False
If missingBaselineCt > 0 Then
MsgBox "There are " & missingBaselineCt & " tasks missing a baseline finish date.  Set a baseline date for these tasks for accurate metrics"
End If
End Sub

Step 3: Save your file and test it

Save the file, close Microsoft Project, and re-open it to test the new Toggle Late Tasks feature (Figure C). Figure C

Toggle Late Tasks button
The task name field will toggle between white and yellow background color. You can filter by the Resource Names column (Figure D) to identify all the late tasks, while viewing all the tasks assigned to the filtered resource. Figure D

Filter by Resource and Late Finish (Click the image to enlarge.)

Benefits

In large project schedules with filtered tasks, the project team can lose the context of the task within the overall task hierarchy. The benefit of the approach outlined in this tutorial is that you and your project team can see the late tasks within the context of the overall schedule. Download the sample code for this tutorial, and feel free to experiment with the code to change the color or highlight a different field.

In the final installment in this series, I'll show how to generate project metrics with Visual Basic programming.

About

Dr. Andrew Makar is an IT program manager and is the author of How To Use Microsoft Project and Project Management Interview Questions Made Easy. For more project management advice visit http://www.tacticalprojectmanagement.com.

2 comments
alp0091
alp0091

This is excellent. How could a script account for late starts. The query would need focus on the Actual Start missing prior to the schedule date but I don't know what that would look like in VBA.

amakar
amakar

I would define a late start where the ActualStart is > Baseline Start, so you would adjust the IF statement: If t.ActualStart > t.BaselineStart Then (Treat that line as pseudo code as I'm typing it..vs building it into the script) If you didn't want to use VBA, you could also do a custom highlight filter Andy

Editor's Picks