Here are three macros (collectively called QuickTrace) to display the logical predecessors or successors (or both) of the selected task – filtering out all others – and sorted by logical path. The filter can be limited to show all logic or only “driving” logic. There is also a highlight-only option.
With the apparent demise of Mike Dahlgren’s site, masamiki.com, his “Trace” macro seems to no longer be generally available. (It’s also a violation of his site’s terms of use to re-distribute code that was obtained there.) My entry on Listing Driving Predecessors has been getting a lot of traffic from people who (I suspect) are trying to find a variation of Trace. In response to a question over at MPUG today, I decided to write up something – what I call QuickTrace – to generate similar output for sharing. It is after all less than a hundred lines of code. [Note: The code here works for all modern – i.e. 2007 and later – versions of MSP. If you are already using the “Task Path” bar styles in MSP 2013+ and are looking for a compatible filter, then have a look at my other article: Macro for Filtering based on “Task Path” in Microsoft Project.]
For determining driving relationships, QuickTrace relies on MSP’s “StartDriver” task object, the basis of the Task Inspector pane (and the “Task Path” Driving Predecessors and Driven Successors bar styles in MSP 2013+). This is a substantial improvement over the original Trace macro, which used Free Slack as the driving indicator. Still, I’ve found StartDriver to be unreliable in the presence of non-FS relationships (See here.) BPC Logic Filter (our MSP Add-In) instead identifies driving relationships directly by computing and examining relationship free floats – quite a bit more involved.
[Jan’19 Edit: QuickTrace also relies on recursion (a sort of repeated self-cloning process), and this makes it susceptible to crashing if the path length (i.e. the number of tasks in sequence) is too long. In MSP 2010, I’ve analyzed path lengths a bit over 4,000 tasks before crashing. The same analysis in MSP 2016 leads to a crash after only 700 tasks.
Version 1.5 of BPC Logic Filter now includes a QuickTrace option.
This implements the same recursive tracing algorithm that I’ve included in the macro code. It’s blazing fast, and its results are perfectly aligned with the Task Path bar styles of MSP 2013+, no matter how flawed. It also handles much longer path lengths (just under 8,000 tasks in MSP 2016) before running out of memory.]
Here’s the code. There are basically three front-end macros that you can assign buttons or hot keys to, one for predecessor chains, one for successor chains, and one for both. (Using the last one can make the resulting path sort a little jumbled, so I made re-sorting optional.) These call the other procedures to a) collect user input; b) clear existing values in the Flag4 and Number5 fields; c) recursively run through the chains of related tasks and and mark them using those fields; d) apply the filter and sort using those fields; and e) display a message box summarizing the filter/highlighting. Note, this is provided as-is and is not supported by anyone. I have not taken the time to accommodate every possible situation and won’t be doing so in the future. If you are new to vba, please google around a bit before asking questions that are already answered somewhere else – that includes, “how do I install this and make it work?” (Short answer: copy and paste the entire block into a new module in the visual basic editor. Then add the three front-end macros to a custom group on one or more of your ribbon tabs.)
'QuickTrace Module
'Coded by T.Boyle, PE, PSP on 16Mar'17 [25Sep'18 edits - to allow highlighting, to provide a descriptive message box
' after running, and to streamline the code.] This Module is intended to trace logical paths from the selected task
' to all of its predecessors or successors, then show only related tasks. Tasks are sorted in the order of analysis,
' which generally corresponds to identified logical "paths".
'CAVEATS:
' 1. This code WILL OVER-WRITE fields FLAG4 and NUMBER5. Make sure these fields are not needed before running. Otherwise,
' edit the code to use different fields, as shown below.
' 2. This code relies on the StartDriver object for defining driving path logic. It may not always be reliable for non FS
' relationships.
' 3. Install all code into a new module, with "QuickTrace Module" above as the top line.
' 4. Assign buttons or hotkeys to the first three procedures only (the others are called by these three):
'a. CallQTraceP() - Traces predecessors.
'b. CallQTraceS() - Traces successors.
'c. CallQTraceB() - Traces both predecessors and successors (Added 15Nov'17)
'
Option Explicit
Private Cnt As Long, Driv As Boolean, HL As Boolean, ShowSums As Boolean, Tsel As Task, DirGlob As String
Sub CallQTraceP()
'This procedure finds, marks, filters, and sorts predecessors of the selected task.
'Run this directly using a button or hot key
Cnt = 0
DirGlob = "P"
ClearFields
CollectInput
'Run Trace from Selected cell
Call QTrace(Tsel, "P")
Call Filter("P")
End Sub
Sub CallQTraceS()
'This procedure finds, marks, filters, and sorts successors of the selected task.
'Run this directly using a button or hot key
Cnt = 0
DirGlob = "S"
ClearFields
CollectInput
'Run Trace from Selected cell
Call QTrace(Tsel, "S")
Call Filter("S")
End Sub
Sub CallQTraceB()
'This procedure finds, marks, filters, and sorts both predecessors and successors of the selected task.
'Run this directly using a button or hot key
Cnt = 0
DirGlob = "B"
ClearFields
CollectInput
'Run Trace from Selected cell
Call QTrace(Tsel, "P")
Call QTrace(Tsel, "S")
Call Filter("P")
End Sub
Sub QTrace(ByRef t As Task, ByVal dir As String)
'This procedure marks a task (as related) and calls itself for each related predecessor or successor.
'This procedure is called by another procedure.
Dim d As TaskDependency
Dim ds As TaskDependency
'Mark this task as related
Cnt = Cnt + 1
'''''''''''''''''''''''''''''''''''''''''''''Edit Fields Flag4 and Number5 as Needed'''''''''''''''''''''''''''''''''''''''''
t.Flag4 = True
t.Number5 = Cnt
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Recurse to next dependency
If Driv Then
If dir = "P" Then
For Each d In t.StartDriver.PredecessorDrivers
Call QTrace(d.From, "P")
Next d
Else 'i.e. dir="S"
For Each ds In t.TaskDependencies
If ds.From = t Then
For Each d In ds.To.StartDriver.PredecessorDrivers
If d.From = t Then Call QTrace(d.To, "S")
Next d
End If
Next ds
End If
Else
For Each d In t.TaskDependencies
If dir = "P" And d.To = t Then Call QTrace(d.From, "P")
If dir = "S" And d.From = t Then Call QTrace(d.To, "S")
Next d
End If
End Sub
Sub CollectInput()
'This procedure collects user input.
'This procedure is called by another procedure.
Driv = False
HL = False
ShowSums = False
Set Tsel = ActiveCell.Task
If MsgBox("Driving Path only?", vbYesNo) = vbYes Then Driv = True
If MsgBox("Highlight only?", vbYesNo) = vbYes Then
HL = True
Else
If MsgBox("Show Summary Tasks?", vbYesNo) = vbYes Then ShowSums = True
End If
End Sub
Sub ClearFields()
'This procedure runs through the tasks of the active project and clears two selected fields for use.
'This procedure is called by another procedure.
Dim t As Task
'Clear Fields
For Each t In ActiveProject.Tasks
If Not t Is Nothing Then
'''''''''''''''''''''''''''''''''''''''''''''Edit Fields Flag4 and Number5 as Needed'''''''''''''''''''''''''''''''''''''''''
t.Flag4 = False
t.Number5 = 0
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
End If
Next t
End Sub
Sub Filter(ByVal dir As String)
'This procedure creates and applies a filter to show only related tasks.
'This procedure is called by another procedure.
'''''''''''''''''''''''''''''''''''''''''''''Edit Field Flag4 as Needed'''''''''''''''''''''''''''''''''''''''''
FilterEdit Name:="Flag4", TaskFilter:=True, Create:=True, _
OverwriteExisting:=True, FieldName:="Flag4", Test:="equals", _
Value:="Yes", ShowInMenu:=True, ShowSummaryTasks:=ShowSums
FilterApply Name:="Flag4", Highlight:=HL
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
If ShowSums Then '(HL is False)
'Sort by path order
If MsgBox("Resort to show paths?", vbYesNo) = vbYes Then
'''''''''''''''''''''''''''''''''''''''''''''Edit Field Number5 as Needed'''''''''''''''''''''''''''''''''''''''''
If dir = "P" Then Sort Key1:="Number5", Ascending1:=False, Renumber:=False, Outline:=True
If dir = "S" Then Sort Key1:="Number5", Ascending1:=True, Renumber:=False, Outline:=True
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
End If
Else '(ShowSums is False and HL may be true or false)
If Not HL Then
'Sort by path order
If MsgBox("Resort to show paths?", vbYesNo) = vbYes Then
'''''''''''''''''''''''''''''''''''''''''''''Edit Field Number5 as Needed'''''''''''''''''''''''''''''''''''''''''
If dir = "P" Then Sort Key1:="Number5", Ascending1:=False, Renumber:=False, Outline:=False
If dir = "S" Then Sort Key1:="Number5", Ascending1:=True, Renumber:=False, Outline:=False
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
End If
End If
End If
EditGoTo ID:=Tsel.ID
FilterBox
End Sub
Sub FilterBox()
'This procedure creates and displays a message box describing the filter/highlight basis.
'This procedure is called by another procedure.
Dim Msg As String
If HL Then
Msg = "Highlighting "
Else
Msg = "Filtering for "
End If
Select Case DirGlob
Case "P"
If Driv Then Msg = Msg & "driving "
Msg = Msg & "predecessors "
Case "S"
If Driv Then Msg = Msg & "driven "
Msg = Msg & "successors "
Case "B"
If Driv Then Msg = Msg & "driving & driven "
Msg = Msg & "predecessors & successors "
End Select
Msg = Msg & "of task " & Tsel.ID & ": " & Tsel.Name & " (inclusive)"
MsgBox Msg
End Sub
[Aug’18 Edit:] One of the commenters sent an example of a schedule where the macro includes in the driving path to project completion two tasks that are in fact neither critical nor driving . As shown below, the Task Path functionality is used to highlight the “Driving Predecessors” to Task 13 (orange bars).
Tasks 21 and 22 are included in the Task Path highlighting, and they are also flagged as part of the Driving Path (to Task 13) by the QuickTrace macros. This is because MSP has marked Task 22 as the StartDriver predecessor for Task 26. As a manually-scheduled task, however, Task 26 really has NO StartDriver predecessor, and the reference to Task 22 is incorrect. Neither the macro nor the Task Path function has been adapted to account for this. (BPC Logic Filter correctly excludes these non-driving tasks, and MSP marks them as non-critical because they possess positive Total Slack.)