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).
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.
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.
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
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, _
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).
Dim chartReport As Report
Dim reportName As String
' Add a report.
reportName = "Simple scalar chart"
Set chartReport = ActiveProject.Reports.Add(reportName)
' Add a chart.
Set chartShape = ActiveProject.Reports(reportName).Shapes.AddChart()
chartShape.Chart.ChartTitle.Text = "Sample Chart for the Test1 project"
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
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).
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
.Text = newReportTitle
.Characters.Font.Fill.ForeColor.RGB = &H60FF10 ' Bluish green.
oShape.Reflection.Type = msoReflectionType2
oShape.IncrementTop(-10) ' Move title 10 points up.
msgBoxTitle = "Shapes in report: '" & myNewReport.Name & "'"
If numShapes > 0 Then
MsgBox(Prompt:="This report contains no shapes.", _
MsgBox(Prompt:="No custom report name: " & reportName, _
Title:="ApplyReport error", Buttons:=vbExclamation)
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
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
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.
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.
Private Declare PtrSafe Sub Sleep Lib "kernel32" _
(ByVal dwMilliseconds As LongLong)
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.
' Number of milliseconds to sleep between status messages.
Const millisec2Wait = 500
If WaitForJob(PjJobType.pjCacheProjectSave, millisec2Wait) Then
Debug.Print("Save completed ...")
If WaitForJob(PjJobType.pjCacheProjectPublish, millisec2Wait) Then
Debug.Print("Publish completed: " & ActiveProject.Name)
Debug.Print("Save job not completed")
' 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)
Dim millisec As Long
millisec = msWait
WaitForJob = True
Select Case job
Case PjJobType.pjCacheProjectSave jobType = "Save"
Case PjJobType.pjCacheProjectPublish jobType = "Publish"
Case PjJobType.pjCacheProjectCheckin jobType = "Checkin"
Case Else jobType = "unknown"
bail = 0
If (jobType = "unknown") Then
WaitForJob = False
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
previousJobState = jobState Sleep(msWait)
Loop While Not (jobState = PjCacheJobState.pjCacheJobStateSuccess)
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.
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.
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
results = "Error in AddSiteColumn: " & columnName
fieldName = pjTaskDurationText
columnName = "Current duration"
success = AddSiteColumn(fieldName, columnName)
If success Then
results = results & vbCrLf & "Added site column: " & columnName
results = results & vbCrLf & "Error in AddSiteColumn: " & columnName
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
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.
For videos and training for Project, see the following:
- Module 9, Project Professional 2013 training for developers, on the Office 2013 training for developers page
- Register for Project Ignite
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.