Defining the Ready To Work Metric

A little more VBA from my recent foray into the world of ERP cutover scheduling.  In this case, we had a complicated schedule with many predecessor and successor relationships.  The challenge was to develop a metric to indicate when, in fact, a task was ready to work.

I had seen fellow MVP, Jack Dahlgren’s Ready to Start macro on his Website and decided to adapt the concept to my own needs.  While this macro shares a name and a concept, it essentially scores the task differently – and accounts for inactive tasks in the schedule.

The following code runs through a schedule and drops a ReadytoWork (RTW) value into an enterprise custom field.  You could substitute a number field for the enterprise custom field.

Essentially, the RTW score is calculated as the (number of completed predecessors)/(total number of predecessors) X 100.  Run the RTW macro each evening after updating the schedule and then republish.  Any task with a RTW score of 100 is, well, ready to work.

For added benefit, throw the RTW score onto users’ My Tasks page.  With some training, they will easily be able to identify work that is in their queue – regardless of when it was actually scheduled.

The macro will also mark milestones as completed if their RTW score = 100 and they are not constrained.  For that to work properly, you’ll need to run this macro prior to rescheduling unstarted activities after the status date.

Sub ReadyToStart()

    Dim T As Task
    Dim PT As Task
    Dim PredComp As Integer
    Dim Pred As Integer
    Dim X As Integer
    
    For X = 1 To 2
        For Each T In ActiveProject.Tasks
            T.ReadytoWork = 0
            PredComp = 0
            Pred = 0
            If T.Active = True And T.PercentComplete < 100 And T.Summary = False Then
                For Each PT In T.PredecessorTasks
                    If PT.Active = True Then
                        Pred = Pred + 1
                    End If
                    If PT.PercentComplete = 100 Then
                        PredComp = PredComp + 1
                    End If
                Next PT
                If Pred = 0 Then
                    T.ReadytoWork = 100
                Else
                    T.ReadytoWork = Round((PredComp / Pred) * 100, 0)
                End If
                    If T.Milestone = True Then
                        If T.ReadytoWork = 100 And T.ConstraintType = 0 Then
                            T.PercentComplete = 100
                        End If
                    End If
                End If
            Debug.Print T.ID & ": " & T.ReadytoWork
        Next T
    Next X

End Sub

The only  catch is that on the first run through of the schedule, it may mark some milestones as complete – but then not mark any successor activities as RTW = 100.  For this reason, I have the macro set to run twice.  That doesn’t solve the issue, but it seems to mitigate any related problems.

Advertisements
Defining the Ready To Work Metric

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s