Export (0) Print
Expand All

What's new for VBA developers in Project 2013

Office 2013

Project Standard 2013 and Project Professional 2013 include many new features for developers, such as new reports with Office Art, task path formatting, monitoring cache status, and working with SharePoint tasks lists. This article shows VBA examples for new features in Project.

Last modified: July 01, 2013

Applies to: Project Professional 2013 | Project Standard 2013

This article does not cover task pane apps, which have a JavaScript API and can be developed with HTML5. Task pane apps are another of the major new programmability features in the Office 2013 versions of Project and several other Office applications. For more information, see Task pane apps for Project.

Project has had VBA for decades—well, since Project 4.0 in 1994—and has had a primary interop assembly (Microsoft.Office.Interop.MSProject.dll ) to use with Office Developer Tools for Visual Studio 2013 (VSTO) add-ins since Project 2003. You can use the new classes and members in the Project object model with VBA or with VSTO. The PIA includes essentially the same classes, properties, methods, and events that VBA exposes. You can use the VBA object model to create test macros, and then translate them to a VSTO add-in that uses Visual C# or Visual Basic. VSTO add-ins are much more flexible, robust, secure, and manageable than VBA macros.

Project 2013 extends the object model to include Office Art, new reports, and new members of the Application, Project, and Task objects that reflect new or improved features. For VBA Help topics, see Project 2013 VBA developer reference.

Project 2013 implements most of the Office Art infrastructure that is also used in Word, Excel, and PowerPoint. Project also adds flexible new reports that are directly programmable and use Office Art objects such as Chart, Shape, and ShapeRange. Project adds the Report object and a ReportTable shape type, and enables charts and tables to dynamically use task and resource fields in the active project through the Field List task pane.

Project 2013 includes over 20 new built-in reports such as Project Overview, Task Cost Overview, and Best Practice Analyzer. Each of the new reports contains chart, table, and Office Art shapes. You can create custom reports manually using commands on the REPORT tab of the ribbon, or programmatically with VBA or VSTO.

The following code creates a custom report that contains one Shape object, which is a ReportTable. The number of elements in the SafeArrayOfPjField parameter of the UpdateTableData method specifies the number of columns in the table; fields can be chosen from the 1,338 PjField Enumeration (Project) constants.

Sub TestReportTable()
    Dim theReport As Report 
    Dim tableShape As Shape 
    Dim theReportTable As ReportTable 
    Dim reportName As String
    Dim tableName As String
    Dim rows As Integer
    Dim columns As Integer
    Dim left As Integer
    Dim top As Integer
    Dim width As Integer
    Dim height As Integer
 
    reportName = "Table Report" 
    Set theReport = ActiveProject.Reports.Add(reportName) 

    ' Add the table.
    tableName = "Task information" 
    rows = 0 
    columns = 0 
    left = 0 
    top = 30 
    width = 110 
    height = 20 

    ' Project ignores the NumRows and NumColumns parameters when creating a ReportTable. 
    tableShape = theReport.Shapes.AddTable(rows, columns, _
        left, top, width, height) 
    tableShape.Name = tableName 
    tableShape.Select() 
    Set theReportTable = tableShape.Table 

    ' Set fields for the table. 
    Dim fieldArray(1 To 6) As PjField 
    fieldArray(1) = pjTaskName 
    fieldArray(2) = pjTaskStart 
    fieldArray(3) = pjTaskFinish 
    fieldArray(4) = pjTaskPercentComplete 
    fieldArray(5) = pjTaskActualCost 
    fieldArray(6) = pjTaskRemainingCost 

    theReportTable.UpdateTableData(Task:=True, OutlineLevel:=1, _
        SafeArrayOfPjField:=fieldArray)
 End Sub

Figure 1 shows the resulting report when the project contains four tasks, where assigned resources have entered some work performed on the tasks T1, T2, and T3.

Figure 1. Creating a custom report that contains a table

Updating a table with the UpdateTableData method

Creating a chart on a report is just as easy. The following code example is copied from the Chart Object (Project) topic in VBA Help, which creates a default chart and positions the chart title (see Figure 2).

