How to use VBA in Microsoft project to time scale a custom cost field?-CodePudding

I am trying to time scale the custom cost fields "labor" and "material" so I can view how these data are distributed over a given time horizon.

Sub MSCostOutlay()

    'This macro will copy timescaled variance data into the Baseline9Cost field.
    
    Dim TSVBaselineCost As TimeScaleValue 'Capture the dataset for the Baseline10Cost
    Dim TSVSBaselineCost As TimeScaleValues
    Dim t As Task
    
'    ActiveProject.StatusDate = InputBox("Enter the Status Date.", "Status Date", ActiveProject.StatusDate)
    
  

    For Each t In ActiveProject.Tasks
                Set TSVSBaselineCost = t.TimeScaleData((ActiveProject.StatusDate), ActiveProject.StatusDate, pjTaskTimescaledBaseline9Cost, pjTimescaleMonths, 1)
                For Each TSVBaselineCost In TSVSBaselineCost
                    TSVBaselineCost = t.Cost4
                Next TSVBaselineCost
                t.Baseline9Cost = t.Baseline9Cost   1
    Next t


End Sub

Above is the code I tried to use to store the labor cost in a time scaled array. I tried testing this script on one day worth of data to cut down on processing time. Regardless, I kept getting a run-time error of 1101 with no success.

CodePudding user response：

This code will timephase the Baseline 9 Cost data which can then be seen in the Task Usage field:

Here's the code:

Sub MSCostOutlay()

    Dim tsv As TimeScaleValue
    Dim tsvs As TimeScaleValues
    Dim t As Task

    For Each t In ActiveProject.Tasks
        t.Baseline9Duration = Application.DateDifference(t.Baseline9Start, t.Baseline9Finish)
        Set tsvs = t.TimeScaleData((t.Baseline9Start), t.Baseline9Finish, pjTaskTimescaledBaseline9Cost, pjTimescaleDays, 1)
        For Each tsv In tsvs
            If Application.DateDifference(tsv.StartDate, tsv.EndDate) > 0 Then
                tsv = t.Baseline9Cost / (t.Baseline9Duration / (60 * 8))
            End If
        Next tsv
    Next t

End Sub

Notes:

Code requires Baseline 9 Start and Finish dates to be populated; Baseline Duration is calculated by the code.
Instead of Baseline fields, Cost fields (such as Cost9) could be used. In this case, using Start/Finish would be appropriate.
When entering timescale data, use days rather than months and skip non-working days.
Remember that duration is stored in minutes so 1 day = 480 minutes (60 * 8)

CodePudding user response：

Rachel,

I keep getting a runtime error 1101 at the step in the script noted below. I am trying to store a custom cost field in baseline before time scaling it. Please let me know your thoughts!