Uisng Assignments.Add and the effect on units.

[Mark]

I am getting a strange result using this method and don't know whay and need
some help if any of you out there can.

I am using a VBA program to automatically asign resources to a task. The
line of code is shown below:

Call
prjProjectToSearch.Tasks(intTaskId).Assignments.Add(prjProjectToSearch.Tasks(intTaskId).ID, prjProjectToSearch.Resources(lngTemp).UniqueID, intNoOfPeople)

I basically have a loop a round this statement so I can add multiple
resources. The weird thing I find is that if I add more than one resource all
with units of 100%, when the program has finished running and I check the
tasks......the units sometimes jump to massive percentages all by themselves.
And I don't know why.

Project just seems to reassign it by itself. I do not have levelling on so
don't know why it does it. I will put the full method below so you can see
what I am doing in full.

Private Sub AssignResource(ByVal prjProjectToSearch As Project, ByVal
strResourceName As String, ByVal intTaskId As Integer, ByVal
dblPercetageOfWork As Double)
Dim lngTemp As Long
Dim intNoOfPeople As Integer
Dim longPeriodDays As Double

'Is there any period or work
'lngPeriodDays = prjProjectToSearch.Tasks(intTaskId).Finish -
prjProjectToSearch.Tasks(intTaskId).Start

If (prjProjectToSearch.Tasks(intTaskId).Duration > 0 And
prjProjectToSearch.Tasks(intTaskId).Work > 0) Then
'Calculate number of people
intNoOfPeople =
CalculateWorkPercentage(prjProjectToSearch.Tasks(intTaskId).Duration,
prjProjectToSearch.Tasks(intTaskId).Work) * dblPercetageOfWork

'Try to find resource and add if found
For lngTemp = 1 To prjProjectToSearch.Resources.Count
If Not prjProjectToSearch.Resources(lngTemp) Is Nothing Then
If prjProjectToSearch.Resources(lngTemp).name =
strResourceName Then
Call
prjProjectToSearch.Tasks(intTaskId).Assignments.Add(prjProjectToSearch.Tasks(intTaskId).ID, prjProjectToSearch.Resources(lngTemp).UniqueID, intNoOfPeople)
Exit For
End If
End If
Next lngTemp
End If

End Sub

 

[Rod Gill]

depending upon the Show assignment units as a setting on the Schedule tab of
the Options dialog box units for 1 full time employee is 1 or 100 (100%) so
make sure your intNoOfPeople is correct.

If you are adding more than one resource to the task then if Effort driven
is on, then the duration will keep getting smaller.

Try adding your resources manually using your data and see what happens. The
result with vba should be the same.

--

Rod Gill
Project MVP

NEW!! Project VBA Book, for details visit: http://www.projectvbabook.com

 

[Mark]

Thanks for the reply. And I would expect everything you said to be true. But
its not working out that way.

I am putting 1 in. So I would expect to see it assign 1 person at one
hundred percent.

Or if I put three people in using 1 I'd expect the duration to go down by
third.

But this doesn't happen. It overwrites the percentages often with things
like 21,000%. The maximum specified in the resource sheet isn't even that.

I have tried changing the task to fixed work before assigning to see if that
helps. It doesn't. The value for the work even changes. Which if I have
specified fixed work should never happen.

I am very confused am wondering if I have come across some sort of bug.

And yes I agree that if I put them in manually it behaves entirely as
expected. Which is what makes this so strange.

Any other thoughts greatly recieved.

Mark

 

[Mark]

Dear Rod,

I have noticed that this error only occurs when applying multiple resources.
And then I found this on the net.....don't think it could be conected do you:

I used Microsoft Project (MSP) to Calculate / Level and it shows task end
dates that are very different from the Rational Portfolio Manager end dates
(the MSP end dates are pushed way out in time - many years in the future).
Why did this happen?
This is a known MSP issue. When a project contains fractional assignments
for more than one task for different resources (an example of a fractional
assignment contour is "0.2 hours"), and you export to calculate/level in MSP,
MSP can not resolve the fractional contours. As a result, the default
behavior for MSP is to assign that contour to the system limit, typically 12
years in the future.
The work around is to avoid using fractional assignments such as 0.2 hours
or 0.2 days.

Mark

 

[Rod Gill]

If you are assigning resources realistically and investing time in your
schedule, then the last thing I would do is use any Project software for
levelling resources!! If you could enter in all the data in your head
relating to Tasks, Resources, capabilities, likes, dis-likes, other work
loads, weaknesses and all the different possible solutions, you would find
it would be much quicker to level manually!!

--

Rod Gill
Project MVP

NEW!! Project VBA Book, for details visit: http://www.projectvbabook.com

 

[Mark]

The problem I have is that I am dealing with 20 projects over the course of
year with 80 people.

I need to get a high level view of resource levels over that year.

Doing individual people and entering people for something that big is a
massively time consuming task. We have done it for a year and a half. There
has to be a better way.

Now I could go down the route of getting Server. But that is a big
investment. £10,000 basically by the time your fully licenced for 6 PM's.

