none
how to use formula in enterprise custom field "Work" or "Cost" RRS feed

  • Question

  • Hi there,I am using MS Project 2010 and I am looking for a way to customize enterprise custom field such as "Work" and "Cost" field where I was to use a formula (eg: Number1+Number2).
    I came to know I need to use VBA to perform that task.Could anyone please suggest if there is any way I could do that.I will really really appreciate your advice.Thanks in advance.
    • Edited by robeenclarke Sunday, March 2, 2014 10:35 PM update
    Sunday, March 2, 2014 10:31 PM

Answers

  • robeenclarke,

    For your initial estimations of hours and cost Excel is a much better application. I wouldn't set anything up in Project until I was ready to actually create a plan. However, in the spirit of answering your original question, the following simple macro will take the values entered into the Number1 and Number2 field, sum them, and then write them into the Work field for each task. Note that Project stores all time values in minutes so if the values in the number fields are intended to be hours, then they must be multiplied by 60 to end up with hours for the Work field.

    Sub SumWork()
    Dim t As Task
    For Each t In ActiveProject.Tasks
        If Not t Is Nothing Then
            t.Work = t.Number1 * 60 + t.Number2 * 60
        End If
    Next t
    End Sub

    I don't understand your statement about wanting to default the Finish field to the Date1 field if there is no date in the Finish field. You must be using manual scheduling since Project will automatically populate the Start and Finish fields for normal auto-scheduled tasks. Are you saying you would like a macro that looks at the Finish field and if it is blank, then take the date in the Date1 field and put it into the Finish field?

    With regard to getting your feet wet with VBA, I highly recommend Rod Gill's book on Project VBA. I learned Project VBA before Rod wrote his book but self-taught is not for everybody. For more information on his book, see: http://www.project-systems.co.nz/project-vba-book/index.html

    I ran the macro your wrote and it works fine for me. I don't use Project Server so I can't test the full realm of enterprise properties but I do have Project Pro so I can write macro for the server environment. You said you did not see the change, and by that I assume mean "hello" in the Name field of the Project Summary task, are you sure you were looking at the Project Summary task? What did you see in the Name field of the Project Summary Task?

    I realize you are trying to feel your way with VBA, but your entire macro could effectively be done with this single line:

    activeproject.ProjectSummaryTask.Name = "hello"

    John

    • Marked as answer by robeenclarke Tuesday, March 4, 2014 4:29 PM
    Monday, March 3, 2014 8:30 PM
  • robeenclarke,

    On the surface it might appear that it would be easy to look at the Start and/or Finish fields of a manual task and see that it is blank, but in reality it isn't so easy. The blank field you see is a view presentation only. Internally Project does in fact have a default date in those "blank" fields and that's the date you see when you query the date using VBA. If the Start field of a manual task is blank, then internally both the start and finish dates of that task take on the Project Start Date. If the Start field of a manual task has a date but the Finish field is blank, then internally the finish date is the same as the start date. And to make it even more interesting, the internal finish date of a manual task will track with the duration and/or the Work field.

    So the bottom line of this is, "what you see is NOT what you get". Certainly some assumptions could be made based on the above and those assumptions could be integrated into a macro to populate the "blank" Finish field but I wouldn't say it would be foolproof. If you have some very defined characteristics for those tasks with a blank finish date (i.e. also no start date, and/or no duration, and/or no work), and those characteristics are consistent throughout your whole file, then a simple macro could be developed.

    With respect to the "If Not t is Nothing Then" statement. The purpose of that statement is to avoid a runtime error is the file has blank task lines. Some users like to visually separate parts of their plan by inserting blank task lines between major sections, so a good practice when writing Project macros is to always include that statement in the loop.

    Update: However, after writing all the above verbiage, I believe I may have a workaround. As it turns out, even though you can't directly query the Start and/or Finish field of a manual task, you can transfer that blank string to a text field and then query that text field, so you can get there from here with an extra step.

    Try this process. First customize the Text3 field with the following formula:

    Text3 = [finish]

    Now run this macro:

    Sub SetFinishDate()
    Dim t As Task
    For Each t In ActiveProject.Tasks
        If Not t Is Nothing Then
            If t.Summary = False And t.Manual = True And t.Text3 = "" Then
            t.Finish = t.Date1
            End If
        End If
    Next t
    End Sub

    John



    • Edited by John - Project Tuesday, March 4, 2014 12:41 AM full bannana
    • Marked as answer by robeenclarke Tuesday, March 4, 2014 4:29 PM
    Tuesday, March 4, 2014 12:00 AM

