none
Timescaled values in VBA - Project 2010 RRS feed

  • Question

  • This snippet of code adopted from Bob Gill's book works fine - as ICtr iterates in a loop Tsvs accumulates the timescaled values for task ICtr.

     Set Tsvs = Proj.Tasks(ICtr).TimeScaleData( _
                               Date1, Date2, _
                               Type:=pjTaskTimescaledCost, _
                               Timescaleunit:=pjTimescaleMonths, Count:=1)

    However - if I try to obtain TimescaledBaselineCost I get nothing. In the code window the Type:= assumes the value 6? and Tsvs returns as empty.

    Thanks for any help

    Norm

    Tuesday, October 18, 2011 3:44 PM

Answers

  • John,

    I have developed flat-head syndrome from hitting my forehead with my palm!

    I created a simple project to illustrate the problem, which showed there was no problem with the macro! So, I went back to the project file I had been given, which showed that the baseline had been saved. This time, I repeated the save baseline operation and ran the macro unaltered - and the problem disappeared.

    Answer - the baseline in the file I had been given had been saved before the costs were assigned! My face is red and I apologize for taking up your time and the time of others who offered help.

    Norm

    Friday, November 18, 2011 8:57 PM
  • Norm,

    Actually it's Rod Gill, not Bob Gill.

    Yes I can confirm a failure with the timescale value for the BaselineCost field in Project 2010 (it works fine in Project 2007). I remember Rod brought up an issue with the timescale values for CumulativePercentComplete several months ago and I believe that was supposed to be fixed with the SP1 update to Project 2010. Whether or not the BaselineCost issue was also fixed I do not know because I haven't installed the update myself. If you have not installed SP1 and cumulative updates (I believe the latest is August), please do so.

    John

    • Marked as answer by GoldenNorm Wednesday, October 19, 2011 2:15 AM
    • Unmarked as answer by GoldenNorm Wednesday, October 19, 2011 2:15 AM
    • Marked as answer by GoldenNorm Wednesday, October 19, 2011 2:16 AM
    Tuesday, October 18, 2011 10:03 PM