Sub AddSimpleScalarChart() 
    Dim chartReport As Report 
    Dim reportName As String 
    
    ' Add a report. 
    reportName = "Simple scalar chart" 
    Set chartReport = ActiveProject.Reports.Add(reportName) 

    ' Add a chart. 
    Dim chartShape As Shape 

    Set chartShape = ActiveProject.Reports(reportName).Shapes.AddChart() 
    chartShape.Chart.SetElement(msoElementChartTitleCenteredOverlay) 
    chartShape.Chart.ChartTitle.Text = "Sample Chart for the Test1 project" 
End Sub

When you select the Chart object on the report, you can see the Field List task pane, and manually change the fields, filter, grouping, and sorting (or programmatically change them using the UpdateChartData method).

Figure 2. Creating a default chart on a report

Simple scalar chart in a report

For the built-in reports, you can manually change items on the reports, but you cannot programmatically change the existing items. But, you can copy any of the new reports with the Application.CopyReport method, create a custom report and paste the copied report to it, and then modify any of the elements. For example, the following code copies the Task Cost Overview report and changes the report title (see Figure 3).

Sub CopyCostReport() Dim reportName As String 
    Dim newReportName As String 
    Dim newReportTitle As String 
    Dim myNewReport As Report 
    Dim oShape As Shape 
    Dim msg As String 
    Dim msgBoxTitle As String 
    Dim numShapes As Integer 

    reportName = "Task Cost Overview" ' The built-in report. 
    
    newReportName = "Task Cost Copy" 
    msg = "" 
    numShapes = 0 

    If ActiveProject.Reports.IsPresent(reportName) Then 
        ApplyReport(reportName)    ' Display the report. 
        CopyReport() 
        myNewReport = ActiveProject.Reports.Add(newReportName) 
        PasteSourceFormatting()    ' Paste the copy to the new report. 

        ' List the shapes in the copied report. 
        For Each oShape In myNewReport.Shapes 
            numShapes = numShapes + 1 
            msg = msg & numShapes & ". Shape type: " & CStr(oShape.Type) _
                 & ", '" & oShape.Name & "'" & vbCrLf 

            ' Modify the report title. 
            If oShape.Name = "TextBox 1" Then 
                newReportTitle = "My " & oShape.TextFrame2.TextRange.Text 

                With oShape.TextFrame2.TextRange 
                    .Text = newReportTitle 
                    .Characters.Font.Fill.ForeColor.RGB = &H60FF10  ' Bluish green. 
                End With 

                oShape.Reflection.Type = msoReflectionType2 
                oShape.IncrementTop(-10)    ' Move title 10 points up. 
                oShape.Select() 
            End If 
        Next oShape 

        msgBoxTitle = "Shapes in report: '" & myNewReport.Name & "'" 

        If numShapes > 0 Then 
            MsgBox(Prompt:=msg, Title:=msgBoxTitle) 
        Else 
            MsgBox(Prompt:="This report contains no shapes.", _
                Title:=msgBoxTitle) 
        End If 
    Else 
        MsgBox(Prompt:="No custom report name: " & reportName, _
            Title:="ApplyReport error", Buttons:=vbExclamation) 
    End If 
End Sub

The changes the previous code makes in the report are the text, color, and position of the title (in the TextBox 1 shape, which is selected in Figure 3), and adding a reflection effect. You can modify any of the shapes, including table and chart fields, and add the wide variety of Office Art shapes and fancy effects. The macro also displays the name and type of each shape in the Shapes collection that is in the report; shape types are specified in the MsoShapeType Enumeration (Office) enumeration.

Figure 3. Copying and modifying a built-in report

Copying and modifying a built-in report
NoteNote

Instead of using the CopyReport method, you can also use the RenameReport method, which copies a built-in report to a new custom report.

The Task Path feature in Project 2013 helps to show how predecessor and successor tasks are affected by scheduling changes. You can dynamically show task predecessors, driving predecessors, successors, and driven successors by colors of tasks in the Gantt chart.

In Figure 4, the Driving Predecessors and Driven Successors items are selected in the Task Path drop-down list. When you select task T3, the Gantt chart shows that T1 is a driving predecessor task and T4 is a driven successor task. That is, T1 is a predecessor task, where the finish date drives the scheduled start date of T3; and T4 is a successor task of the selected task T3, where the T4 start date is driven by the scheduling of T3.

Figure 4. Using the task path properties to highlight tasks

Using the task path properties to highlight tasks

You can manually select a task or use VBA to select a task, and then use VBA or VSTO to check how another task is related to the selected task. For example, if you are in the Gantt chart view of the project shown in Figure 4, and then run the following statements in the Immediate window of the VBE, the PathDrivenSuccessor statement prints True.