All replies

  • robeenclarke,

    Although there are multiple extra cost fields, including enterprise fields, that can be customized, there are no extra work fields that can be customized, so what exactly are you referring to when talking about custom enterprise work fields?

    It is difficult to suggest a specific VBA approach without more detail knowledge of what you are trying to do. For example, do you simply want to populate a particular enterprise cost field with a formula that is the sum to two enterprise number fields? If so, which cost field (e.g. assignment enterprise, project enterprise, task enterprise, etc.), and which number fields? Is it only for specific tasks or all tasks in the project?

    John

    • Marked as answer by robeenclarke Tuesday, March 4, 2014 4:28 PM
    • Unmarked as answer by robeenclarke Tuesday, March 4, 2014 4:29 PM
    Monday, March 3, 2014 1:05 AM
  • Hi John,thanks for your reply!  I want to customise the task based work and cost field,I could use formulas for extra cost fields in Ms project "custom field" option. But I could not use formulas on the only Work field.

    Right now I am entering values in task based "Work" field manually. Yes ,noticed there are no extra work fields as work1, work2 ..etc.But there are for Costs like cost1,,cost2 including enterprise Cost field.

    I am trying to populate the task based Work field by using formula.I have two fields; number1 for labour hours and number 2 for design hours.Now I need to add up these two numbers to show at the the enterprise"Work" field.

    For Cost I am using Task enterprise too. But I have values at two extra cost fields Cost1 and Cost2.I am adding these two costs at the main Task Cost field.I saw that I can use formulas at Cost1, cost2..cost20.But there is no option for using formula in the main enterprise Cost field.Also it is for all tasks in the project.

    I really appreciate your help!Looking forward to hear from you.Thanks!


    Monday, March 3, 2014 3:21 AM
  • robeenclarke,

    Formulas can only be used for custom fields like Cost1, Cost2, Number1, Number2, etc. Formulas cannot be used to customize normal Project fields such as Work, Cost, Duration, etc. You can use VBA to modify the contents of normal Project fields but I'm not sure it would be worth the effort to do that. If you have separate work estimates for labor and design for example, either add them together before entering into Project's work field (use a calculator or Excel or even a sheet of paper), or show those work efforts as separate tasks. For example, design effort is normally accomplished before build effort. Physical labor (construction, manufacturing, etc.) then follows based on the output of the design. Those are separate tasks.

    If for some reason you want to put everything into one task line, then as I said, add the hours outside of Project before you enter them. You can always use an extra text field (e.g. Text1) or the Notes field to document the separate hours for reference.

    John

    Monday, March 3, 2014 3:26 PM
  • Hi John, thanks so much for replying!  I am doing a pre estimation report for about five hundred projects in one task line.At this moment I am not linking the tasks, rather just getting an estimation for rough time and cost estimation for design and labour.If I have to add them seperately for all 500 hundred projects that would be a lot of work.I did exported thse all information to excel and performed the calculation with  formulas, and then exported back them to project. But it could be nice if I could do this in ms project itself.Could you please tell me if there is any way I could default my "Finish" field to "date1" field unless some one manually enters value to Finish field.

    The code below I was trying to learn how vba works and just took a baby step but trying to set a value on name field using vba.It appears on the message dialogue box that the value has been set , but when I go back to project I do not see the change.Could you please help me? Thanks you so much for helping me out here.

    Sub TestEnterpriseProjectCF()
        Dim projectField As Long
        Dim projectFieldName As String
        Dim message As String
     
        projectField = FieldNameToFieldConstant("name", pjProject)
     
        ' Show the enterprise project field number and old value.
        message = "Enterprise project field number: " & projectField & vbCrLf
        MsgBox message & ActiveProject.ProjectSummaryTask.GetField(projectField)
     
        ActiveProject.ProjectSummaryTask.SetField FieldID:=projectField, Value:="hello"
     
        ' For a demonstration, show the field name from the field number, and verify the new value.
        projectFieldName = FieldConstantToFieldName(projectField)
        message = "New value for field: " & projectFieldName & vbCrLf
        MsgBox message & ActiveProject.ProjectSummaryTask.GetField(projectField)
    End Sub


    • Edited by robeenclarke Monday, March 3, 2014 5:47 PM
    • Marked as answer by robeenclarke Tuesday, March 4, 2014 4:29 PM
    • Unmarked as answer by robeenclarke Tuesday, March 4, 2014 4:29 PM
    Monday, March 3, 2014 5:46 PM
  • robeenclarke,

    For your initial estimations of hours and cost Excel is a much better application. I wouldn't set anything up in Project until I was ready to actually create a plan. However, in the spirit of answering your original question, the following simple macro will take the values entered into the Number1 and Number2 field, sum them, and then write them into the Work field for each task. Note that Project stores all time values in minutes so if the values in the number fields are intended to be hours, then they must be multiplied by 60 to end up with hours for the Work field.

    Sub SumWork()
    Dim t As Task
    For Each t In ActiveProject.Tasks
        If Not t Is Nothing Then
            t.Work = t.Number1 * 60 + t.Number2 * 60
        End If
    Next t
    End Sub

    I don't understand your statement about wanting to default the Finish field to the Date1 field if there is no date in the Finish field. You must be using manual scheduling since Project will automatically populate the Start and Finish fields for normal auto-scheduled tasks. Are you saying you would like a macro that looks at the Finish field and if it is blank, then take the date in the Date1 field and put it into the Finish field?

    With regard to getting your feet wet with VBA, I highly recommend Rod Gill's book on Project VBA. I learned Project VBA before Rod wrote his book but self-taught is not for everybody. For more information on his book, see: http://www.project-systems.co.nz/project-vba-book/index.html

    I ran the macro your wrote and it works fine for me. I don't use Project Server so I can't test the full realm of enterprise properties but I do have Project Pro so I can write macro for the server environment. You said you did not see the change, and by that I assume mean "hello" in the Name field of the Project Summary task, are you sure you were looking at the Project Summary task? What did you see in the Name field of the Project Summary Task?

    I realize you are trying to feel your way with VBA, but your entire macro could effectively be done with this single line:

    activeproject.ProjectSummaryTask.Name = "hello"

    John

    • Marked as answer by robeenclarke Tuesday, March 4, 2014 4:29 PM
    Monday, March 3, 2014 8:30 PM
  • Hi John! Many many thanks for your reply! Your code worked like magic!It saved a lot of my work.You are right Excel is the best for these calculation,but for now I that I had all in project it worked perfect.thanks!

    From your comment "Are you saying you would like a macro that looks at the Finish field and if it is blank, then take the date in the Date1 field and put it into the Finish field?" You are absolutely right.Sorry I am very bad at explaining things.I tried the same code you gave in the following manner , but figured that even if the cell had any value it overwrite that value and defaults the date1 column.

    Sub Finish()

    Dim t As Task

    For Each t In ActiveProject.Tasks

        If Not t Is Nothing Then

            t.Finish = t.Date1

        End If

    Next t

    End Sub

     From the third line here "If Not t Is Nothing Then"-since you had this line I thought it will enter only when this cell is empty but defaults no matter this cell has value or not. Do you suggest there is any way such that this code will default only when it is blank.Sorry to bug.I think I need to learn all the codes from the Rod Gill book.I will try to bid that book in ebay.It is $54 in Amazon, little expensive.

    Also "activeproject.ProjectSummaryTask.Name = "hello" , yes this is exacly what I was trying to do, I just coppied that code from microsoft website, since I dont have knowledge on VBA, so just tried to see what this piece of code does.

    Thank you so much for helping.I really appreciate!I am very much looking forward to hear from you.





    Monday, March 3, 2014 10:52 PM
  • robeenclarke,

    On the surface it might appear that it would be easy to look at the Start and/or Finish fields of a manual task and see that it is blank, but in reality it isn't so easy. The blank field you see is a view presentation only. Internally Project does in fact have a default date in those "blank" fields and that's the date you see when you query the date using VBA. If the Start field of a manual task is blank, then internally both the start and finish dates of that task take on the Project Start Date. If the Start field of a manual task has a date but the Finish field is blank, then internally the finish date is the same as the start date. And to make it even more interesting, the internal finish date of a manual task will track with the duration and/or the Work field.

    So the bottom line of this is, "what you see is NOT what you get". Certainly some assumptions could be made based on the above and those assumptions could be integrated into a macro to populate the "blank" Finish field but I wouldn't say it would be foolproof. If you have some very defined characteristics for those tasks with a blank finish date (i.e. also no start date, and/or no duration, and/or no work), and those characteristics are consistent throughout your whole file, then a simple macro could be developed.

    With respect to the "If Not t is Nothing Then" statement. The purpose of that statement is to avoid a runtime error is the file has blank task lines. Some users like to visually separate parts of their plan by inserting blank task lines between major sections, so a good practice when writing Project macros is to always include that statement in the loop.

    Update: However, after writing all the above verbiage, I believe I may have a workaround. As it turns out, even though you can't directly query the Start and/or Finish field of a manual task, you can transfer that blank string to a text field and then query that text field, so you can get there from here with an extra step.

    Try this process. First customize the Text3 field with the following formula:

    Text3 = [finish]

    Now run this macro:

    Sub SetFinishDate()
    Dim t As Task
    For Each t In ActiveProject.Tasks
        If Not t Is Nothing Then
            If t.Summary = False And t.Manual = True And t.Text3 = "" Then
            t.Finish = t.Date1
            End If
        End If
    Next t
    End Sub

    John



    • Edited by John - Project Tuesday, March 4, 2014 12:41 AM full bannana
    • Marked as answer by robeenclarke Tuesday, March 4, 2014 4:29 PM
    Tuesday, March 4, 2014 12:00 AM
  • Dear John,You are brilliant! It is doing exactly what I was looking for.I am sorry I couldn't get back to you earlier.I could never do that myself.I understood that if t.Text3=blank and t.Summary = False  then insert finish=date1..but when you typed " t.Manual=true" , how does this piece of code checks that if there is any value manually entered to the Text3 field of this task ,I meant where does it say to look at the text3 field. Or if t.manual is actually checking all fields to see if there is any value manually entered in any of the field of this task.But I guess the second assumption is not right because I manually change other fields of one task keeping finish field blank then running this code still filled out the finish field.

    I am still going to buy the book you suggested online.In the meantime do you suggest any website where I could get something to read to learn about the objects, such as manual, summary,project etc.Like how do I know more terms like ActiveProject,activeproject.ProjectSummaryTask.Name etc . Any reading suggestion will be a great help John.I really want to learn this vba.I really really appreciate!Thanks you so much.

    Tuesday, March 4, 2014 4:28 PM
  • robeenclarke,

    This simple approach makes some assumptions. The macro would need some enhancements if some of those assumptions are not valid in all cases.

    Basically the code assumes the whole file is manually scheduled, mainly because that is the only case where the Finish field can be visually blank. It also assumes you have set up the custom field formula to put the contents of the Finish field into the Text3 field before running the macro. Once the macro is executed, the custom field formula will automatically update the Text3 field. If the macro needs to be run multiple times, the automatic update may or may not cause an issue.

    The macro loops through all tasks in the file. It jumps around summary tasks and any tasks that may happen to be auto-scheduled, and it looks for any tasks that have a null string (i.e. "") in the Text3 field. All of this is handled by the big "If" statement. If the whole "If" statement is true, then it writes the contents of the Date1 field into the Finish field.

    With regard to learning more about Project VBA elements, one of the best sources is the Object Browser which can be accessed via an icon once the VB Editor window is opened. You could also take a look at the following links:

    1. Although it is a bit dated, the basic information is still valid. Go to: http://project.mvps.org/links.htm, and scroll to the bottom of the page. There you will find a link to the Project 98 Visual Basic Environment Training Materials.

    2. You can find some good example of code on Jack Dahlgren's webpage at: http://masamiki.com/project/macros.htm

    John

    Tuesday, March 4, 2014 5:28 PM
  • It is crystal clear now. I was not sure how the searching process works.That explaination was awesome! Didn't have any trouble understanding.You have provided with great resources.From now I will follow those links that you provided.Seems like lots to read and exercise.Thank you so much John.You have no idea how much you have helped me.I really appreciate:)
    Tuesday, March 4, 2014 7:24 PM
  • robeenclarke,

    You're welcome and thanks for the feedback.

    John

    Tuesday, March 4, 2014 7:45 PM