Simple Macro for Computing (Duration) Drag in MS Project

Here is the BFDDrag macro, which implements a brute-force technique to compute Critical Path Drag for all tasks in the active project.

Drag and Drag cost are two useful metrics in CPM scheduling that Stephen Devaux introduced in his book Total Project Control, but they are not widely computed or used.  Drag – coined as “Devaux’s Removed Activity Guage,” represents the individual contribution of any single schedule element (e.g. a task) to the overall duration of a project.  By definition, only “Critical Path” tasks have it, and a task’s Drag indicates the total theoretical project acceleration that could be gained if the task’s remaining duration were reduced to zero.  (Duration is not the only contributor to Drag – consider predecessor lags and constraints – but it is certainly the most prevalent.)

Drag can be useful in prioritizing activities for crashing (i.e. shortening) a project schedule.  Consider the simple project illustrated in Figure 1.  The “Critical Path” (ACFIJ – dark-red bars) is partially flanked by a near-critical path (BEH – hatched red bars) with a relative float (same as total slack here) of 2 days.  The overall project duration can be shortened by accelerating critical-path tasks, and most experienced schedulers would be tempted to first consider modifying the two longest critical tasks (C and I).  Accelerating either of them by more than two days yields no benefit, however, as the near-critical path then becomes Critical.  Drag indicates this limit for each task – not so obvious in complex project schedules – in advance.

Figure 1: NLD59DRAG1
Figure 1: Simple Drag Example

Below is a simple, brute force, macro for computing task drag (i.e. the drag of it’s duration) in Microsoft Project. The procedure cycles through each task in the project. If the task is a non-summary, incomplete task that is marked “Critical”, then the procedure sets its remaining duration to zero and measures the resulting acceleration (if any) of the project finish date. That value is stored in the Custom Field – “Duration5” as DRAG.  You’ll have to change this in the macro if “Duration5” is already in use.  I’ve also included a few comments in the code to guide you in modifying its scope.  The procedure is logged in the “Immediate” window of the Visual Basic editor.  After running this macro, Drag Cost could be separately computed for each task using a custom field formula that references the value in “Duration5”.

As noted, this is a “Brute Force” approach that relies on repetitive re-calculation of the CPM network by MSP’s scheduling engine.  At two calculation cycles for each critical task, it may take a long time to chug through a very large project, but it will get there eventually.  If your schedule has negative Total Slack, then MSP may interrupt the procedure to pop up a schedule warning window that you will need to click through (I have not added code to turn this warning off, but you can if you want).  This warning window may not always be visible if MSP is not the active application – you’ll need to click on it occasionally.


Sub BFDDrag()

'       Coded by Thomas Boyle, PE, PMP, PSP on 8Jan'16
'       This is a simple brute-force procedure to compute Duration DRAG (Devaux's Removed Activity Guage") for
'       each Critical task in the active project.  The procedure cycles through each task in the project.  If the task
'       is a non-summary, incomplete task that is marked "Critical", then the procedure sets its duration to zero and
'       measures the resulting acceleration (if any) of the project finish date.  That value is stored in the Custom
'       Field - "Duration5" as DRAG.  The procedure is logged in the "Immediate" window of the Visual Basic editor.

    Dim Tsk As Task, ODur As Double, OFin As Date, TDrag As Double
    Dim StartNote As String
    Dim Counter As Long
        
    Application.CalculateProject
    StartNote = ActiveProject.Name & vbCrLf & Now() & " Starting Drag Brute Force" & vbCrLf
    Counter = 0
        Debug.Print StartNote
    'OFin is the finish date to be tested.  Default is the Project Finish.
    OFin = ActiveProject.ProjectFinish
    
    'Default computes DRAG for each task in ActiveProject.  To compute only for selected tasks,
    'then change "ActiveProject.Tasks" to "ActiveSelection.Tasks"
    For Each Tsk In ActiveProject.Tasks
        If Not Tsk Is Nothing Then
            If Tsk.Summary = False And Tsk.PercentComplete <> 100 Then
                Tsk.Duration5 = 0
                Counter = Counter + 1
                Debug.Print Counter & "Checking Task: " & Tsk.ID & " " & Tsk.Name
                
                If Tsk.Critical = True Then
                    If Tsk.Duration > 0 Then
                        ' Remove Task Duration and Check Finish Date
                        ODur = Tsk.Duration
                        Tsk.Duration = Tsk.ActualDuration
                        Application.CalculateProject
                        If Application.DateDifference(ActiveProject.ProjectFinish, OFin) > 0 Then  'Decreasing Normal Critical case
                            TDrag = Application.DateDifference(ActiveProject.ProjectFinish, OFin)
                        Else    'Decreasing Neutral or Reverse Critical case (Zero Drag)
                            'Test for Increasing Reverse Critical Case
                            Tsk.Duration = ODur + 1 'Adding one minute to Duration
                            Application.CalculateProject
                            If Application.DateDifference(ActiveProject.ProjectFinish, OFin) > 0 Then
                                'Increasing Reverse Critical case
                                TDrag = -1 * 5 'INDICATOR ONLY. Need further testing to find limit
                            Else
                                TDrag = 0
                            End If
                        End If
                        Debug.Print "Drag = " & TDrag / 60 / ActiveProject.HoursPerDay & "Days"
                        Tsk.Duration5 = TDrag
                        Tsk.Duration = ODur
                        Application.CalculateProject
                    End If 'Positive Duration

                Else
                    Debug.Print "Non-Critical"
                End If 'Critical Task Check DRAG
            End If 'Summary=No
        End If 'Not Nothing
    Next Tsk
    
    Debug.Print "Previous: " & StartNote
    Debug.Print Now(), "Finished Drag Brute Force"

End Sub

I started to include Lag Drag in the procedure, but modifying the lag value through code is a little more complicated.  That will have to wait for another time.

I originally wrote a version of this brute-force macro to test the performance of the drag computations in BPC Logic Filter.  Unfortunately, although BPC Logic Filter runs much, much faster for larger networks, it can fail to catch the Drag limitations caused by early start constraints and task calendar changes away from a given task.  This simple macro is still the standard (for incomplete tasks).

[20Jan’16 – Edited code to catch “negative” drag for “reverse-critical” tasks.  These are rare critical-path tasks that include a reverse flow of driving logic.

22Jan’16 – Further code edit to zero-out negative drag for all except the singular case where increasing task duration shorten’s the project.  In that case, the code leaves a value of -0.01 days as an indicator only.]

[This macro computes Drag only with respect to the overall completion of the active project, not any specific completion milestone, and it uses the active project’s default calendar for quantifying Drag.  If your true Critical Path is defined by some completion milestone that is not the last task in the project, then you will need a more powerful tool.  This video demonstrates Drag calculation in BPC Logic Filter.]

 

Leave a Reply

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