Project Management optimize

Get project schedule metrics by using VBA

In this Microsoft Project 2010 and VBA tutorial, Andrew Makar details how to use Visual Basic to develop a quick metrics snapshot of your project schedule.

Mature project management organizations, experienced project managers, and PMOs know the value of managing projects by metrics. Metric collection usually involves completing a template based on project data that includes schedule data.

Microsoft Project 2010 contains a lot of raw schedule data that can be used for meaningful project metrics. The challenge with this approach is gathering project data often requires exporting the task data into Excel, developing pivot tables, and writing a few formulas. In this Microsoft Project and VBA tutorial, I'll show you how to use Visual Basic to develop a quick metrics snapshot of your project schedule (Figure A). Figure A

Project statistics (Click the image to enlarge.)

Quick project metrics

When assessing project status, there are several key schedule metrics to consider, including total task count, completed task count, incomplete tasks with late starts, incomplete tasks with late finishes, and remaining tasks to be completed. All of these metrics help quantify project progress rather than just subjectively explain project status. If your organization hasn't adopted earned value management as a project management technique, then task counts are an easy first step to metric driven management.

Microsoft Project has a built-in statistics feature accessible by selecting Project | Project Information | Statistics. The delivered statistics feature provides high-level metrics, although I prefer to know more detailed task counts. After completing this tutorial, you'll be able to modify and enhance the code to include additional metrics in your own custom metrics reporting.

The process includes the following steps:

  1. Step through each task in the project schedule and evaluate task data based on the Project Status date.
  2. Count all the completed tasks.
  3. For incomplete tasks, determine if the tasks are in the future or late based.
  4. For late tasks, increment late start and late finish counts.
  5. Format the data.
  6. Display the message box.
Step 1: Add the menu item to the Navigation bar

Please refer to my previous tutorial on how to add a new item to the custom navigation bar. This is the code snippet to add the displayProjectMetrics subroutine to the AddCustomNavigation subroutine.

ribbonXml = ribbonXml + "          <mso:button id=""prjMetrics"" label=""Project Metrics"" "
ribbonXml = ribbonXml + "imageMso=""DiagramTargetInsertClassic"" onAction=""displayProjectMetrics""/>"
Step 2: Build the VBA subroutine

In the Visual Basic editor, create a new subroutine called displayProjectMetrics and paste the following code.

Sub displayProjectMetrics()
Dim tsks As Tasks
Dim t As Task
Dim lateFinishCt, lateStartCt, futureCt, completeCt As Integer
Dim myStats As Variant
Dim statString As String
Set tsks = ActiveProject.Tasks
lateFinishCt = 0
lateStartCt = 0
futureCt = 0
completeCt = 0
For Each t In tsks
If (Not t Is Nothing) And (Not t.Summary) Then
'Completed Tasks
If t.PercentComplete = 100 Then
completeCt = completeCt + 1
Else
'count future tasks
If t.BaselineFinish > ActiveProject.StatusDate And t.BaselineStart > ActiveProject.StatusDate Then
futureCt = futureCt + 1
Else
'Late Finish Incomplete Tasks
If t.BaselineFinish <= ActiveProject.StatusDate Then
lateFinishCt = lateFinishCt + 1
End If
If t.BaselineStart <= ActiveProject.StatusDate Then
'Late Start Incomplete Tasks
lateStartCt = lateStartCt + 1
End If
End If
End If
End If
Next t
statString = "As of Project Status Date: " & ActiveProject.StatusDate & " the project task count is: " & vbCrLf & vbCrLf
statString = statString & "Total Tasks: " & vbTab & vbTab & tsks.Count & vbCrLf
statString = statString & "Completed Tasks: " & vbTab & vbTab & completeCt & vbCrLf & vbCrLf
statString = statString & "Incomplete Task Metrics: " & vbCrLf
statString = statString & "Late Start Tasks: " & vbTab & vbTab & lateStartCt & vbCrLf
statString = statString & "Late Finish Tasks: " & vbTab & vbTab & lateFinishCt & vbCrLf
statString = statString & "Future Tasks: " & vbTab & vbTab & futureCt & vbCrLf
MsgBox statString
End Sub
Step 3: Save your file and test it Save the file, close Microsoft Project, and re-open it to test the project metrics feature. Click the Project Metrics button in the Utilities tab, and the statistics will display (Figure B). Figure B

Project metrics (Click the image to enlarge.)

Additional calculations can be made by changing the statString variable. In the code, I kept appending new calculations to the variable to include the appropriate project metrics. The MessageBox function displays the final calculation. I also used the vbTab (Tab) and vbCrLf (Carriage Return Line Feed) variables to format the string and include proper line breaks.

Conclusion

This is just one example of the type of data analysis that is possible with Visual Basic; an entire project dashboard could be created with VBA for Microsoft Project. For a quick project status assessment, I use this code subroutine that uses objective metrics in addition to the subjective project assessment.

I encourage you to build upon these Visual Basic examples and extend Microsoft Project with your own VBA powered ingenuity. Download the sample code for this tutorial.

Read more installments in my VBA series

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.

0 comments