All replies

  • This code does work:

    Set Tsvs = Proj.Tasks(ICtr).TimeScaleData( _
                               Date1, Date2, _
                               Type:=pjTaskTimescaledBaseline1Cost, _
                               Timescaleunit:=pjTimescaleMonths, Count:=1)

    and, I can substitute the constant 484 for the Type:=pcjTaskBaseline1Cost, or 5 for pjTimescaledCost statement, but 6, the value for BaselineCost fails. Seems the flaw is internal to VBA? For the time being I can work with Baseline1.

    Norm

    Tuesday, October 18, 2011 5:38 PM
  • Norm,

    Actually it's Rod Gill, not Bob Gill.

    Yes I can confirm a failure with the timescale value for the BaselineCost field in Project 2010 (it works fine in Project 2007). I remember Rod brought up an issue with the timescale values for CumulativePercentComplete several months ago and I believe that was supposed to be fixed with the SP1 update to Project 2010. Whether or not the BaselineCost issue was also fixed I do not know because I haven't installed the update myself. If you have not installed SP1 and cumulative updates (I believe the latest is August), please do so.

    John

    • Marked as answer by GoldenNorm Wednesday, October 19, 2011 2:15 AM
    • Unmarked as answer by GoldenNorm Wednesday, October 19, 2011 2:15 AM
    • Marked as answer by GoldenNorm Wednesday, October 19, 2011 2:16 AM
    Tuesday, October 18, 2011 10:03 PM
  • John,

    Thank you for the answer. I'll get my IT group to install the update to see if the problem is solved.

    My apologies to Rod - he's still the source of most of my knowledge.

    Norm

    Wednesday, October 19, 2011 2:19 AM
  • Norm,

    You're welcome and thanks for the feedback.

    John

    Wednesday, October 19, 2011 2:27 AM
  • John,

    One last piece of information. SP1 was already installed, so failure has not (yet) been fixed by MS.

    Norm

    Wednesday, October 19, 2011 2:51 PM
  • Norm,

    I've contacted Rod and asked if he could lend his insight.

    John

    Thursday, October 20, 2011 2:32 AM
  • Norm,

    Okay, now that my head is screwed on straight I have to own up to an error (and discovery) on my part.

    First, there is nothing wrong with the timescale values for the BaselineCost field. When I did the test that I thought confirmed your issue I made an error in the syntax of the timescaled data Method. This is what I used:

    With ActiveProject.Tasks(1)

            Set tsvsBaselineCost = .TimeScaleData(.Start, .Finish, pjTaskBaselineCost, pjTimescaleWeeks, 1)

    End With

    And of course that fails because the constant for the timescale data is incorrect. However, if I correct the error as follows it works fine:

    With ActiveProject.Tasks(1)

            Set tsvsBaselineCost = .TimeScaleData(.Start, .Finish, pjTaskTimescaledBaselineCost, pjTimescaleWeeks, 1)

    End With

    I can even substitute the constant numeric value (i.e. "6") and it still works fine, however for ease of debugging I always use the alpha form of constants unless I include a comment statement near the code line. I don't know about you but when I write a macro and then don't look at it for months or even years, the more descriptive code format helps at lot when I have to get back up to speed.

    However, I digress slightly. Here is an interesting little tidbit that helped lead me astray in declaring that I confirmed your issue. For whatever reason VBA does have an interesting "glitch". If you use any other baseline cost field (although I only tied the Baseline1Cost field), the correct constant for the timescale value constant is apparently not required. For example, the following DOES work and does give the correct values:

    With ActiveProject.Tasks(1)

            Set tsvsBaselineCost = .TimeScaleData(.Start, .Finish, pjTaskBaseline1Cost, pjTimescaleWeeks, 1)

    End With

    However, if you use the numeric (i.e. 188744164) value for the constant, it does NOT work.

    So the bottom line. My guess is you made the same syntax entry error that I did, tried to confirm it by using another baseline cost field and were led to the conclusion, (as I unfortunately was), that VBA had an error. Check you syntax and see it that helps.

    I have to give credit to Rod for helping point out my error.

    John

     

    Thursday, October 20, 2011 3:54 PM
  • John,

    I greatly appreciate the work you've put into trying to solve my local problem. I was able to satisfy the needs of the person who asked for the solution by simply using any one of the numbered baselines. That's the reason I did not come back to the Forum to read your most recent response.

    If you go back to my response of October 18 at 5:38pm, I noted that substituting the number "6" did not work for me. Using "6" should have been equivalent of correctly typing pjTaskTimescaledBaselineCost. Despite my laziness in typing within the Forum, I had been using the proper constant of pjTaskTimescaledBaselineCost, (I was simply removing the trailing "1' in my example.) It failed, and still does.

    Here is the actual code  I'm using BaselineType as Integer, Date1 and Date2 dimensioned as Date, ICtr as Long. I've stripped out some code that outputs the the results to Excel - another piece of code that originated with Rod. BaselineType is set by user input using a Select Case construct. This code works with all valid BaselineType constants, with the significant exception of 6 and/or hard-coding pjTaskTimescaledBaselineCost. I'm mystified.

    I don't think it's worth any further effort on your part (beyond looking at what I've typed and confirming that I've not made some other brainless typing error.)

     Tcount = Proj.Tasks.Count
            For ICtr = 1 To Tcount
                If Not (Proj.Tasks(ICtr).Summary) = True Then
                               Set Tsvs = Proj.Tasks(ICtr).TimeScaleData( _
                               Date1, Date2, _
                               BaselineType, _
                               Timescaleunit:=pjTimescaleWeeks, Count:=2)
                End If
            Next

    Thank you,

    Norm

    Wednesday, November 16, 2011 6:56 PM
  • Im not sure why pjTaskTimescaledBaselineCost is not working for you. The code below works on my machine.

    Sub testingTSV()
    Dim I As Integer
    Dim T As Task
    Dim TSV As TimeScaleValues
    
    For Each T In ActiveProject.Tasks
        If Not (T Is Nothing) Then
            Set TSV = T.TimeScaleData(T.Start, T.Finish, pjTaskTimescaledBaselineCost, pjTimescaleMonths)
            For I = 1 To TSV.Count
                Debug.Print "Start: " & TSV(I).StartDate & " | " & TSV(I).Value
            Next I
        End If
    Next T
    End Sub
    

     

     

     

     


    Brian Kennemer – DeltaBahn Senior Architect
    endlessly obsessing about Project Server…so that you don’t have to.
    Blog | Twitter | LinkedIn
    Wednesday, November 16, 2011 8:23 PM
    Moderator
  • Norm,

    Tell you what. For you and as a special today only, can you put together a sample project file with a sample macro that doesn't work and send it to me at the address below? Once I got my head on straight, tried it myself, consulted with Rod, and now see that Brian has also tried it, there must be something you are missing that we just don't see. Rather than spending more time trying to figure out what's wrong, it will be a whole lot easier to see the problem as you see it.

    John

    jensenljatatfastmaildotdotfm

    (remove obvious redundancies and note: the seventh character is an alpha)

    Wednesday, November 16, 2011 8:51 PM
  • John,

    I have developed flat-head syndrome from hitting my forehead with my palm!

    I created a simple project to illustrate the problem, which showed there was no problem with the macro! So, I went back to the project file I had been given, which showed that the baseline had been saved. This time, I repeated the save baseline operation and ran the macro unaltered - and the problem disappeared.

    Answer - the baseline in the file I had been given had been saved before the costs were assigned! My face is red and I apologize for taking up your time and the time of others who offered help.

    Norm

    Friday, November 18, 2011 8:57 PM
  • No problem. it is easy to get wrapped up in an issue like this. By coincidence I am working on some code that uses TimeScaledValues and I had a similar issue and only found the problem once I was writing up the email to send to someone I know.

    My issue was that the way timescaled cost values (and likely others but mine was cost) are saved for nonworking days are as "" not as 0. So if you save a baseline it will not save $0 into Saturday. It saves it as a "". Not NULL but not a number either.

    I was trying to do some rollups and I needed to put some values into a nonworking day. X = X + Y works fine if X is currently a numeric value but if it is a "" then X=X+Y returns a TypeMismatch error!

    I spent an embarassing amount of time thinking about it before I looked at a usage view closely and saw that there were not numbers in the field for the nonowrking days.

    ;-) 


    Brian Kennemer – DeltaBahn Senior Architect
    endlessly obsessing about Project Server…so that you don’t have to.
    Blog | Twitter | LinkedIn
    Friday, November 18, 2011 9:05 PM
    Moderator