none
Project 2007 - Copying Start1 value to pjTaskStart using VBA RRS feed

  • Question

  • Dear Gurus,

    Apologies first, I'm inexperienced with Project VBA, however from the internet I've managed to get some small code segments working.

    I've been struggling with someting that I accept MS Project was not designed to do, basically feed the start and finish dates from my own calculated dates. Reasons are fairly obscure, but I do have such a bizzare need.

    Within my macro loop I've got to this point:

    Dim i As Long
    Dim TempCal As Variant
        For i = 1 To ActiveProject.Tasks.Count Step 1
            If Not ActiveProject.Tasks(i) Is Nothing Then
                If ActiveProject.Tasks(i).GetField(FieldNameToFieldConstant("Start MBJ1")) > 0 Then
                    ActiveProject.Tasks(i).SetField pjTaskDuration, "0?"
                    TempCal = ActiveProject.Tasks(i).GetField(FieldNameToFieldConstant("Start1"))
                    ActiveProject.Tasks(i).SetField pjTaskStart, TempCal
                    TempCal = ActiveProject.Tasks(i).GetField(FieldNameToFieldConstant("Finish1"))
                    ActiveProject.Tasks(i).SetField pjTaskFinish, TempCal
                End If
            End If
        Next i

    Macro appears to compile OK, and runs fine. I stepped through it and it selects the corect lines of the project and correctly sets the pjTaskDuration

    With the pjTaskStart and pjTaskFinish, I'm getting nothing but random results. I checked the values of TempCal prior to attempting to SetField, and in both cases the values contained were the correct dates I wished.

    Can anybody guide me to my mistake?

    Many thanks for your time.

    Monday, August 6, 2012 8:46 AM

Answers

  • Hi,

    Supposing the tasks are automatically scheduled, you should be aware of the fact that

    TaskX.start="3/9/12" does NOT NECESSARILLY put he start date of the task to 3/9/12.

    In fact, it is equivalent to the following two statements:

    TaskX. Constrainttype= pjStartNoEarlierthan

    TaskX. Constraintdate="3/9/12"

    So if TaskX has predecessors the value in Start may be different from 3/9/12. Start is a calculated field and your input only adds 1 constraint to the calculation!

    Greetings,

    Tuesday, August 7, 2012 7:00 PM
    Moderator
  • In addition to what Jan said you shoudl know that if the start1 and finish1 dates are not exactly the same then you could also end up with odd results since you set the duration to 0. This added in with the possibility of links between tasks and you end up with several possible explanations for your results. it will depend entirely on the links to this task and the values of start1 and finish1.

    On an unlreated note since you are new to VBA take a look at the code below. It provides a slightly more efficient way to do your same code. On a VERY large project this code will run faster since it is not setting values into a variable and then writing them to the field. it also shows how to use the task collection in a different way that can be easier for many people.

    Dim T As Task
     
     For Each T In ActiveProject.Tasks
        If Not (T Is Nothing) Then
            If T.GetField(FieldNameToFieldConstant("Start MBJ1")) > 0 Then
                T.Duration = "0?"
                T.Start = T.Start1
                T.Finish = T.Finish1
            End If
        End If
     Next T


    Brian Kennemer - Project MVP
    DeltaBahn Senior Architect
    endlessly obsessing about Project Server…so that you don’t have to.
    Blog | Twitter | LinkedIn

    Tuesday, August 7, 2012 7:45 PM
    Moderator
  • StuckInDaOffice,

    Sorry, I was in too much of a rush to get moving on other stuff this morning that I didn't give you a good answer. Fortunately both Jan and Brian stepped in with some words of wisdom. Just to reiterate and expand on Jan's and Brian's comments, by trying to directly enter a task's start date, duration and finish date you are basically attempting to force Project to do something that may not be possible. Project's scheduling engine operates on very basic data. Task start dates are driven by the Project Start Date, a Start-no-earlier-than constraint date, or links from predecessor tasks. Task finish dates are driven by the task's start date, the task's duration and of course the work calendar and maybe by a resource's calendar if it is different from the task calendar.

    Even though Brian provided a much more efficient way of doing what you are attempting, I think it would be helpful for all of us to better understand your "fairly obscure" reasons for trying to do it. It is very likely we can help guide you towards a better approach. We'll feel better about helping you and you will be a lot happier.

    John

    Wednesday, August 8, 2012 3:00 AM
  • Dear Brian, Jan, and John,

    Many thanks for the guidance. With Brian's code snippet, I've managed to get the code running better, but it still gave erratic responses.

    Made the breakthrough this morning, as the snippet I provided was incomplete. When I ignored the intermediate settings that VBA reported in the debugger, and continued as if it had done what I expected, the code worked. It appears that the values reported by the debugger are not accurate, and the final update that appeared to fix the routine was the setting ot the constraint type to "Must Finish On"

    Final(ish) code is below:

    Dim T As Task

    Application.Calculation = pjManual

    For Each T In ActiveProject.Tasks
        If Not (T Is Nothing) Then
            If T.GetField(FieldNameToFieldConstant("Start MBJ1")) > 0 Then
                T.Duration = "0?"
                T.Start = T.Start1
                T.Finish = T.Finish1
                T.Duration = T.Duration1
                T.ConstraintDate = T.Finish1
                T.ConstraintType = "Must Finish On"
            End If
        End If
    Next T

    Application.Calculation = pjAutomatic
    Application.CalculateAll

    I plan to incorporate some error trapping now, but the code does appear to do what I requuire.

    FYI: The "fairly obscure" reasons are that I'm entirely miss-using Project to document a fixed project plan that we're required to comply with. The intent is to use project to add our own "Flexible" project information to meet these fixed requirements. The code was, and is, intended to rebase the fixed project. I was aware that the sequence of setting the values was important to ensure the correct results ended up in each task, but did not understand that I needed to ensure the sequence was complete before I polled the VBA code for correct functioning.

    Many thanks for your time and guidance, hopefully I'll be better placed now to code the additional routines I need. Always helps to start off correctly ;-)

    Friday, August 10, 2012 9:45 AM

