Forum Discussion
VIsual Basic and MS Project
Here ya go. This is the macro:
Sub StatusMetrics()
'macro written for Riele485 by John-Project 2/4/2022
Dim t As Task, PS As Task
Dim Cnt As Single, Comp As Single, Lat As Single, OT As Single, Fut As Single
For Each t In ActiveProject.Tasks
If Not t Is Nothing Then
If t.Summary = False Then Cnt = Cnt + 1
If t.Status = pjComplete Then Comp = Comp + 1
If t.Status = pjLate Then Lat = Lat + 1
If t.Status = pjOnSchedule Then OT = OT + 1
If t.Status = pjFutureTask Then Fut = Fut + 1
End If
Next t
Set PS = ActiveProject.ProjectSummaryTask
PS.Text1 = Cnt
PS.Text2 = Format(Comp / Cnt * 100, "##.0") & " %"
PS.Text3 = Format(Lat / Cnt * 100, "##.0") & " %"
PS.Text4 = Format(OT / Cnt * 100, "##.0") & " %"
PS.Text5 = Format(Fut / Cnt * 100, "##.0") & " %"
MsgBox "Total tasks: " & PS.Text1 & vbCr _
& "Completed: " & PS.Text2 & vbCr _
& "Late: " & PS.Text3 & vbCr _
& "On Time: " & PS.Text4 & vbCr _
& "Future: " & PS.Text5, Title:="Status Metrics"
End SubAnd this is the result on a sample file:
Pretty sweet huh?
John
Sorry but that's not much in the way of specifics.
1. Are you looking for a count of each type of status?
2. Where would you like to see the data (e.g. Project Summary Task)?
John
- Sorry but that's not much in the way of specifics.
1. Are you looking for a count of each type of status?
Tasks total, and total percentage of each status on tasks.
Yes. Task total and total percentage each status on task.
2. Where would you like to see the data (e.g. Project Summary Task)?
Can be!- By example:
Total task: 100
Completed: 30%
Late: 20%
On time: 40%
Future: 10%- Riele485,
Okay, that's something I can work with. Matter of fact, its nearly identical to something I did as added benefit to a much more complex macro I wrote for another user last year.
Stay tuned, I'll update this response with the macro a little later.
John