Macro for Tracing, Filtering, and Sorting Task Paths in Microsoft Project

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.)

15 thoughts on “Macro for Tracing, Filtering, and Sorting Task Paths in Microsoft Project”

    1. You’re welcome! I do prefer BPC Logic Filter, but QuickTrace will do in a pinch – especially if your company has locked-out add-ins.

  1. Hi,
    Great macro!
    I found a few cases where it does not work correctly (e.g. it keeps a few non-critical tasks in), but I can’t figure out why.
    I could share a Project file example where the issue shows up, if you let me know how to send it to you.
    Thanks

    1. Glad you found it useful. As I’ve pointed out in another entry that I referenced above, the “driving path” identified by the macro generally agrees with the “driving predecessors” “task path” findings (in MSP 2016), but both become unreliable in defining true driving logic paths in the presence of complicating factors. (BPC Logic Filter was developed to handle such complications.)

      MSP’s definition of “Critical” and “non-Critical” are based strictly on Total Slack, which sometimes has nothing to do with driving logic paths. I’ve sent an email to the address you provided when commenting. You can include a test file in reply to that, then we can take the troubleshooting off line.

      1. Fed sent me the example file that I used in the Aug’18 edit to the post. As shown, Manually-scheduled tasks also cause problems for the logic elements (provided by MSP) that are used in these macros.

  2. I made some edits to the code to provide a highlighting (rather than filtering/exclusion) option and to put up a message box defining the filter basis at the end, in addition to general clean up.

  3. This is awesome, I can’t believe this isn’t built into Project by default!. Is there any way to have it work with subprojects as well? I have a large master project with 5 sub-projects managed by other team members. We are using Project Server to link their subs into my master. The macro seems to ignore external predecessors. Is there any way to modify the code to bring those in? Or does BPC logic filter do that?

    Thanks Tom!

    1. Matt, I’m glad you found it useful. The “Task Path” bar styles built into MSP 2013+ are somewhat similar, though you need to use another macro (here) to apply a corresponding filter.

      Neither “Task Path” nor this macro – which uses the same underlying data – make the jump across external relationships. Yes, BPC Logic Filter does it.

  4. As i am using a german version of MS project i have difficutlties with apply the filter method on “Attribut20” i also tried the corresponding “flag20″ field
    FilterEdit Name:=”Attribut20″, TaskFilter:=True, Create:=True, _
    OverwriteExisting:=True, FieldName:=”Attribut20″, Test:=”equals”, _
    Value:=”Yes”, ShowInMenu:=True, ShowSummaryTasks:=ShowSums

    FilterApply Name:=”Attribut20″, Highlight:=HL

    Moreover this piece of code does not work, cause of “;”
    If Driv Then Msg = Msg & “driving ”

    Msg = Msg & “predecessors ”

    If Driv Then Msg = Msg & “driving & driven ”

    Msg = Msg & “predecessors & successors ”

    ‘Msg = Msg & “of task ” & Tsel.ID & “: ” & Tsel.Name & ” (inclusive)”

    1. Tim,
      The macros I share all use English-us defaults. Some adaptation for other languages is normal – in particular the list separator (“,” or “;”) and the dialog words that are in “quotation marks” in the code. E.g. “equals”, “Yes”, “Flag4”, “Number5”. In German, these become “Gleich”, “Ja”, “Attribut4”, and “Zahl5″, so you need to change the FilterEdit line to:

      FilterEdit Name:=”Attribut20″; TaskFilter:=True; Create:=True; _
      OverwriteExisting:=True; FieldName:=”Attribut20″; Test:=:”Gleich”; _
      Value:=”Ja”; ShowInMenu:=True; ShowSummaryTasks:=ShowSums

      Thanks for pointing out the issue with the “amp;” insertions. This code corruption seems to result from a new bug in one of our WordPress plugins. The fix is to remove all instances of “amp;”. I have done this on the published code block and hope that the issue does not return.

      (BPC Logic Filter – our add-in that includes a variation of Quicktrace – automatically makes these adjustments for various language packs.)

      1. Hello Tom,

        I’m struggling with the same issue as Tim only I am using the Dutch language. I think is translated all the “word” correctly
        but I still become an error running the macro.

        I assume the issue is related to test:=”equals” which I already tried with Dutch translation “gelijk” but didn’t work.

        Do you know the correct translation?

        Thank you very much.

        FilterEdit Name:=”Vlag4″, TaskFilter:=True, Create:=True, _
        OverwriteExisting:=True, FieldName:=”Vlag4″, Test:=”equals”, _
        Value:=”Ja”, ShowInMenu:=True, ShowSummaryTasks:=ShowSums
        FilterApply Name:=”Vlag4″, Highlight:=HL

        1. Hi Michael,
          We’ve not yet ported our add-in software to Dutch, so I don’t have quick access to that field. The syntax is tailored to local language users, so a 1-word literal translation doesn’t typically apply. An easy check for you is to start creating a filter yourself, then verify exactly what phrase is used instead of the English “equals”. That’s all I’ve got for now. Please report back, and we’ll track it down together. tom

          1. Hello Tom,

            Thank you for your quick reply.

            The filter should be ‘is gelijk aan’. Then it works great.
            Thank you for sharing this kind of features!

            Cheers,
            Michael.

          2. Thanks for the feedback. Glad you got it sorted. By the way, we could easily port the add-in to Dutch in an afternoon if that was of interest to you…. Regards, tom

Leave a Reply

Your email address will not be published. Required fields are marked *