Material usage based on formula

[Darren101]

Hi All,
I am new to this forum and coming back to using MS project after 5+ years away.

The project that i am trying to setup is a home reno project and I have a question relating to materials usage. I want to have the ability to add the units as M² and then have a calculated field to show the number of litres of paint required and the cost to be based on the unit cost of the paint. is this feasible? alternatively i could manually work out the cost per M² but i would still need to have a caculated field for the amount of paint so that i can total for the project. If the above is not feasible, how would i reference the material usage qty in a formula for each of the resources? understanding that different paint resources may have different coverage rates.

[#2]

@Darren101

Well, yeah, its feasible but it will require a combination of custom field formulas, a custom field value list and some VBA. Here's the concept. The painting tasks are laid out with a couple extra custom fields. The Text1 field is a value list allowing selection of paint resource. The Task Number1 field (Sq feet) is used to directly enter the area to be painted, an ability you want to have.

The Resource Sheet also has custom field for entering the paint coverage spec (e.g. 400 sq ft/gal). This is pulled directly from the paint can and manually entered. The material label is usage (e.g. /gal). And of course the cost of a gallon of paint is entered in the Std Rate field.

That's the setup, now the good stuff. In order to sync paints shown on the value list with actual paint resources, macro code will read each paint resource and create the value list for the Task Text1 field. Once that is in place, the macro will calculate how many gallons are needed to paint the area and show that number in the Task Number2 field (Gal), something else you wanted. The macro will then go on to assign that amount of paint to the task with the total cost appearing in the Cost field.

As a side note, although this is all possible to do in Project it is quicker and easier to do what you want in Excel.

Sound good?

John

[#3]

@John-project 

Thanks John,

This sounds just like what i was looking for.

Are you able to provide any VBA examples of how this could be done?

Thanks Again

Darren

[#4]

@Darren101

Okay, here ya go. There are actually two macros. Run the first (SetUp_Paint_List) to set up the Task Text1 value list so you can select which material resource (paint) you want for the task. Run the second (MatlCost) to calculate how much paint will be needed based on your input of area in the Task Number1 field and coverage data entered in the Resource Number1 field. It will then calculate the total cost.

John

Option Explicit
'This procedure populates a value list with all material resources in file
Sub Setup_Paint_List()
Dim r As Resource
'First kill value list if it already exists (provides or real time changes to material list)
CustomFieldDelete Field:=pjTaskText1
'create value list with material resources
CustomFieldPropertiesEx FieldID:=pjCustomTaskText1, attribute:=pjFieldAttributeValueList
For Each r In ActiveProject.Resources
If r.Type = pjResourceTypeMaterial Then
CustomFieldValueListAdd FieldID:=pjCustomTaskText1, Value:=r.Name
End If
Next r
End Sub
'This procedure calculates amount of material needed based on user entry into Number1
' and then assigns that material to each task with a selected material (Text1)
Sub MatlCost()
Dim t As Task
Dim PaintType As String
Dim Need As Variant
'assign paints to tasks with entry in Text1
For Each t In ActiveProject.Tasks
If t.Text1 <> "" Then
PaintType = t.Text1
Need = CDec(t.Number1 / ActiveProject.Resources(PaintType).Number1)
t.ResourceNames = PaintType & "[" & Need & "]"
End If
Next t

End Sub