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.