VB code to add Work and Baseline work and store in custom fields

[lbethel]

I need to add both the Work and the Baseline Work starting at the project
start date and continuing to the Status Date, and to store the results in a
custom field. Does anyone know how to do this? I am familiar with all the
earned value fields, and I have reasons for not being able to use those to
determine the results

 

[Jack Dahlgren MVP]

There is no custom field which is timescaled and no formula which can do
this without the EV fields...
The earned value fields are designed for this.
What are the reasons you can not use them?

-Jack Dahlgren

 

[lbethel]

Thanks Jack, I realize that there's no custom field that is timescaled, and
no formula to do this calculation. I suspect that the VB code wouldn't be too
complicated to do this, but I'm not sure. I was hoping someone had done it
before. I could use the BCWS and ACWP, and divide by cost rate to get back to
the relevant hours, but its a long story about the issues with this.

 

[Rob Schneider]

There is a whole chapter in "VBA Programming for Microsoft Office
Project" that covers timehased data" (Module 19) which probably is a
good starter.

 

[Big John]

This is code I use to calculate a SI, eg SPI based on hours only

Sub UpdateStatusIndex()
On Error Resume Next

Dim tsv As TimeScaleValue
Dim tsvs As TimeScaleValues
Dim tsvc As TimeScaleValues
Dim TotWork As Single
TotWork = 0
' Update Project Summary Task
'ActiveProject.Tasks(65).TimeScaleData(StartDate:=ActiveProject.ProjectStart, EndDate:=ActiveProject.StatusDate,type:=

Set tsvs =
ActiveProject.ProjectSummaryTask.TimeScaleData(StartDate:=ActiveProject.ProjectStart,
EndDate:=ActiveProject.StatusDate, Type:=pjTaskTimescaledBaselineWork,
TimeScaleUnit:=pjTimescaleDays, Count:=1)
For Each tsv In tsvs
TotWork = TotWork + Val(tsv.Value) / 60
'Debug.Print tsv.StartDate, Val(tsv.Value) / 60
Next tsv
'Debug.Print TotWork, ActiveProject.ProjectSummaryTask.Name
If TotWork > 0 Then
'ActiveProject.ProjectSummaryTask.Number8 =
ActiveProject.ProjectSummaryTask.ActualWork / 60 / TotWork
' ActiveProject.ProjectSummaryTask.SetField
FieldNameToFieldConstant("WTD"), TotWork
ActiveProject.ProjectSummaryTask.SetField FieldNameToFieldConstant("ATD"),
ActiveProject.ProjectSummaryTask.ActualWork / 60
ActiveProject.ProjectSummaryTask.SetField FieldNameToFieldConstant("WTD"),
TotWork
ActiveProject.ProjectSummaryTask.SetField FieldNameToFieldConstant("SI"),
ActiveProject.ProjectSummaryTask.GetField(FieldNameToFieldConstant("ATD")) /
TotWork

End If
TotWork = 0

' Update all tasks

Set objSelection = ActiveProject.Tasks
For Each t In objSelection
Set tsvs = t.TimeScaleData(StartDate:=ActiveProject.ProjectStart,
EndDate:=ActiveProject.StatusDate, Type:=pjTaskTimescaledBaselineWork,
TimeScaleUnit:=pjTimescaleDays, Count:=1)
For Each tsv In tsvs
TotWork = TotWork + Val(tsv.Value) / 60
'Debug.Print tsv.StartDate, Val(tsv.Value) / 60
Next tsv
'Debug.Print TotWork, t.Name, t.ActualStart
If TotWork > 0 Then
't.Number8 = t.ActualWork / 60
t.SetField FieldNameToFieldConstant("ATD"), t.ActualWork / 60
t.SetField FieldNameToFieldConstant("WTD"), TotWork
t.SetField FieldNameToFieldConstant("SI"),
t.GetField(FieldNameToFieldConstant("ATD")) / TotWork

ElseIf (t.BaselineStart < ActiveProject.StatusDate) Then
If (IsDate(t.ActualStart)) Then
t.SetField FieldNameToFieldConstant("SI"), 0
Else
t.SetField FieldNameToFieldConstant("SI"), 10.01
End If
End If
TotWork = 0
Next t
'Debug.Print "done"

End Sub