Forum Discussion

Darren101's avatar
Darren101
Copper Contributor
Feb 20, 2023

Material usage based on formula

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.

 

  • John-project's avatar
    John-project
    Silver Contributor
    Feb 21, 2023

    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

    • Darren101's avatar
      Darren101
      Copper Contributor
      Feb 23, 2023

      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

       

       

      • John-project's avatar
        John-project
        Silver Contributor
        Feb 23, 2023

        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

Share

Resources