none
Using VBA to Update Project Cost Fields RRS feed

  • Question

  • I am trying to use VBA in MS Project 2010 to calculate custom costs using a very simple piece of code I found online (http://www.techrepublic(dot)com/article/microsoft-project-lets-you-track-pay-and-billing-rates-for-tasks-and-assignments/#). The code works great and it updates the Cost 1/2/3 fields on the Task Usage view perfectly. However, I need to also view costs by resource and the Cost 1/2/3 fields on the Resource Usage view are not updated. I had expected that the cost fields were the same throughout the application but apparently not. I have tried to determine how to reference the Resource Usage cost fields so I can update the code but I can't quite figure it out.

    Here is part of the code that calculates the cost

    For Each tskT In ActiveProject.Tasks
    If Not (tskT Is Nothing) Then
      For Each asnA In tskT.Assignments
    
        asnA.Cost1 = (asnA.Work / 60) * Left(ActiveProject.Resources _
           (asnA.ResourceID).CostRateTables(2).PayRates(1).StandardRate, _
           (Len(ActiveProject.Resources(asnA.ResourceID) _
           .CostRateTables(2).PayRates(1).StandardRate) - 2))
    
       curCost = curCost + asnA.Cost1
    Next asnA
    tskT.Cost1 = curCost

    This correctly updates the task usage cost field so now how do I get it to also update the resource usage cost field?

    Wednesday, June 22, 2016 8:26 PM

Answers

  • Brian_aw,

    Extra fields are unique to tasks, resources and assignments and starting with Project 2007 there are separate task assignment and resource assignment fields so you'll need to do some translation. The following Wiki article should get you pointed in the right direction.

    http://social.technet.microsoft.com/wiki/contents/articles/32051.ms-project-extra-fields-in-views.aspx

    If you need further help, let us know.

    John

    • Marked as answer by Brian_aw Thursday, June 23, 2016 1:16 PM
    Wednesday, June 22, 2016 10:02 PM

All replies

  • Brian_aw,

    Extra fields are unique to tasks, resources and assignments and starting with Project 2007 there are separate task assignment and resource assignment fields so you'll need to do some translation. The following Wiki article should get you pointed in the right direction.

    http://social.technet.microsoft.com/wiki/contents/articles/32051.ms-project-extra-fields-in-views.aspx

    If you need further help, let us know.

    John

    • Marked as answer by Brian_aw Thursday, June 23, 2016 1:16 PM
    Wednesday, June 22, 2016 10:02 PM
  • Thanks John!  That's exactly what I needed.
    Thursday, June 23, 2016 1:16 PM
  • Brian,

    You're welcome and thanks for the feedback.

    John

    Thursday, June 23, 2016 5:22 PM