Option Explicit
Sub CheckOutOfSequence()
'*******************************************************************************************
'* This macro will flag Text30 with offending predecessor out of sequence UIDS.            *
'* This macro was developed by Johnny "is totally awesome" Barrett   March 21, 2012        *
'* Further modified by Ron Winter, PSP, FAACE 31OCT18                                      *
'* Feel free to copy/modify/distribute/delete/steal/take-credit-for any code found within. *
'*******************************************************************************************

Dim t As Task
Dim ts As Tasks
Dim a As Assignment
Dim pred As Task
Dim predcount As Long
Dim TotalTasks As Long
Dim TotalOOSActs As Long
Dim TotalOOSEvents As Long
Dim LastEventCount As Long
Dim L As Long
Dim TotalEarly As Long
Dim Testdate As Date

OptionsCalculation AUTOMATIC:=False
If MsgBox("WARNING: This will replace any custom data in Text30.  Do you want to continue?", _
    vbYesNo, "Out of Sequence Macro") = vbYes Then
    Set ts = ActiveProject.Tasks
    For Each t In ts
        t.Text30 = ""
        If Not (ts Is Nothing) Then ' this handles a blank task line
        If Not t.Summary Then
            TotalTasks = TotalTasks + 1
            LastEventCount = TotalOOSEvents ' if don't match, then a new OOS act was added.
            If IsDate(t.ActualStart) Then
                predcount = 1
                For Each pred In t.PredecessorTasks
                    'GET THE RELATIONSHIP TYPE
                    Select Case t.TaskDependencies(predcount).Type
                        Case pjFinishToStart
                            If pred.PercentComplete < 100 Then
                                'Note: Early Finish as it does not consider the data date.
                                TotalEarly = TotalEarly + t.RemainingDuration ' Best guess.
                                TotalOOSEvents = TotalOOSEvents + 1
                                t.Text30 = t.Text30 & pred.UniqueID & ","
                            ElseIf t.TaskDependencies(predcount).Lag > 0 Then
                                'Note: Any line that ends with a "_" means add the next line.
                                If Application.DateAdd(pred.ActualFinish, _
                                (t.TaskDependencies(predcount).Lag), _
                                ActiveProject.Calendar) > t.ActualStart Then
                                    Testdate = Application.DateAdd(pred.ActualFinish, _
                                        (t.TaskDependencies(predcount).Lag))
                                    L = Application.DateDifference(t.ActualStart, Testdate, _
                                        ActiveProject.Calendar)
                                    If L > 0 Then
                                        TotalEarly = TotalEarly + L
                                    End If
                                    TotalOOSEvents = TotalOOSEvents + 1
                                    t.Text30 = t.Text30 & pred.UniqueID & ","
                                End If
                            ElseIf t.TaskDependencies(predcount).Lag < 0 Then
                                If Application.DateSubtract(pred.ActualFinish, _
                                -(t.TaskDependencies(predcount).Lag), _
                                ActiveProject.Calendar) > t.ActualStart Then
                                    Testdate = Application.DateSubtract(pred.ActualFinish, _
                                        Abs((t.TaskDependencies(predcount).Lag)))
                                    L = Application.DateDifference(t.ActualStart, Testdate, _
                                        ActiveProject.Calendar)
                                    If L > 0 Then
                                        TotalEarly = TotalEarly + L
                                    End If
                                    TotalOOSEvents = TotalOOSEvents + 1
                                    t.Text30 = t.Text30 & pred.UniqueID & ","
                                End If
                            ElseIf pred.ActualFinish > t.ActualStart Then
                                Testdate = pred.ActualFinish
                                L = Application.DateDifference(t.ActualStart, Testdate, _
                                    ActiveProject.Calendar)
                                If L > 0 Then
                                    TotalEarly = TotalEarly + L
                                End If
                                TotalOOSEvents = TotalOOSEvents + 1
                                t.Text30 = t.Text30 & pred.UniqueID & ","
                            End If
                        Case pjStartToStart
                            If Not IsDate(pred.ActualStart) Then
                                If t.TaskDependencies(predcount).Lag > 0 Then
                                    Testdate = Application.DateAdd(pred.EarlyStart, _
                                        (t.TaskDependencies(predcount).Lag))
                                ElseIf t.TaskDependencies(predcount).Lag < 0 Then
                                    Testdate = Application.DateSubtract(pred.EarlyStart, _
                                        Abs((t.TaskDependencies(predcount).Lag)))
                                Else
                                    Testdate = pred.EarlyStart
                                End If
                                L = Application.DateDifference(t.ActualStart, Testdate, _
                                    ActiveProject.Calendar)
                                If L > 0 Then
                                    TotalEarly = TotalEarly + L
                                End If
                                TotalOOSEvents = TotalOOSEvents + 1
                                t.Text30 = t.Text30 & pred.UniqueID & ","
                            ElseIf t.TaskDependencies(predcount).Lag > 0 Then
                                If Application.DateAdd(pred.ActualStart, _
                                (t.TaskDependencies(predcount).Lag), _
                                ActiveProject.Calendar) > t.ActualStart Then
                                    Testdate = Application.DateAdd(pred.ActualStart, _
                                        (t.TaskDependencies(predcount).Lag))
                                    L = Application.DateDifference(t.ActualStart, Testdate, _
                                        ActiveProject.Calendar)
                                    If L > 0 Then
                                        TotalEarly = TotalEarly + L
                                    End If
                                    TotalOOSEvents = TotalOOSEvents + 1
                                    t.Text30 = t.Text30 & pred.UniqueID & ","
                                End If
                            ElseIf t.TaskDependencies(predcount).Lag < 0 Then
                                If Application.DateSubtract(pred.ActualStart, _
                                -(t.TaskDependencies(predcount).Lag), _
                                ActiveProject.Calendar) > t.ActualStart Then
                                    Testdate = Application.DateSubtract(pred.ActualStart, _
                                        Abs((t.TaskDependencies(predcount).Lag)))
                                    L = Application.DateDifference(t.ActualStart, Testdate, _
                                        ActiveProject.Calendar)
                                    If L > 0 Then
                                        TotalEarly = TotalEarly + L
                                    End If
                                    TotalOOSEvents = TotalOOSEvents + 1
                                    t.Text30 = t.Text30 & pred.UniqueID & ","
                                End If
                            ElseIf pred.ActualStart > t.ActualStart Then
                                Testdate = pred.ActualStart
                                L = Application.DateDifference(t.ActualStart, Testdate, _
                                    ActiveProject.Calendar)
                                If L > 0 Then
                                    TotalEarly = TotalEarly + L
                                End If
                                TotalOOSEvents = TotalOOSEvents + 1
                                t.Text30 = t.Text30 & pred.UniqueID & ","
                            End If
                        Case pjFinishToFinish
                            If IsDate(t.ActualFinish) And Not IsDate(pred.ActualFinish) Then
                                'Note: Early Finish as it does not consider the data date.
                                TotalEarly = TotalEarly + pred.RemainingDuration ' Best guess.
                                TotalOOSEvents = TotalOOSEvents + 1
                                t.Text30 = t.Text30 & pred.UniqueID & ","
                            ElseIf t.TaskDependencies(predcount).Lag > 0 Then
                                If Application.DateAdd(pred.Finish, _
                                (t.TaskDependencies(predcount).Lag), _
                                ActiveProject.Calendar) > t.Finish Then
                                    Testdate = Application.DateAdd(pred.Finish, _
                                        (t.TaskDependencies(predcount).Lag))
                                    L = Application.DateDifference(t.Finish, Testdate, _
                                    ActiveProject.Calendar)
                                    If L > 0 Then
                                        TotalEarly = TotalEarly + L
                                    End If
                                    TotalOOSEvents = TotalOOSEvents + 1
                                    t.Text30 = t.Text30 & pred.UniqueID & ","
                                End If
                            ElseIf t.TaskDependencies(predcount).Lag < 0 Then
                                If Application.DateSubtract(pred.Finish, _
                                -(t.TaskDependencies(predcount).Lag), _
                                ActiveProject.Calendar) > t.Finish Then
                                    Testdate = Application.DateSubtract(pred.Finish, _
                                        Abs((t.TaskDependencies(predcount).Lag)))
                                    L = Application.DateDifference(t.Finish, Testdate, _
                                        ActiveProject.Calendar)
                                    If L > 0 Then
                                        TotalEarly = TotalEarly + L
                                    End If
                                    TotalOOSEvents = TotalOOSEvents + 1
                                    t.Text30 = t.Text30 & pred.UniqueID & ","
                                End If
                            ElseIf pred.Finish > t.Finish Then
                                Testdate = pred.Finish
                                L = Application.DateDifference(t.Finish, Testdate, _
                                    ActiveProject.Calendar)
                                If L > 0 Then
                                    TotalEarly = TotalEarly + L
                                End If
                                TotalOOSEvents = TotalOOSEvents + 1
                                t.Text30 = t.Text30 & pred.UniqueID & ","
                            End If
                        Case pjStartToFinish
                            If t.TaskDependencies(predcount).Lag > 0 Then
                                If Application.DateAdd(pred.Start, _
                                (t.TaskDependencies(predcount).Lag), _
                                ActiveProject.Calendar) < t.Finish Then
                                    Testdate = Application.DateAdd(pred.Start, _
                                        (t.TaskDependencies(predcount).Lag))
                                    L = Application.DateDifference(t.Finish, Testdate, _
                                        ActiveProject.Calendar)
                                    If L > 0 Then
                                        TotalEarly = TotalEarly + L
                                    End If
                                    TotalOOSEvents = TotalOOSEvents + 1
                                    t.Text30 = t.Text30 & pred.UniqueID & ","
                                End If
                            ElseIf t.TaskDependencies(predcount).Lag < 0 Then
                                If Application.DateSubtract(pred.Start, _
                                -(t.TaskDependencies(predcount).Lag), _
                                ActiveProject.Calendar) < t.Finish Then
                                    Testdate = Application.DateSubtract(pred.Start, _
                                        Abs((t.TaskDependencies(predcount).Lag)))
                                    L = Application.DateDifference(t.Finish, Testdate, _
                                        ActiveProject.Calendar)
                                    If L > 0 Then
                                        TotalEarly = TotalEarly + L
                                    End If
                                    TotalOOSEvents = TotalOOSEvents + 1
                                    t.Text30 = t.Text30 & pred.UniqueID & ","
                                End If
                            ElseIf pred.Start > t.Finish Then
                                Testdate = pred.Start
                                L = Application.DateDifference(t.Finish, Testdate, _
                                    ActiveProject.Calendar)
                                If L > 0 Then
                                    TotalEarly = TotalEarly + L
                                End If
                                TotalOOSEvents = TotalOOSEvents + 1
                                t.Text30 = t.Text30 & pred.UniqueID & ","
                            End If
                    End Select
                    predcount = predcount + 1
                Next pred
                If LastEventCount < TotalOOSEvents Then
                    'This activitiy started out-of-sequence
                    TotalOOSActs = TotalOOSActs + 1
                    L = Len(t.Text30)
                    If L > 0 Then
                        t.Text30 = Left(t.Text30, L - 1) ' Remove last comma
                    End If
                End If
            End If
        End If
        End If
    Next t
    If TotalTasks = 0 Then
        MsgBox ("There were no tasks to check.")
    Else
        If TotalOOSActs > 0 Then
            TotalEarly = TotalEarly / TotalOOSActs / 60 / ActiveProject.HoursPerDay
        End If
        MsgBox "Out-of-Sequence calculations are complete.  Look to Text30" & vbCrLf & _
              "for a list of predecessors to the out-of-sequence activity." & vbCrLf & vbCrLf & _
               "Out-of-Sequence progress statistics for this project:" & vbCrLf & _
               "    " & TotalTasks & " tasks were reviewed," & vbCrLf & _
               "    " & TotalOOSActs & " were out-of-sequence," & vbCrLf & _
               "    " & TotalOOSEvents & " out-of-sequence events occurred," & vbCrLf & _
               "    " & Format(TotalOOSActs / TotalTasks, "0%") & _
               " of tasks were out-of-sequence, and" & vbCrLf & _
               "    " & TotalEarly & " workdays average early start.", 64
    End If
Else
    MsgBox "No changes were made."
End If
OptionsCalculation AUTOMATIC:=True
End Sub