Application.SelectRow Row:=3, RowRelative:=False 
Application.HighlightDrivenSuccessors(True) 
? ActiveProject.Tasks(4).PathDrivenSuccessor

Project Server 2007 and Project Server 2010 can determine when a server-side queue job is completed or has a problem, by using Project Server Interface (PSI) methods (for example, see How to: Use the QueueSystem Service). Project Server 2013 and Project Online extend that capability with the client-side object model (CSOM—not to be confused with the client object model for VBA and VSTO in Project Professional and Project Standard).

With Project Professional 2013, you can programmatically check the Active Cache status when you save, publish, or check in a project to Project Web App. The Active Cache for Project Professional runs on your local computer. The GetCacheStatusForProject property exposes the status of the queue job.

The following code example saves the active project, calls WaitForJob to wait for the queue job to finish successfully, and then publishes the project. The WaitForJob function periodically checks the job state by calling GetCacheStatusForProject and prints the job status to the Immediate window of the VBE. If it finds the same status more than ten times in succession, the WaitForJob function assumes there is a problem and exits. The example uses a Sleep method that can be run in either a 64-bit or 32-bit Project installation.

Option Explicit 

#If Win64 Then 
    Private Declare PtrSafe Sub Sleep Lib "kernel32" _
        (ByVal dwMilliseconds As LongLong) 
#Else 
    Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long) 
#End If 

' Save and publish the active project; wait for the queue after each operation. 
Sub TestCacheStatus() 
    ' Number of milliseconds to sleep between status messages. 
    Const millisec2Wait = 500 
    Application.FileSave()

    If WaitForJob(PjJobType.pjCacheProjectSave, millisec2Wait) Then 
        Debug.Print("Save completed ...") 
        Application.Publish() 
        
        If WaitForJob(PjJobType.pjCacheProjectPublish, millisec2Wait) Then 
            Debug.Print("Publish completed: " & ActiveProject.Name)
        End If 
    Else 
        Debug.Print("Save job not completed") 
    End If 
End Sub 

' Check the cache job state for a save, publish, or check-in operation. 
Function WaitForJob(job As PjJobType, msWait As Long) As Boolean 
    ' Number of times the same job status is repeated until 
    ' WaitForJob exits with an error. 
    Const repeatedLimit = 10 
    Dim jobState As Integer 
    Dim previousJobState As Integer 
    Dim bail As Integer 
    Dim jobType As String 

#If Win64 Then 
    Dim millisec As LongLong 
    millisec = CLngLng(msWait) 
#Else 
    Dim millisec As Long 
    millisec = msWait 
#End If 

    WaitForJob = True 

    Select Case job 
        Case PjJobType.pjCacheProjectSave jobType = "Save" 
        Case PjJobType.pjCacheProjectPublish jobType = "Publish" 
        Case PjJobType.pjCacheProjectCheckin jobType = "Checkin" 
        Case Else jobType = "unknown" 
    End Select 

    bail = 0 

    If (jobType = "unknown") Then 
        WaitForJob = False 
    Else 
        Do 
            jobState = Application.GetCacheStatusForProject(ActiveProject.Name, job) 
            Debug.Print(jobType & " job state: " & jobState) 

            ' Bail out if something is wrong. 
            If jobState = previousJobState Then 
                bail = bail + 1     
                If bail > repeatedLimit Then 
                    WaitForJob = False 
                    Exit Do 
            End If 
        
            previousJobState = jobState Sleep(msWait) 
        Loop While Not (jobState = PjCacheJobState.pjCacheJobStateSuccess) 
    End If 
End Function

Following is an example of output in the Immediate window. For the meaning of the output values, see the PjCacheJobState enumeration in the VBA Object Browser.

Save job state: 4 Save completed 
... 
Publish job state: -1 
Publish job state: 3 
Publish job state: 3 
Publish job state: 4 
Publish completed: WinProj test 1

Project Server 2013 and Project Online can import a SharePoint tasks list as a project where SharePoint maintains control, or can import a tasks list as a project where Project Server has full control of scheduling and other project management features. You can use Project Professional 2013 to open and update either type of project. When you create a local project in Project Professional 2013, you can save the project to a new SharePoint tasks list—or link the new project with an existing tasks list.

