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

Next

 

 

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

Next

 

 

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