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:
- Step through each task in the project schedule and evaluate task data based on the Project Status date.
- Count all the completed tasks.
- For incomplete tasks, determine if the tasks are in the future or late based.
- For late tasks, increment late start and late finish counts.
- Format the data.
- Display the message box.
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.
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
If t.PercentComplete = 100 Then
completeCt = completeCt + 1
'count future tasks
If t.BaselineFinish > ActiveProject.StatusDate And t.BaselineStart > ActiveProject.StatusDate Then
futureCt = futureCt + 1
'Late Finish Incomplete Tasks
If t.BaselineFinish <= ActiveProject.StatusDate Then
lateFinishCt = lateFinishCt + 1
If t.BaselineStart <= ActiveProject.StatusDate Then
'Late Start Incomplete Tasks
lateStartCt = lateStartCt + 1
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
End SubStep 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.
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.