All replies

  • StuckinDaOffice,

    What version of Project are you using? If it's Project 2010, you may simply be addressing the wrong date properties. For example, in Project 2010, the old Start field is not the Schedule Start field. This is due to some field "rearranging" brought on by the addition of the manual scheduling mode.

    If I had a little more time I'd post a simple macro that would do what you want, but I don't and it's of value to you to explore VBA yourself to get more experienced.

    John

    Tuesday, August 7, 2012 3:49 PM
  • Hi John,

    As in the title I'm using Project 2007.

    I do understand that you're busy, however I've been struggling for several weeks now on this, and I would not have posted unless I'd exhaused all my options.

    Truth is I do not understand why the code is not working, and I'm not asking for somebody to code it for me, I'm trying to understand why a simple copy operation does not appear to work.

    I would hope that you'd understand that and at least guide me towards a resolution.

    Best regards

    Tuesday, August 7, 2012 5:41 PM
  • Hi,

    Supposing the tasks are automatically scheduled, you should be aware of the fact that

    TaskX.start="3/9/12" does NOT NECESSARILLY put he start date of the task to 3/9/12.

    In fact, it is equivalent to the following two statements:

    TaskX. Constrainttype= pjStartNoEarlierthan

    TaskX. Constraintdate="3/9/12"

    So if TaskX has predecessors the value in Start may be different from 3/9/12. Start is a calculated field and your input only adds 1 constraint to the calculation!

    Greetings,

    Tuesday, August 7, 2012 7:00 PM
    Moderator
  • In addition to what Jan said you shoudl know that if the start1 and finish1 dates are not exactly the same then you could also end up with odd results since you set the duration to 0. This added in with the possibility of links between tasks and you end up with several possible explanations for your results. it will depend entirely on the links to this task and the values of start1 and finish1.

    On an unlreated note since you are new to VBA take a look at the code below. It provides a slightly more efficient way to do your same code. On a VERY large project this code will run faster since it is not setting values into a variable and then writing them to the field. it also shows how to use the task collection in a different way that can be easier for many people.

    Dim T As Task
     
     For Each T In ActiveProject.Tasks
        If Not (T Is Nothing) Then
            If T.GetField(FieldNameToFieldConstant("Start MBJ1")) > 0 Then
                T.Duration = "0?"
                T.Start = T.Start1
                T.Finish = T.Finish1
            End If
        End If
     Next T


    Brian Kennemer - Project MVP
    DeltaBahn Senior Architect
    endlessly obsessing about Project Server…so that you don’t have to.
    Blog | Twitter | LinkedIn

    Tuesday, August 7, 2012 7:45 PM
    Moderator
  • StuckInDaOffice,

    Sorry, I was in too much of a rush to get moving on other stuff this morning that I didn't give you a good answer. Fortunately both Jan and Brian stepped in with some words of wisdom. Just to reiterate and expand on Jan's and Brian's comments, by trying to directly enter a task's start date, duration and finish date you are basically attempting to force Project to do something that may not be possible. Project's scheduling engine operates on very basic data. Task start dates are driven by the Project Start Date, a Start-no-earlier-than constraint date, or links from predecessor tasks. Task finish dates are driven by the task's start date, the task's duration and of course the work calendar and maybe by a resource's calendar if it is different from the task calendar.

    Even though Brian provided a much more efficient way of doing what you are attempting, I think it would be helpful for all of us to better understand your "fairly obscure" reasons for trying to do it. It is very likely we can help guide you towards a better approach. We'll feel better about helping you and you will be a lot happier.

    John

    Wednesday, August 8, 2012 3:00 AM
  • Dear Brian, Jan, and John,

    Many thanks for the guidance. With Brian's code snippet, I've managed to get the code running better, but it still gave erratic responses.

    Made the breakthrough this morning, as the snippet I provided was incomplete. When I ignored the intermediate settings that VBA reported in the debugger, and continued as if it had done what I expected, the code worked. It appears that the values reported by the debugger are not accurate, and the final update that appeared to fix the routine was the setting ot the constraint type to "Must Finish On"

    Final(ish) code is below:

    Dim T As Task

    Application.Calculation = pjManual

    For Each T In ActiveProject.Tasks
        If Not (T Is Nothing) Then
            If T.GetField(FieldNameToFieldConstant("Start MBJ1")) > 0 Then
                T.Duration = "0?"
                T.Start = T.Start1
                T.Finish = T.Finish1
                T.Duration = T.Duration1
                T.ConstraintDate = T.Finish1
                T.ConstraintType = "Must Finish On"
            End If
        End If
    Next T

    Application.Calculation = pjAutomatic
    Application.CalculateAll

    I plan to incorporate some error trapping now, but the code does appear to do what I requuire.

    FYI: The "fairly obscure" reasons are that I'm entirely miss-using Project to document a fixed project plan that we're required to comply with. The intent is to use project to add our own "Flexible" project information to meet these fixed requirements. The code was, and is, intended to rebase the fixed project. I was aware that the sequence of setting the values was important to ensure the correct results ended up in each task, but did not understand that I needed to ensure the sequence was complete before I polled the VBA code for correct functioning.

    Many thanks for your time and guidance, hopefully I'll be better placed now to code the additional routines I need. Always helps to start off correctly ;-)

    Friday, August 10, 2012 9:45 AM