Custom field to test for resources from a specific group

[jomouk]

Hi,

I am trying to write a formula that will test each task for resources belonging to a specific group and then sum/display the total work content (for that task) for the resources from that group...

eg. A task with duration of 8hrs uses 3 resources of which 2 are in group A and 1 is in group B. I want an easy way to identify the total work for Group A. This would be 16hrs in this example

I can use the formula.. InStr([Resource Group],"GROUPA" to identify if any resources from Group A are present. However, I don't know how extend the formula to sum the total work for all Group A resources on that task.

Any help appreciated

Thanks

J

 

[PDQBach]

If "formula" means that you have tried using the Customize functionality to create a formula for a field such as Number1 then I have been unable (after several tries) to create a formula that will do what you ask.

I suspect it's a fairly straight forward piece of VBA code (I didn't try that). Let me know if VBA macro is an acceptable solution and I'll see what I can do.

 

[jomouk]

Hi PDQBach,

Yes, that's exactly what I meant.

I've never used VBA code but if you can assist, I'll give it a go.

Thanks for your help

 

[PDQBach]

Before I give you the code (which is more than a little kludgy), why not

View | Resource Usage
Set "Group By" to 'Resource Group'
Set "Show" to 'Outline level 1'

 

[jomouk]

Hi,

It's a long story but essentially, I'm working with some guys in China who don't know how to use Project. Trying to teach them with time and language difference is impossible. I just need an easy way for them to see their commitment to each task.

I know there are several ways to do this but I'm trying to come close to a method they have used previously.

Thanks

J

 

[PDQBach]

OK ... you asked for it!

Start Project
Open your project file
Alt+F11 to open the VBA window
In the VBA windown on the left side, you'll see your project name; click once on it.
In the VBA window on the menu bar, click on Insert | Module

Watch for line wraps in the text that follows.

Sub PDQBach1()
Dim tsk As Task
Dim assgn As Assignment
Dim res As Resource

Dim strMsgBox As String
Dim strGroup As String
Dim strSp As String

Dim lngWork() As Long
Dim lngWorkIndex As Long

Dim lngLength As Long

Dim resID As Long


'Find the longest Group name
lngLength = 0
For Each res In ActiveProject.Resources
If Not res Is Nothing Then
If Len(res.Group) > lngLength Then
lngLength = Len(res.Group)
End If
End If
Next

'Initialize some fields
strSp = Space(lngLength)
lngLength = lngLength + 1
'jb strGroup = "/" & strSp

'Build string with all Group names
For Each res In ActiveProject.Resources
If Not res Is Nothing Then
If InStr(1, strGroup, "/" & res.Group) = 0 Then
strGroup = strGroup & Left("/" & res.Group & strSp, lngLength)
End If
End If
Next

lngWorkIndex = Len(strGroup) / lngLength
ReDim lngWork(lngWorkIndex)

'Loop through project getting each task
' (A task can have 0 or more assignments)
' Loop through the task getting each assignment
' Find the resource assigned
' Find the Group name for the resource
' Look it up in the string with all group names (and fiddle to get an integer)
' Add the work (which is in minutes) to the array total
' End loop of each assignment
'End loop of each task

For Each tsk In ActiveProject.Tasks
If Not tsk Is Nothing Then
If tsk.Assignments.Count > 0 Then
For Each assgn In tsk.Assignments
resID = assgn.ResourceID
lngWorkIndex = ((InStr(1, strGroup, "/" & ActiveProject.Resources(resID).Group) - 1) / lngLength)
lngWork(lngWorkIndex) = lngWork(lngWorkIndex) + assgn.Work
Next
End If
End If
Next

'Prepare msgbox display
strGroup = Replace(strGroup, " ", "_")
strMsgBox = ""
lngWorkIndex = Len(strGroup) / lngLength
For i = 0 To lngWorkIndex - 1
strMsgBox = strMsgBox & Mid(strGroup, (lngLength * i) + 2, lngLength - 1) _
& vbTab _
& lngWork(i) / (ActiveProject.HoursPerDay * 60) _
& vbCrLf
Next
MsgBox strMsgBox, vbOKOnly, "Resource Group and Work Days"

End Sub


Pick up all that text and paste it into the module you just opened.
Close the VBA window (click on the X in the upper right corner) -- don't worry about saving, that happens next.
Save the project file (saves the project and the macro)
Alt+F8 to open the list of macros.
Look for PDQBach1
Double-click on it. You'll get a msgbox showing each group and the number of work days in the group.

You can compare those numbers with the results produced by my earlier message. In my tests, they matched (occasionally off in the 2nd or 3rd decimal position because of rounding by Project).

Hope this is what you wanted.

 

[jomouk]

Hi PDQBach,

Thanks for the detailed response. I will try this asap and let you know how it goes.

Thanks very much for your help.

 

[jomouk]

Hi PDQBach

Finally got a chance to try this.

The good news....your VBA works perfectly. The not-so-good news (for me at least)...it isn't quite what I'm after

I want to add a column that will identify the total work effort for the group for each task.

eg...

Task Name Group 1 Group 2 Work
Work Work
Task 1 24 hrs 8 hrs 32 hrs
Task 2 16 hr 32 hrs 48 hrs


If you have any ideas how I might be able to achieve this, please let me know.

Thanks

J

 

[PDQBach]

You'll need to write VBA. It's not clear why you have two groups but 3 columns headed work. This will be a non-trivial piece of VBA. Good luck.