Project Management optimize

Use VBA to highlight missing baseline finish dates in Microsoft Project 2010

In part two of his series on using VBA in Microsoft Project 2010, Andrew Makar describes how to build a custom task field highlighting feature.

In my previous Visual Basic Programming for Microsoft Project 2010 post, I provided a simple Hello World tutorial that introduced how to use VBA code to extend the Ribbon UI and create a simple message. Now I'll show how to identify all the tasks in your project schedule that are missing a project baseline using VBA.

In practice, new tasks are added to a project schedule as the schedule executes. The project manager identifies more granular tasks or adds new tasks based on modified scope. One step that is easily overlooked is the need to baseline the new tasks that were added to the project schedule. Usually I will insert the Baseline Finish column and filter on any tasks that have a Baseline Finish of NA. I prefer to view all the tasks with the missing baseline in the context of the entire project schedule (Figure A). Figure A

Project schedule with the missing baseline (Click the image to enlarge.)
When you have a large project schedule, it can be difficult to identify the tasks missing the baseline dates within the entire schedule. A useful technique is to highlight the specific Baseline Finish Date fields that have the missing Baseline Finish dates. Figure B highlights the tasks with missing baseline finish dates without having to filter out the other tasks. Figure B

Tasks with missing baseline finish dates are highlighted. (Click the image to enlarge.)

The algorithm for the solution is:

  1. Select the Gantt Chart view and activate the Entry table.
  2. For each task in the schedule, check the Baseline Finish date for the NA value.
  3. If the date is NA, toggle the background color to yellow.
  4. Select the 1st row in the schedule when the checking is complete.
  5. Incorporate error handling in case the Baseline Finish date hasn't been added to the Entry table.

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

Step 1: Create the menu item

Using the code base from my last Microsoft Project tutorial, I will add another menu button using this code:

myNavBar = myNavBar + "          <mso:button id=""missingBaseline"" label=""Missing Baseline"" "
myNavBar = myNavBar + "imageMso=""DiagramTargetInsertClassic"" onAction=""toggleMissingBaseline""/>"
Remember to access the Visual Basic editor by selecting the Developer tab in Microsoft Project 2010 and clicking the Visual Basic icon (Figure C). Figure C

Visual Basic icon

This snippet of code will add another button to the MyTools group of buttons on the Utility tab; we created this group of buttons and custom tab in my previous Microsoft Project VBA tutorial. The full subroutine that creates the ToggleMissingBaseline button and the Hello World button from the earlier tutorial is included at the end of this tutorial.

Step 2: Build the VBA subroutine

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

Public Sub toggleMissingBaseline()

On Error GoTo ErrorHandler

Dim tsks As Tasks

Dim t As Task

Dim rgbColor As Long
Set tsks = ActiveProject.Tasks
'Switch to Gantt Chart Entry View

ViewApplyEx Name:="&Gantt Chart", ApplyTo:=0

TableApply Name:="&Entry"
For Each t In tsks

If (Not t Is Nothing) And (Not t.Summary) Then

SelectTaskField Row:=t.ID, Column:="Baseline Finish", RowRelative:=False

rgbColor = ActiveCell.CellColorEx
If t.BaselineFinish = "NA" Then

' Check whether the manual 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

' The task is automatically scheduled, so change the background to white.

Font32Ex CellColor:=&HFFFFFF

End If

End If

Next t
'Selects the top row in the table
SelectRow Row:=0, RowRelative:=False
Exit Sub

ErrorHandler:

MsgBox "Error: Insert Baseline Finish Column and Toggle Again"

Exit Sub
End Sub

Step 3: Save your file and test it

Save your file, close Microsoft Project, and re-open it to test the new Toggle Missing Baseline button (Figure D). Figure D

Missing Baseline button

The Baseline Finish field will toggle between white and yellow background colors, and each time the cursor will select the top row in the project schedule.

Try to hide the Baseline Finish date and press the Missing Baseline button. Since the set of tasks assumes the Baseline Finish data is in the table, Microsoft Project will throw an error if it is not part of the view. Since the routine contains error handling, the appropriate message is displayed (Figure E). Figure E

Error handling

Additional notes

The Font32Ex CellColor:=&H66FFFF line of code references a hexadecimal RGB value. In Microsoft Project's implementation, the RGB values are reversed, with the R value stored in the last byte position. You can use a hexadecimal color tool like ColorHexa to pick different colors -- just remember to reverse the value when adding it to the VBA code.

Using this code base, you can now loop through all of your tasks and perform various checks based on key project criteria. There may be more elegant ways to accomplish this same highlighting task, such as creating a custom filter and applying it to the Highlight filter, but you wouldn't have the chance to explore other opportunities with Microsoft Project and Visual Basic. You can also find a lot of helpful programming examples using Visual Basic at the Microsoft Project Developer Curriculum site.

You can download the sample code for this tutorial here.

In my next post, I'll explain how to build upon the code base and identify late tasks in the schedule using a similar method.

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.

3 comments
Tommy Lovelace
Tommy Lovelace

Regarding: \' The task is automatically scheduled, so change the background to white. Font32Ex CellColor:=&HFFFFFF End If End If

amakar
amakar

That last End If closes the first IF statement at: If (Not t Is Nothing) And (Not t.Summary) Then Andy

Tommy Lovelace
Tommy Lovelace

Hi Andy, Yeah, I tried it. An error message came up and said I had too many End Ifs...so I deleted one and the subroutine worked just fine. Tommy