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:
- Filter the schedule by setting the successors field to show only those that have 333 as a successor.
- Review all these tasks to see which one has moved and is causing the delay.
- 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.
- 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.
- 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!).