Ms Project Tutorial - Project VBA : Looping All Tasks


Microsoft Project has powerful VBA capabilities. 


In this tutorial we will write VBA to loop through all the tasks inside the project file, add up the durations of all the tasks and store it into custom field "Number10".




Step 1: Open Visual Basic Editor


1. In Ms. Project, open your project file. For my case, Exercise2.mpp.


2. Press Alt + F11 to open Visual Basic Editor.



Step 2: Create Open Event of Project Object


As i need to auto-run the add up process every time the file is being open, i create Open Event of Project object.


1. In the Project Explorer, double-click ThisProject Module.


2. At the top left combobox, choose Project. At the top right combobox, choose Open.



Note: The editor will construct Open Event Procedure.


Step 3: Declare Variables


1. Inside the Open Event Procedure, write the following scripts to declare the variables.


'store current project file

Dim Proj As Project   

'store the current task during looping

Dim t As Task 

'store the sum value of durations

Dim MytotalDuration As Integer 


Step 4: Sum All Durations


1. Below the variable declaration script, write the following scripts.


'====sum all durations====

'initialize the variable to 0

MytotalDuration = 0

'assign the current project to Proj variable

Set Proj = ActiveProject

'loop all the tasks

For Each t In Proj.Tasks

      'ensure the tasks are not summary task   

      If t.Summary = False Then

            'add the task's duration to the variable

            'the  value of duration is in minutes, so remember to divide 480 to get day     

            MytotalDuration = MytotalDuration + t.Duration / 480   

      End If




Step 5: Store Value to Custom Field


1. Continue to add the following scripts.


You can store the variable to any custom field. For this case, i store into Number10.

'======store into custom field====

For Each t In Proj.Tasks   

     If t.Summary = False Then       

            t.Number10 = MytotalDuration   

     End If




Here is the complete code in Module Window.



Step 6: Display the Number10 custom field


1. Close and save the project file.


2. Reopen the project file to let it execute the script.


3. Display the number10 custom field in the table as shown below.


Notice that the sum of durations are stored into the custom field.



By Liang Ee Hang  | Google  | LinkedIn


Cempaka Technology Sdn Bhd

Pusat Latihan Komputer Cempaka
~Your HRDC Premiere Training Provider~
64-2, Jalan Puteri 2/2, Bandar Puteri Puchong, 47100 Puchong Selangor, Malaysia. Tel: 603-80684461
1-28, Jalan PM4, Plaza Mahkota, 75000 Melaka. Tel: 606-2835955
Instant SSL