The Project Professional 2013 object model includes new and updated members of the Application object to help work with SharePoint tasks lists:

  • SynchronizeWithSite is updated to work with SharePoint sites through Project Web App, where projects can be in either the Project Server full control mode or the SharePoint management mode. The SynchronizeWithSite method can still synchronize a local project with a SharePoint tasks list, as it did in Project Professional 2010.

  • LinkToTaskList links a new project with a SharePoint tasks list. The method first verifies that the specified SharePoint site and tasks list exists, and then synchronizes the active project with the site by adding manually scheduled tasks and any assigned resources. The LinkToTaskList method also creates an .MPP file in the Site Assets list in SharePoint. The project file can contain VBA macros. For example, if the original tasks list in http://MySite is named Test Tasks List, use the following command:

    LinkToTaskList SiteURL:="http://MySite", TaskListName:="Test Tasks List"
    

    After you use the LinkToTaskList method, the site contains the .MPP file at the following URL: http://MySite/SiteAssets/Content%20site-Test%20Tasks%20List.mpp. If Project is installed on a computer running Windows Server, you can use Server Manager to install the Desktop Experience feature; otherwise, the .MPP file cannot be created.

  • AddSiteColumn adds a column to a SharePoint tasks list for a project site, and optionally specifies the column name. The column can be one of the task PjField constants, such as pjTaskBaselineDurationText, where the column does not already exist in the SharePoint tasks list. The field cannot be an enterprise custom field or lookup table or a non-task field such as pjResourceActualCost. The VBA Help topic for the AddSiteColumn method will include a list of prohibited fields.

For example, create a tasks list in a SharePoint site, create a project in Project Professional, and then use the LinkToTaskList method to import the task list. Set a baseline for the project (use the Set Baseline command on the PROJECT tab of the ribbon), and then change the duration of some tasks.

The following code adds task duration and baseline duration to the list of available columns in the SharePoint tasks list.

Note Note

After you run the AddDurationColumns macro, you must save the project in Project Professional to synchronize your changes with the SharePoint tasks list.

Sub AddDurationColumns() Dim success As Boolean 
    Dim columnName As String 
    Dim fieldName As PjField 
    Dim results As String 

    results = "" 
    fieldName = pjTaskBaselineDurationText 
    columnName = "Baseline duration" 
 
    ' If the field name exists in the SharePoint tasks list, or 
    ' fieldName is one of the prohibited fields, the AddSiteColumn 
    ' method returns error 1100. 
    On Error Resume Next 

    success = AddSiteColumn(fieldName, columnName) 

    If success Then 
        results = "Added site column: " & columnName 
    Else 
        results = "Error in AddSiteColumn: " & columnName 
    End If 

    fieldName = pjTaskDurationText 
    columnName = "Current duration" 
    success = AddSiteColumn(fieldName, columnName) 

    If success Then 
        results = results & vbCrLf & "Added site column: " & columnName 
    Else 
        results = results & vbCrLf & "Error in AddSiteColumn: " & columnName 
    End If 
    
    Debug.Print(results)
End Sub

After you save the project, go to the tasks list in SharePoint. On the LIST tab, select the Modify View command. On the Settings – Edit View page, select the Baseline duration field and the Current duration field that the macro added. Figure 5 shows the tasks list with the two new fields.

Figure 5. Adding fields to a synchronized SharePoint task list

Adding fields to a synchronized tasks list

The Project object model can be used with VBA or with VSTO. The Project object model includes seven new classes, 292 new members, and many new enumeration constants that support many new features in Project Standard 2013 and Project Professional 2013, including:

  • Create new reports that can have tables and charts with task and resource fields, can include Office Art features, and can be both manually and programmatically modified.

  • Manipulate the Task Path properties to dynamically show how predecessor tasks affect scheduling of a selected task, and how the selected task affects scheduling of successor tasks.

  • Monitor the Active Cache to show the status of saving, publishing, and checking in a project to Project Web App.

  • Work with SharePoint tasks lists in four different ways, to help realize the goal of managing and visualizing all of your work in one place.

For videos and training for Project, see the following:

The Project team periodically adds new articles to the Project Blog about new features in Project and Project Online. For example, see the following:

For links to the Project 2013 SDK and many other resources, see the Project for developers page on MSDN at http://msdn.microsoft.com/project.

Show:
© 2014 Microsoft