Part of a series on customising Microsoft® Project® to make working with project schedules more useful.

This post describes how to trace predecessors and successors in MS-Project using a clever little VBA macro.

The Why

So you’ve created a useful programme schedule, and you are now using it to keep delivery on the right track.  You’ve made a bunch of edits based on conversations with PMs, and now your main delivery is showing significantly overdue. You’ve checked the edits you made, and they’re OK. So how do you find the cause of the delay?

You could do this manually. Say the main delivery milestone is TaskID 333. You could:

1. Filter the schedule by setting the successors field to show only those that have 333 as a successor.
2. Review all these tasks to see which one has moved and is causing the delay.
3. If you found it – great! Let’s say it was TaskID 222. If you can’t see why this one was delayed (e.g. it took longer than expected), work backwards up this chain applying steps 1 & 2 but focusing on TaskID 222 to see what is the root cause of the delay.
4. If you didn’t find it, start with the first predecessor (say TaskID 111) of the main milestone, and filter the schedule by setting the successors field to show only those that have 111 as a successor.
5. Work backwards up this chain to see what is the root cause of the delay.

This approach should get you to the root cause of the delay, but it is repetitive, laborious, involves a lot of working through long lists ticking things off, and is not a particularly rewarding way to spend far too much time.

So what should you do instead?

The How

A much better approach to trace predecessors and successors in MS-Project is to use a macro that does all this hard work for you in a few seconds. It produces a version of the schedule filtered to show all the tasks that are predecessors (or successors if you prefer) of any non-summary task that you select.

The macro to trace predecessors and successors in MS-Project was written by Jack Dahlgren and originally published on his (now defunct) site masamiki.com. It’s not there any more but I’m republishing it here with his permission as I find it so useful (thanks Jack!).

What to do

To use the macro, first make sure you aren’t using the custom field Flag 5 for anything you don’t want to lose (as the macro uses that field and will overwrite all the values in there). Then, in MS-Project 2016 select View → Macros → Visual Basic (or Alt+F11 if you prefer) to bring up the Visual Basic editor.

In the project browser window (on the left side; looks like a folder list), click on the object that looks like a folder, and is called “VBA Project (Name of your current file here)”, and then click on Modules  →  Module 1. This should bring up a code window on the right hand side, which may or may not have text in it.

If the window has text in it, click on Insert → Module to insert a new module. Once you have done that or if the window is initially empty, copy the text below and paste it into the code window.

' A little Macro which fans out predecessors, successors or both depending on the user input
' This macro works best if assigned to a button on your toolbar
' Uses Flag5 to store information - please be sure that this field is not currently in use
' Note: Does not work with Inserted/Consolidated projects as it does not handle external tasks
' Jack Dahlgren, Jan 11, 2001
' Do not redistribute without Author's Permission
' No guarantee of performance or suitability for any purpose
' Use only on files which have been backed up

' RELEASE HISTORY
' Version 1.2, Jan 11, 2001 - Added ability to show only driving/driven
' activities (FreeSlack = 0)
' Version 1.1, Jan 05, 2001 - Added ability to show critical items only,
' simplified ClearFlags
' Version 1.0, June 19, 2000 - Original version
' To Do: enable multiple traces by setting ClearFlags
' Work out trace through external tasks

Option Explicit
Dim Forward As Boolean
Dim SelectedID As Integer
Dim jString As String
Dim IsSum As Boolean
Dim IsCrit As Boolean
Dim IsDrive As Boolean
Dim jTask As Task

'This is the master macro
Sub Trace()
If ActiveSelection = 0 Then
MsgBox "You must have just one task selected for this macro to work"
Exit Sub
End If
If ActiveSelection.Tasks.Count <> 1 Then
MsgBox "You must have just one task selected for this macro to work"
Exit Sub
End If
'This sets flag used later for tracing paths.
jString = InputBox(("Please Enter Fan Type" & Chr(13) & Chr(13) & "P (Predecessors)" & Chr(13) & "S (Successors)" & Chr(13) & "A (All)"), "Fan-out Dependencies")
jString = UCase(Left(jString, 1))
If jString = "" Then
Exit Sub
End If
ClearFlags
IsCrit = False
For Each jTask In ActiveSelection.Tasks
If jTask.Summary = True Then
MsgBox "You have selected a summary task. Select a task or milestone and try again"
Exit Sub
End If
If jTask.Critical = True Then
If MsgBox("Do you want to display only Critical Tasks?", 260, "Display Critical Tasks Only?") = vbYes Then
IsCrit = True
End If
End If
Next jTask
'This sets the flag for 0 free float (driving) tasks
IsDrive = False
For Each jTask In ActiveSelection.Tasks
If IsCrit = False Then
If MsgBox("Do you want to display only Driving Tasks?", 260, "Display Driving Tasks Only?") = vbYes Then
IsDrive = True
End If
End If
Next jTask
Select Case jString
Case "P"
TracePredecessors
Case "S"
TraceSuccessors
Case Else
TraceAll
End Select
FilterMe
If SelectedID > 0 Then Find Field:="ID", Test:="equals", Value:=SelectedID, Next:=True
End Sub

