What's new for VBA developers in Project 2013
Published: September 26, 2012
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.
Applies to: Project Standard 2013 | Project Professional 2013
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 2012 (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.
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).
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.
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.
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.
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:
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.
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.
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:
Module 9, Project Professional 2013 training for developers, on the Office 2013 training for developers page
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.