Project Standard 2013 and Project Professional 2013 include many new features for developers, such as new reports, task paths, cache status, and working with SharePoint tasks lists. This article shows VBA examples for new features in Project. You can use the new classes and members in the Project object model with VBA or with Visual Studio 2012 and Tools for Office (VSTO).

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, Word, and Excel. Word, Excel, and Outlook also support content apps and mail apps. For information about apps for Office 2013, see Apps for Office and SharePoint and Task pane apps for Project in the MSDN online library. Additionally, you can follow the Apps for Office and SharePoint developer blog to keep up on the latest information around apps for Office and SharePoint.

Task pane apps and other apps for Office and SharePoint can be sold in the Office Store (see http://officepreview.microsoft.com/store/) for use with both Project Online and on-premises installations. VBA macros and VSTO add-ins cannot be distributed in the Office Store; they are designed for local use with Project Standard and Project Professional. You can distribute VBA macros within a project .MPP file, install them in the Global.MPT file on your machine, or distribute them in the enterprise global template in Project Server 2013. VSTO add-ins can be distributed more securely through ClickOnce deployment, which enables easy updates.

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 ) for VSTO add-ins since Project 2003. 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 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.

New reports

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 constants. Figure 1 shows the result.

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. Creating a custom report that contains a table

Creating a chart on a report is just as easy. The following code example is copied from the Chart Object 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

pj15_VBA_ChartObject_735

For the built-in reports, you can manually change items on the reports, but you cannot programmatically change the existing items. However, 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 color and position of the title (in "TextBox 1", 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 collection of Shapes that is in the report; shape types are specified in the MsoShapeType enumeration.

Figure 3. Copying and modifying a built-in report

pj15_VBA_CopyReport_735

Task Path formatting

With the Task Path feature in Project 2013, 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 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

pj15.VBA_TaskPathDrivingPredecessor_735

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

Waiting for a cache job to complete

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. 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 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 machine. 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 macro periodically checks the job state by calling GetCacheStatusForProject and prints the job status to the Immediate window. If it finds the same status more than ten times in succession, the WaitForJob macro 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

Working with SharePoint tasks lists

Project Server 2013 and Project Online can import 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. SynchronizeWithSite can still synchronize a local project with a SharePoint tasks list.
  • LinkToTaskList links a new project with a SharePoint task 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. LinkToTaskList also creates a .MPP file in the Site Assets list. 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 machine 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 next update of 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 task list.

Note   After you run the AddDurationColumns macro, you must save the project in Project Professional to synchronize your changes with the SharePoint task 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 task 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 task list with the two new fields.

Figure 5. Adding fields to a synchronized SharePoint task list

pj15_VBA_AddSiteColumn_735

Conclusion

The Project object model can be used with VBA or with VSTO. The Project object model includes seven new classes, 292 new members, and a slew of 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 PWA.
  • 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.

VBA-based and VSTO-based development can customize and integrate Project with a wider range of solutions than ever before possible.

Additional Resources

For videos and training for Project, see the following:

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

The Project VBA Help topics are located in the MSDN online library. Many of the topics for new members are incomplete in the Project 2013 Preview SDK; the topics will be completed for the RTM publication of the Project 2013 SDK. See Welcome to the Project 2013 VBA developer reference.

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.