none
ProjectSummaryTask and TimeScaleValues Puzzle RRS feed

  • Question

  • I'm trying to load an internal array with BCWS and BCWP time scale values from the ProjectSummaryTask (code snippet listed below), and I'm having a weird problem.

    The BCWS loads correctly, but the BCWP either does not load at all or (even more puzzling) loads up to the fourth array element and then does not update the values in the array. I know that the BCWP time scale values are being read: I display each tsv with a MsgBox, and I assign each value to a variable and watch the value change in the Locals window. But, the array update, which occurs right after the MsgBox and variable update, does not work.

    Does anyone have an idea of what's causing this behavior, and what I can do to correct it?

    Code:

                'For project summary task, get an array with each and every days BCWP

                 Set tsvsBCWS = ProjectSummaryTask.TimeScaleData( _

                 StartDate:=InputStartDate, EndDate:=InputFinishDate, _

                 Type:=pjTaskTimescaledBCWS, TimeScaleUnit:=TimeScalePeriodLength, _

                  Count:=1)

                'For project summary task, get an array with each and every days BCWP

                 Set tsvsBCWP =ProjectSummaryTask.TimeScaleData( _

                 StartDate:=InputStartDate, EndDate:=InputFinishDate, _

                 Type:=pjTaskTimescaledBCWP, TimeScaleUnit:=TimeScalePeriodLength, _

                 Count:=1)     

          

                 'Insert this tasks BCWS into each period bucket

                 j = 1

                 For Each tsvBCWS In tsvsBCWS

                    PeriodicBCWS(j) = VBA.Val(tsvBCWS.Value)

                    j = j + 1

                 Next tsvBCWS

                 'Insert this tasks BCWP into each period bucket

                 j = 1

                 For Each tsvBCWP In tsvsBCWP

                    MsgBox Val(tsvBCWP.Value) ‘display tsv

                    x = Val(tsvBCWP.Value) ‘assign to variable

                    PeriodicBCWP(j) = VBA.Val(tsvBCWP.Value)

                    j = j + 1

                 Next tsvBCWP


    RobVV


    • Edited by RobVV Wednesday, December 31, 2014 9:38 PM
    Wednesday, December 31, 2014 9:35 PM

All replies

  • Rob,

    First of all, what are you doing working on this stuff New Year's eve? Are you nuts?

    Unfortunately I'm going to have to give you the old "it works for me" answer, although I did need to complete and tweak your code. This is the modified version I used on simple test project that goes from Dec 29, 2014 through Jan 16, 2015.

    John

    Sub RobVV()
    Dim PeriodicBCWS()
    Dim PeriodicBCWP()
    Dim tsvsBCWS As TimeScaleValues
    Dim tsvBCWS As TimeScaleValue
    Dim tsvsBCWP As TimeScaleValues
    Dim tsvBCWP As TimeScaleValue
    Dim j As Integer, span As Integer
    Dim InputStartDate As Date
    Dim InputFinishDate As Date

    InputStartDate = ActiveProject.ProjectStart
    InputFinishDate = ActiveProject.ProjectFinish
    span = DateDiff("d", InputStartDate, InputFinishDate) + 1
    ReDim PeriodicBCWS(span), PeriodicBCWP(span)

    'For project summary task, get an array with each and every days BCWS
    Set tsvsBCWS = ActiveProject.ProjectSummaryTask.TimeScaleData( _
        StartDate:=InputStartDate, EndDate:=InputFinishDate, _
        Type:=pjTaskTimescaledBCWS, TimeScaleUnit:=pjTimescaleDays, _
        Count:=1)
        
    'For project summary task, get an array with each and every days BCWP
        Set tsvsBCWP = ActiveProject.ProjectSummaryTask.TimeScaleData( _
            StartDate:=InputStartDate, EndDate:=InputFinishDate, _
            Type:=pjTaskTimescaledBCWP, TimeScaleUnit:=pjTimescaleDays, _
            Count:=1)
            
    'Insert BCWS values into each period bucket
    j = 1
    For Each tsvBCWS In tsvsBCWS
    Debug.Print tsvBCWS.Value   'print to Immediate window
        PeriodicBCWS(j) = tsvBCWS.Value
        j = j + 1
    Next tsvBCWS

    'Insert BCWP values into each period bucket
    j = 1
    For Each tsvBCWP In tsvsBCWP
        Debug.Print tsvBCWP.Value
        PeriodicBCWP(j) = tsvBCWP.Value
        j = j + 1
    Next tsvBCWP

    End Sub

    And here are the screen shots of the two arrays:

        

    • Proposed as answer by RobVVV Friday, January 2, 2015 3:49 PM
    • Unproposed as answer by RobVVV Friday, January 2, 2015 3:52 PM
    • Proposed as answer by RobVVV Friday, January 2, 2015 3:52 PM
    Thursday, January 1, 2015 4:36 PM
  • Hi, John. Thanks for the response. I'm not sure which is worse: my working on New Year's Eve or our working on New Year's Day? We should be watching football! But, I keep thinking about the puzzle. Maybe, it's a data problem with the schedules I'm using. Any chance I could send you one so that you could have a look (after football, of course)?

    Thursday, January 1, 2015 6:12 PM
  • Rob,

    I'm not a big football fan so that's not what I'm about today. As long as your file isn't larger than 1M when zipped, go ahead and send it. Not sure when I'll get a chance to look at it, but it shouldn't be more than a couple of days.

    John

    Thursday, January 1, 2015 8:09 PM
  • Thanks, John. I did a line-by-line comparison between your code and mine. I found one small difference: I had declared the PeriodicBCWP array as Integer, and you had let it default. When I changed my code to Single, it worked.

    I'm not sure why the change in declaration made such a difference, but it solved the problem.

    Thanks, again, for your help.

    Friday, January 2, 2015 4:01 PM
  • Rob,

    You're welcome and thanks for the feedback.

    There were more differences than just the type declaration for the PeriodicBCWP array but then I didn't have your full macro code so I had to fill in some holes to make it work properly.

    With regard to declaring a variable as a type integer, that should normally be reserved for indices (i.e. i, j, k, etc) or for whole number variables that you know will be small, (i.e. less than 32,767). In the case of earned values metrics, those can easily be greater than $32K. What I suspect happened when you ran you code initially is that the first few values of BCWP were less than 32K so it worked fine, but as soon as it hit the limit for an integer, it couldn't go any higher, so the array no longer was populated. I would have thought an overflow error message would have popped up but then it's been a while since I ran into that type of problem, so I really don't remember what does happen.

    At any rate, you got it fixed, so all is well with the world.

    John

    Saturday, January 3, 2015 2:05 AM