So I was trying to automate it myself using vb by grouping my resources into
groups and increasing the work unit. i.e. 10 gui programmer mean a resource
entry of Gui Programmers with max units of 10. Then I was creating a program
to automatically assign the resources.

Then I would fine tune it manually.

Should be relatively simple. Apart from the fact that Microsoft project
never quite does what you expect.

At the moment I am not using levelling. So it isn't that complicating things.

Mark

 

[Rod Gill]

I created a one task project with three resources. The following code added
them to the task and the task ended up exactly as I expected, duration
unchanged (Effort Driven is Off) and each task had the same hours as the
duration (max units=100).

Sub Test()
With ActiveProject.Tasks(1).Assignments
.Add ResourceID:=1
.Add ResourceID:=2
.Add ResourceID:=3
End With
End Sub


So, I'm not sure what you are doing. I suggest you simulate the code by
doing manually what you think the code is doing and see what happens.
Failing that, make a copy of the code and delete sections until the problem
doesn't occur, then add code in again until you can isolate exactly which
line causes the problem, then model that scenario manually. Project VBA code
executes exactly as if you were doing it manually.

--

Rod Gill
Project MVP

NEW!! Project VBA Book, for details visit: http://www.projectvbabook.com

 

[Mark]

I have found two things:

Firstly I was inserting the task populating both work and duration in code.
See below:

futureProjects.Tasks(position).OutlineLevel = (1 + tsk.OutlineLevel -
intFromLevel)
futureProjects.Tasks(position).Work = tsk.Work
futureProjects.Tasks(position).Estimated = tsk.Estimated
futureProjects.Tasks(position).Start = tsk.Start
futureProjects.Tasks(position).Priority = tsk.Priority
futureProjects.Tasks(position).Date1 = tsk.Date1
'futureProjects.Tasks(position).PercentComplete = tsk.PercentComplete
'futureProjects.Tasks(position).Duration = tsk.Duration
'futureProjects.Tasks(position).DurationEstimated = False

I copied your code and it didn't work. Still the same problem. Then I
stopped populating the duration and it worked.

Great I thought. So then I added in the units bit. And it went random again.

..Add ResourceID:=prjProjectToSearch.Resources(lngTemp).UniqueID, Units:=1

So

1. That code works if you don't populate the duration.
2. It goes mental if you try and put units in. I have even hard coded the
units to 1 which should do the same thing and it still went strange.

Mark

 

[Rod Gill]

How about:
Sub Test2()
Dim A As Assignment
Set A = ActiveProject.Tasks(1).Assignments.Add(1, 1)
A.Work = 20 * 60
ActiveProject.Tasks(1).Type = pjFixedWork
A.Units = 0.5
ActiveProject.Tasks(1).Type = pjFixedUnits
End Sub

Once the assignment has been created, the rules change. After that you are
editing the assignment and the Units = Work / Duration always applies. Fixed
Units is the most useful and flexible Fixed type which is why I reset it at
the end of the macro.

--

Rod Gill
Project MVP

NEW!! Project VBA Book, for details visit: http://www.projectvbabook.com

 

[Mark]

Hi Rod,

I think I have got it. And I think it is similar to what you are
suggesting....

Fundementally what I have been trying to do is insert task with a duration
and a work value and then add resources dividing the work between them by
percentage.

The problem I had before was that I was thinking I want the work to stay
constant and was using fixed work tasks. But if you use this type MS Project
will always try and assign the work to be done as quickly as possible even if
it is overallocating and then expects you to do something about it. So it
overallocates and reduces the duration. Hence I kept saying the units have
gone mad.

So what I now do now is this:

1. insert a task.
2. put in the duration and work
3. Make the task of type Fixed Duration
4. Asign the number of resources I want.
5. Take the work and divide by the percentages I want and put it in
Assignment.Work.

To be honest I reckon I don't need to populate the work in 2 and I think it
will still work as long as I save as a local variable and then use it.

Then I need on a regular basis to update the task (as effectively i am
taking these tasks from multiple low level projects and they will change as
people update their projects.)

What you then have to do is update the task.duration followed by task.work
keeping it as fixed duration.

Doing it that way means the any extra work gets assigned in the percentages
set.

If you do the work followed by a duration Project will reduce the work as it
keeps the hours the resources can work the same.

So this is quite similar to you. And in fact it may be your method would end
up calculating exactly the same duration when you change from FixedWork to
FixedUnits and change the percentage would end up getting the same duration.

Regards And Thanks For The Help

Mark

 

[Rod Gill]

Glad we got there in the end!!

--

Rod Gill
Project MVP

NEW!! Project VBA Book, for details visit: http://www.projectvbabook.com

 

[BillB]

Mark,

There's no need to assign task work. If you use Fixed Duration tasks, the
work should roll up from the assignments to the task level. I create tasks
as FD to begin with, then set in durations and finally assign resources. As
long as you keep updating the assignments, the task work should be updated
automatically.

Bill B

 

[Mark]

Thanks Bill. I will give it a try today and get back to you.

Regards

Mark