Using MS Project VB Macro for Obtaining Custom Field Value at Assignment Level

Question

In our Project Enterprise installation there is a task level custom field called Billing Rate, but in the plan it is populated at the assignment level, like the following:

Task Name         Billing Rate
==========        ============
Task 1              USD0.00
    Resource 1      USD100.00
    Resource 2      USD120.00
Task 2              USD0.00
    Resource 3      USD150.00

I would like to access the value of Billing Rate in VBA Macro. But since it is a custom field at task level, I am unable to access it in assignment object in Task.Assignments.

I tried

Assignment.Resource.GetField(FieldNameToFieldConstant("Billing Rate"))

But getting error:

<The argument is not valid.>

How do I access this field at the assignment level?

On a related note, the field can be accessed in PowerBI under Assignment table at "BillingRate_T", but unable to access in Project VBA Macro. Thank you in advance!

Looking at this thread (scroll to the very bottom...) it seems a solution would be to figure out which Enterprise field is used for "Billing Rate" and query that directly. For example: Assignment.EnterpriseNumber1 — Rachel Hettinger
Thank you, but the those enterprise fields (e.g. EnterpriseCost1 - EnterpriseCost10) actually show up in watch window, and those are not custom fields. — SolarBear

Stephen Richards Stephen Richards · Accepted Answer · 2020-08-30T05:39:44

If the Billing Rate figures are already input as the standard rate for each resource on the resource sheet, you could use the following on a task-by-task basis:

Sub testBR()
Dim t As Task
Dim res As Resource
Dim asg As Assignment
Dim aN, br As String
Set t = ActiveSelection.Tasks(1)
For Each asg In t.Assignments
aN = asg.ResourceName
For Each res In ActiveProject.Resources
    If res.Name = aN Then
    br = res.StandardRate
    'do something with the 'br' variable before it is overwritten
    Debug.Print br
    End If
Next res
Next asg
End Sub

Another method using foreground processing, going task-by-task could be:

Sub testBR1()
'start on the task name
Dim aBR As String
aBR = SelectTaskField(1, "Cost1 (Billing Rate)", True) 'change the reference column as needed
aBR = ActiveCell
'do something with the 'aBR' variable before it is overwritten
Debug.Print aBR
End Sub

Using MS Project VB Macro for Obtaining Custom Field Value at Assignment Level

1 Answers