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.

Defining the Ready To Work Metric

Happy New Year!

…Mongolian New Year that is.  May your animals fatten nicely…

In honor of the holiday, I figured that I’d include this useful vocabulary tip from a Project Manager’s Guide to the Mongolian Language:

Buda – dry rice.  Not cohesive.  No matter how hard you squeeze a handful of dry rice, it will never stick together.  Example: “Call a wrecker, this car is buda.” or “Stick a fork in it and call it done.  This project team is buda.”

Yag Shuu Dee – (pronounced “Yagshday”).  “Yes,” but actually meaning “No.”  The ironic equivalent of “Yeah, right.”  As in “Project management is great!  Yag Shuu Dee.”

Link below for further reading….

Tsagaansar 2012

…and pictures from our last trip to the Land of Blue Sky – although it was more grey sky at the time.

Happy New Year!

Accruing BCWP for Ahead of Schedule Tasks

So I sat down a couple of days ago to knock out a couple of screenshots to support my upcoming EVM presentation at the Project Conference.  My goal was to illustrate a well-known issue with how EVM is calculated within the Project client.

For years, I’ve heard how Microsoft Project cannot calculate BCWP for tasks that complete ahead of schedule – i.e. BCWP only accrues during the same time period as the task baseline.  This has long been a significant complaint about the EVM engine within Project.

Much to my surprise, I found that I couldn’t get the functionality to fail.  After some testing, it appears that the fine folks at Redmond have quietly fixed a very significant issue that has been lurking in the shallow depths of the Microsoft Project client.  Or maybe, in the nature of software bugs the world over, it just fixed itself as an unintended consequence of some other change.

In the example below, I have marked several tasks as complete – even though the baseline dates clearly are displayed after the status date.  Note how BCWP is calculated.

image

Yep.  BCWP is definitely accruing for ahead of schedule tasks.

Through some preliminary testing, this functionality seems related to the Microsoft Project 2010 baseline mechanism.  When I tested this against a file baselined in 2007, I could not get BCWP to appear correctly.  It was only after I rebaselined the file in 2010 that BCWP would calculate.

Now, I am sure that there will still be challenges in using Microsoft Project standalone to support EVM calculations, but this is definitely a very welcome step in the right direction.

Accruing BCWP for Ahead of Schedule Tasks

Find UID Utility

Hey folks…sorry for being gone so long.  Been a bit busy managing the schedule for an ERP cutover.  Figured I’d post a little VBA utility that I wrote to help navigate the schedule.

As many of you probably know, it’s best practice to expose and use the Unique ID field within the schedule.  The Unique ID field doesn’t change – unlike the ID field which will recalculate to reflect any new tasks added.

In this case, with a couple thousand items, it got hard to find a specific UID.  So, I wrote a quick macro to automate the process.

This macro will navigate to a specific UID within the schedule.  It also will post the UID of original task that you’re on when you run it to the Number10 field.

Sub FindUID()

    Dim T As Task
    Dim Temp_UID As Integer
    Dim UID As Integer
    Dim ID As Integer
    
    ActiveProject.ProjectSummaryTask.Number10 = Application.ActiveSelection.Tasks.Item(1).UniqueID
    
    UID = InputBox("Enter UID", "UID")
    ID = 1
    
    ActiveProject.AutoFilter = False
    
    For Each T In ActiveProject.Tasks
        Temp_UID = T.UniqueID
        If Temp_UID = UID Then
            ID = T.ID
        End If
        Debug.Print T.UniqueID
    Next T
    
    If ID <> 1 Then
        SelectRow row:=ID, RowRelative:=False
    Else
        MsgBox "UID Not Found", vbOKOnly, "Error"
    End If
    
    ActiveProject.AutoFilter = True
    OutlineShowAllTasks
    
End Sub

The following macro is essentially the same, but will pick up the number in Number10 and return to that row.

Sub GoBacktoUID()

    Dim T As Task
    Dim Temp_UID As Integer
    Dim UID As Integer
    Dim ID As Integer

    ID = 1
    
    ActiveProject.AutoFilter = False
    
    For Each T In ActiveProject.Tasks
        Temp_UID = T.UniqueID
        If Temp_UID = ActiveProject.ProjectSummaryTask.Number10 Then
            ID = T.ID
        End If
        Debug.Print T.UniqueID
    Next T
    
    If ID <> 1 Then
        SelectRow row:=ID, RowRelative:=False
    Else
        MsgBox "UID Not Found", vbOKOnly, "Error"
    End If
    
    ActiveProject.AutoFilter = True
    OutlineShowAllTasks
    
End Sub

Assign these macros to two key strokes and you’ll be easily able to navigate a large project with ease.

Find UID Utility