none
Project 2010 - VBA code to calculate the Cumulative Baseline Cost RRS feed

  • Question

  • First time on this Project Programming site as I am not really a programmer but wanted some help with creating a custom field.

    Has anyone written some VBA code I could use to calculate the Cumulative Baseline Cost in Project 2010 ? Or will this available in 2013 ?

    Many thanks,

    Mick, Bristol, England


    Mick Delany

    Tuesday, December 4, 2012 4:28 PM

Answers

All replies

  • The timescaleData VBA module of my book is at: http://msdn.microsoft.com/en-us/library/office/ee355231(v=office.12).aspx

    or try:

    Sub CuCost()
    'Calculates cumulative baselinecost to status date for select task
    Dim BaselineCost As Currency
    Dim Tsk As Task
    Dim tsvs As TimeScaleValues
    Dim tsv As TimeScaleValue
    Dim Dt As Date
        Set Tsk = ActiveProject.ProjectSummaryTask
        If ActiveProject.StatusDate = "NA" Then
            Dt = Date
        Else
            Dt = ActiveProject.StatusDate
        End If
        
        If Tsk.BaselineStart > Dt Then
            BaselineCost = 0
        Else
            Set tsvs = Tsk.TimeScaleData(Tsk.Start, Dt, pjTaskTimescaledBaselineCost, pjTimescaleWeeks)
            BaselineCost = 0
            For Each tsv In tsvs
                BaselineCost = BaselineCost + Val(tsv.Value)
            Next tsv
        End If
        Debug.Print "Cumulative BaselineCost is: " & BaselineCost
    End Sub
    


    Rod Gill

    The one and only Project VBA Book

    Rod Gill Project Management

    Tuesday, December 4, 2012 8:47 PM
    Moderator
  • Many thanks Rod,

    Not sure I understand your answer - apologies.

    I am trying to show the original Baseline Cumulative Cost forecast againts the current Cumulative Cost forecast for the entire project duration. Does your code above only show this up until the project status date or am I missing a trick here ?

    I can see from the Visual Reports in 2010 that the Cumulative Cost forecast values are available in the OLAP cube but not for any of the Baseline Costs including Baseline Costs 1 to 10 etc.

    If your code above will calculate the timephased Baseline Cumulative Costs - please can you tell me where I can insert this code into Project Custom Fields / Visual Reports ?

    Cheers,

    Mick, Bristol, England


    Mick Delany

    Wednesday, December 5, 2012 4:25 PM
  • Yes, its to status date, but easy to swap this to .Finish instead.

    This code would need to be part of a VBA solution that exports all data directly to Excel.


    Rod Gill

    The one and only Project VBA Book

    Rod Gill Project Management

    • Marked as answer by mick.delany Friday, December 7, 2012 2:39 PM
    Wednesday, December 5, 2012 8:16 PM
    Moderator
  • Thanks Rod,

    Maybe it's time to consider purchasing your VBA book (just hope I am up to it !)

    Would your book also help me write the code to extract data for Excel pivot table reporting ? Along with many other MSP users, I too am giving up on the pre-built Visual Reports feature which is not working for me - see link below to a recent thread for more info ?

    http://social.technet.microsoft.com/Forums/en-US/projectprofessional2010general/thread/f4f5c1ca-4364-4387-9356-b9ada9ab3755

    Cheers,

    Mick


    Mick Delany

    Friday, December 7, 2012 2:44 PM