Accessing custom attributes from macro / formula / VBA

[Guest]

A while back I asked "Is there a way to set a skill-level multiplier
for resources such that Resource A is 15% faster than resource B for a
given task?"

I got the answer "This isn't a built-in functionality in the tool, but
you can create it for yourself by assigning a custom attribute for
resources to hold their multiplier factor and use a macro to adjust
either your work or duration estimates based on the filled value."

I'm having trouble using either a formula, a macro or the VBA editor to
make this happen. Any ideas how I can access a custom attribute
assigned to a resource from another column in that task, in order to
perform the above calculation?

Cheers and thanks very much.

 

[JackD]

First you need to determine what "faster" means. It seems that it means that
less work is required by a skilled resource than for an unskilled one. Thus
you would probably set the tasks to fixed units and vary the work. In that
case a change in work is proportional to a change in duration. It would go
something like this:

First, set the skill factors.
1) Use one of the customfields for resources. Say Number1
2) Put an appropriate value in the Number1 field for each resource.

Second, store the default work values
1) use one of the assignment custom fields to hold the default work value.
You need to do this so that you can return to it if you want to reverse any
changes you have made.
Basically you would use a for each loop like this

for each assignment in assignments
assignment.number1 = assignment.work
next assignment

Third, Go through each task and adjust the work for each assignment based on
the resource skill multiplier

Sub adjust()
Dim t As Task
Dim ts As Tasks
Dim a As Assignment
Dim r As Resource
Set ts = ActiveProject.Tasks
For Each t In ts
If Not t Is Nothing Then
For Each r In t.Resources
For Each a In r.Assignments
a.Work = a.Work / r.Number1
Next a
Next r
End If
Next t
End Sub

That is basically it. You will need to add some more code to cover corner
cases like when there are no resources assigned to a task or to reset the
work values to default (allowing you to toggle the skill multiplier)