' Set all tasks Flag5 to false
Private Sub ClearFlags()
Dim jTask As Task
For Each jTask In ActiveProject.Tasks
If Not (jTask Is Nothing) Then
If jTask.Flag5 = True Then jTask.Flag5 = False
End If
Next jTask
End Sub

'Traces Only Successor Tasks - forward equal to true
Private Sub TraceSuccessors()
SelectedID = 0
Forward = True
MarkItem
End Sub

'Traces Only Predecessor Tasks - forward equal to false
Private Sub TracePredecessors()
SelectedID = 0
Forward = False
MarkItem
End Sub

'Traces All Tasks - one pass for successors, then one for predecessors
Private Sub TraceAll()
SelectedID = 0
Forward = True ' mark successors
MarkItem
Forward = False ' mark predecessors
MarkItem
End Sub

'Marks all tasks feeding by selected task(s)
Private Sub MarkItem()
Dim jTask As Task, jjTask As Task
For Each jTask In ActiveSelection.Tasks
If Not (jTask Is Nothing) Then
SelectedID = jTask.ID
If Not (jjTask Is Nothing) Then
If Not Forward Then
Fan jjTask
Else
jjTask.Flag5 = True
End If
If Not (jjTask Is Nothing) Then
If Forward Then
Fan jjTask
Else
jjTask.Flag5 = True
End If
End If
Else
Fan jTask
End If
End If
Next jTask
End Sub

'Walks through all predecessors or successors to a task and marks their flag5 as true
Private Sub Fan(jTask As Task)
Dim jjTask As Task
jTask.Flag5 = True
If Forward Then
For Each jjTask In jTask.SuccessorTasks
If jjTask.Flag5 <> True Then
If IsCrit And Not IsDrive Then
If jjTask.Critical = True Then
Fan jjTask
End If
ElseIf IsDrive = True Then
If jjTask.FreeSlack < 100 Then
Fan jjTask
End If
Else
Fan jjTask
End If
End If
Next jjTask
Else
For Each jjTask In jTask.PredecessorTasks
If jjTask.Flag5 <> True Then
If IsCrit And Not IsDrive Then
If jjTask.Critical = True Then
Fan jjTask
End If
ElseIf IsDrive = True Then
If jjTask.FreeSlack < 100 Then
Fan jjTask
End If
Else
Fan jjTask
End If
End If
Next jjTask
End If
End Sub

'Filter with or without summary tasks
Private Sub FilterMe()
If MsgBox("Do you want to display Summary Tasks?", vbYesNo, "Display Summary Tasks?") = vbYes Then
IsSum = True
Else: IsSum = False
End If
OutlineShowAllTasks
FilterEdit Name:="_Trace", TaskFilter:=True, _
Create:=True, _
OverwriteExisting:=True, _
FieldName:="Flag5", _
Test:="Equals", _
Value:="Yes", _
ShowInMenu:=False, _
ShowSummaryTasks:=IsSum
FilterApply Name:="_Trace"
End Sub

Use Alt + Q to close the Visual Basic Code Editor and return to MS-Project.

How to use it

In MS-Project 2016, select View → Macros → View Macros, which should get you to this dialogue box:

Select the macro with the name ending in “Trace” and click on Run.

In the dialogue box that appears, tell the macro whether you want to trace Predecessors, Successors, or All.

Then tell the macro whether you want to trace only driving tasks – selecting yes here will cut the length of the list you will need to check if you are tracing predecessors to find out why a milestone is showing later than you expect.

Then tell the macro if you want to display summary tasks – I would generally go with yes here as it helps to give you context, which is useful if you have a number of similar phases and they all contain a task called “design” rather than “phase 1 design”, “phase 2 design” and so on.

There will probably be a delay of a few moments between each of these steps while the macro works behind the scenes, but after a short while the display will change and you will see only the tasks that are predecessors or successors of the task you initially selected.

You should now be able to fairly easily spot a predecessor that has moved from its baseline dates, or a successor that has drifted beyond its deadline.

How it works

The macro first goes through every task in the project and sets the value in Flag 5 to FALSE, effectively clearing the values in the field, either from earlier uses or from earlier runs of the macro.

It then runs through the tasks in the plan in order, sets Flag 5 to TRUE if the task it is looking at is a successor or predecessor of the initially selected task, and then follows the dependency chain forwards or backwards depending on the settings.

Once it has finished, it edits the settings of a custom filter to display summary tasks (or not, as selected), then applies that filter so that only marked tasks are displayed.

If you want a better explanation than that, you’ll need to ask Jack Dahlgren!

The Result

This clever macro enables you to easily trace the root cause of a slipped deliverable milestone, or to assess the impact delaying as task. This can be really useful and save you loads of time as you work to keep your programme schedule on track.

Is this approach useful to you? How do you trace the root cause of delays, or assess the impact of schedule changes? Let me know in the comments.

If you would like to have the benefits of an approach like this but would prefer not to get bogged down in the detail, The PMO Professionals can help. Why not take a look at our “Savvy scheduling” service, and if that looks interesting, get in touch to discuss how we can help you?

® Microsoft, Project, Word, PowerPoint and Excel are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries.

The macro featured in this post was written by Jack Dahlgren and originally published on his site masamiki.com. It’s not there any more but I’m republishing it here with his permission as I find it so useful (thanks Jack!).