ValidationModule.bas

Attribute VB_Name = "ValidationModule"
' Manufacturing Workflow Scheduler - Validation Module
' Created: April 2025
'
' This module contains validation and testing functions to ensure data integrity
' and correct workflow definitions, following software quality best practices.

Option Explicit

' Validate imported data
Public Function ValidateImportedData() As Boolean
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim errors As Collection
    Dim result As Boolean
    
    On Error GoTo ErrorHandler
    
    Set errors = New Collection
    result = True
    
    ' Set reference to data sheet
    Set ws = ThisWorkbook.Sheets("ImportedData")
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    
    ' Check if data exists
    If lastRow <= 1 Then
        errors.Add "No data found in the ImportedData sheet."
        result = False
        GoTo Finalize
    End If
    
    ' Validate required columns
    If ws.Range("A1").Value <> "WorkOrder" Then errors.Add "Column A should be 'WorkOrder'"
    If ws.Range("B1").Value <> "Product" Then errors.Add "Column B should be 'Product'"
    If ws.Range("C1").Value <> "Quantity" Then errors.Add "Column C should be 'Quantity'"
    If ws.Range("D1").Value <> "StartDate" Then errors.Add "Column D should be 'StartDate'"
    If ws.Range("E1").Value <> "DueDate" Then errors.Add "Column E should be 'DueDate'"
    If ws.Range("F1").Value <> "Priority" Then errors.Add "Column F should be 'Priority'"
    
    If errors.Count > 0 Then
        result = False
        GoTo Finalize
    End If
    
    ' Validate data rows
    For i = 2 To lastRow
        ' Check for empty cells in required fields
        If Trim(ws.Cells(i, 1).Value) = "" Then
            errors.Add "Row " & i & ": WorkOrder is required"
            result = False
        End If
        
        If Trim(ws.Cells(i, 2).Value) = "" Then
            errors.Add "Row " & i & ": Product is required"
            result = False
        End If
        
        If Trim(ws.Cells(i, 3).Value) = "" Or Not IsNumeric(ws.Cells(i, 3).Value) Then
            errors.Add "Row " & i & ": Quantity must be a number"
            result = False
        End If
        
        ' Validate dates
        If Not IsDate(ws.Cells(i, 4).Value) Then
            errors.Add "Row " & i & ": StartDate must be a valid date"
            result = False
        End If
        
        If Not IsDate(ws.Cells(i, 5).Value) Then
            errors.Add "Row " & i & ": DueDate must be a valid date"
            result = False
        End If
        
        ' Check logical constraints
        If IsDate(ws.Cells(i, 4).Value) And IsDate(ws.Cells(i, 5).Value) Then
            If CDate(ws.Cells(i, 4).Value) > CDate(ws.Cells(i, 5).Value) Then
                errors.Add "Row " & i & ": StartDate cannot be after DueDate"
                result = False
            End If
        End If
    Next i
    
Finalize:
    ' Display errors if any
    If Not result Then
        Dim errorMsg As String
        errorMsg = "Data validation failed with the following errors:" & vbCrLf & vbCrLf
        
        For i = 1 To errors.Count
            errorMsg = errorMsg & "• " & errors(i) & vbCrLf
        Next i
        
        MsgBox errorMsg, vbExclamation, "Validation Errors"
    End If
    
    ValidateImportedData = result
    Exit Function
    
ErrorHandler:
    MsgBox "Error validating data: " & Err.Description, vbExclamation
    ValidateImportedData = False
End Function

' Validate workflow definition
Public Function ValidateWorkflowDefinition() As Boolean
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long, j As Long
    Dim errors As Collection
    Dim result As Boolean
    Dim stepIds As Collection
    
    On Error GoTo ErrorHandler
    
    Set errors = New Collection
    Set stepIds = New Collection
    result = True
    
    ' Set reference to workflow sheet
    Set ws = ThisWorkbook.Sheets("WorkflowSetup")
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    
    ' Check if data exists
    If lastRow <= 1 Then
        errors.Add "No workflow steps found in the WorkflowSetup sheet."
        result = False
        GoTo Finalize
    End If
    
    ' Validate required columns
    If ws.Range("A1").Value <> "StepID" Then errors.Add "Column A should be 'StepID'"
    If ws.Range("B1").Value <> "StepName" Then errors.Add "Column B should be 'StepName'"
    If ws.Range("C1").Value <> "Department" Then errors.Add "Column C should be 'Department'"
    If ws.Range("D1").Value <> "Duration" Then errors.Add "Column D should be 'Duration'"
    If ws.Range("E1").Value <> "Prerequisites" Then errors.Add "Column E should be 'Prerequisites'"
    If ws.Range("F1").Value <> "ResourceRequired" Then errors.Add "Column F should be 'ResourceRequired'"
    If ws.Range("G1").Value <> "SetupTime" Then errors.Add "Column G should be 'SetupTime'"
    
    If errors.Count > 0 Then
        result = False
        GoTo Finalize
    End If
    
    ' Collect all step IDs
    On Error Resume Next
    For i = 2 To lastRow
        If Trim(ws.Cells(i, 1).Value) <> "" Then
            stepIds.Add ws.Cells(i, 1).Value, CStr(ws.Cells(i, 1).Value)
        End If
    Next i
    On Error GoTo ErrorHandler
    
    ' Validate data rows
    For i = 2 To lastRow
        ' Check for empty cells in required fields
        If Trim(ws.Cells(i, 1).Value) = "" Then
            errors.Add "Row " & i & ": StepID is required"
            result = False
        End If
        
        If Trim(ws.Cells(i, 2).Value) = "" Then
            errors.Add "Row " & i & ": StepName is required"
            result = False
        End If
        
        If Trim(ws.Cells(i, 3).Value) = "" Then
            errors.Add "Row " & i & ": Department is required"
            result = False
        End If
        
        If Trim(ws.Cells(i, 4).Value) = "" Or Not IsNumeric(ws.Cells(i, 4).Value) Then
            errors.Add "Row " & i & ": Duration must be a number"
            result = False
        ElseIf CDbl(ws.Cells(i, 4).Value) <= 0 Then
            errors.Add "Row " & i & ": Duration must be greater than zero"
            result = False
        End If
        
        If Trim(ws.Cells(i, 6).Value) = "" Then
            errors.Add "Row " & i & ": ResourceRequired is required"
            result = False
        End If
        
        If Trim(ws.Cells(i, 7).Value) = "" Or Not IsNumeric(ws.Cells(i, 7).Value) Then
            errors.Add "Row " & i & ": SetupTime must be a number"
            result = False
        ElseIf CDbl(ws.Cells(i, 7).Value) < 0 Then
            errors.Add "Row " & i & ": SetupTime cannot be negative"
            result = False
        End If
        
        ' Validate prerequisites
        If Trim(ws.Cells(i, 5).Value) <> "" Then
            Dim prereqs As Variant
            prereqs = Split(ws.Cells(i, 5).Value, ",")
            
            For j = 0 To UBound(prereqs)
                Dim prereq As String
                prereq = Trim(prereqs(j))
                
                ' Check if prerequisite exists
                Dim prereqExists As Boolean
                prereqExists = False
                
                On Error Resume Next
                If stepIds(prereq) <> "" Then prereqExists = True
                On Error GoTo ErrorHandler
                
                If Not prereqExists Then
                    errors.Add "Row " & i & ": Prerequisite '" & prereq & "' is not defined in the workflow"
                    result = False
                End If
            Next j
        End If
    Next i
    
    ' Check for circular dependencies
    If Not CheckCircularDependencies(ws, stepIds, errors) Then
        result = False
    End If
    
Finalize:
    ' Display errors if any
    If Not result Then
        Dim errorMsg As String
        errorMsg = "Workflow validation failed with the following errors:" & vbCrLf & vbCrLf
        
        For i = 1 To errors.Count
            errorMsg = errorMsg & "• " & errors(i) & vbCrLf
        Next i
        
        MsgBox errorMsg, vbExclamation, "Validation Errors"
    End If
    
    ValidateWorkflowDefinition = result
    Exit Function
    
ErrorHandler:
    MsgBox "Error validating workflow: " & Err.Description, vbExclamation
    ValidateWorkflowDefinition = False
End Function

' Check for circular dependencies in workflow
Private Function CheckCircularDependencies(ws As Worksheet, stepIds As Collection, errors As Collection) As Boolean
    Dim lastRow As Long
    Dim i As Long
    Dim dependencyMap As Object
    Dim result As Boolean
    
    result = True
    Set dependencyMap = CreateObject("Scripting.Dictionary")
    
    ' Build dependency map
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    
    For i = 2 To lastRow
        Dim stepId As String
        Dim prerequisites As String
        
        stepId = ws.Cells(i, 1).Value
        prerequisites = ws.Cells(i, 5).Value
        
        If Trim(prerequisites) <> "" Then
            dependencyMap.Add stepId, prerequisites
        Else
            dependencyMap.Add stepId, ""
        End If
    Next i
    
    ' Check each step for circular dependencies
    Dim key As Variant
    For Each key In dependencyMap.Keys
        Dim visited As Object
        Set visited = CreateObject("Scripting.Dictionary")
        
        If HasCircularDependency(key, dependencyMap, visited) Then
            errors.Add "Circular dependency detected involving step '" & key & "'"
            result = False
        End If
    Next key
    
    CheckCircularDependencies = result
End Function

' Check if a step has circular dependencies
Private Function HasCircularDependency(stepId As String, dependencyMap As Object, visited As Object) As Boolean
    ' Mark as visited
    visited.Add stepId, True
    
    ' Check prerequisites
    If Trim(dependencyMap(stepId)) <> "" Then
        Dim prereqs As Variant
        prereqs = Split(dependencyMap(stepId), ",")
        
        Dim i As Long
        For i = 0 To UBound(prereqs)
            Dim prereq As String
            prereq = Trim(prereqs(i))
            
            ' Check if already visited
            If visited.Exists(prereq) Then
                HasCircularDependency = True
                Exit Function
            End If
            
            ' Check recursively
            If dependencyMap.Exists(prereq) Then
                Dim newVisited As Object
                Set newVisited = CloneVisited(visited)
                
                If HasCircularDependency(prereq, dependencyMap, newVisited) Then
                    HasCircularDependency = True
                    Exit Function
                End If
            End If
        Next i
    End If
    
    HasCircularDependency = False
End Function

' Clone visited dictionary
Private Function CloneVisited(visited As Object) As Object
    Dim newVisited As Object
    Set newVisited = CreateObject("Scripting.Dictionary")
    
    Dim key As Variant
    For Each key In visited.Keys
        newVisited.Add key, visited(key)
    Next key
    
    Set CloneVisited = newVisited
End Function

' Run validation tests
Public Sub RunAllValidations()
    Dim dataValid As Boolean
    Dim workflowValid As Boolean
    
    ' Validate imported data
    dataValid = ValidateImportedData()
    
    ' Validate workflow
    workflowValid = ValidateWorkflowDefinition()
    
    ' Show result
    If dataValid And workflowValid Then
        MsgBox "All validations passed successfully.", vbInformation, "Validation Complete"
    Else
        MsgBox "Validation failed. Please fix the errors before continuing.", vbExclamation, "Validation Failed"
    End If
End Sub

' Test data generation for quick testing
Public Sub GenerateTestData()
    Dim wsData As Worksheet
    Dim wsWorkflow As Worksheet
    
    ' Create test data sheet if needed
    If Not SheetExists("ImportedData") Then
        ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)).Name = "ImportedData"
    End If
    Set wsData = ThisWorkbook.Sheets("ImportedData")
    
    ' Clear existing data
    wsData.UsedRange.ClearContents
    
    ' Create test workflow sheet if needed
    If Not SheetExists("WorkflowSetup") Then
        ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)).Name = "WorkflowSetup"
    End If
    Set wsWorkflow = ThisWorkbook.Sheets("WorkflowSetup")
    
    ' Clear existing data
    wsWorkflow.UsedRange.ClearContents
    
    ' Add test data headers
    wsData.Range("A1").Value = "WorkOrder"
    wsData.Range("B1").Value = "Product"
    wsData.Range("C1").Value = "Quantity"
    wsData.Range("D1").Value = "StartDate"
    wsData.Range("E1").Value = "DueDate"
    wsData.Range("F1").Value = "Priority"
    
    ' Add test workflow headers
    wsWorkflow.Range("A1").Value = "StepID"
    wsWorkflow.Range("B1").Value = "StepName"
    wsWorkflow.Range("C1").Value = "Department"
    wsWorkflow.Range("D1").Value = "Duration"
    wsWorkflow.Range("E1").Value = "Prerequisites"
    wsWorkflow.Range("F1").Value = "ResourceRequired"
    wsWorkflow.Range("G1").Value = "SetupTime"
    
    ' Add test data rows
    wsData.Range("A2").Value = "WO-001"
    wsData.Range("B2").Value = "Widget A"
    wsData.Range("C2").Value = 100
    wsData.Range("D2").Value = Date
    wsData.Range("E2").Value = DateAdd("d", 15, Date)
    wsData.Range("F2").Value = "High"
    
    wsData.Range("A3").Value = "WO-002"
    wsData.Range("B3").Value = "Widget B"
    wsData.Range("C3").Value = 50
    wsData.Range("D3").Value = DateAdd("d", 2, Date)
    wsData.Range("E3").Value = DateAdd("d", 20, Date)
    wsData.Range("F3").Value = "Medium"
    
    wsData.Range("A4").Value = "WO-003"
    wsData.Range("B4").Value = "Assembly C"
    wsData.Range("C4").Value = 75
    wsData.Range("D4").Value = DateAdd("d", 5, Date)
    wsData.Range("E4").Value = DateAdd("d", 25, Date)
    wsData.Range("F4").Value = "High"
    
    ' Add test workflow rows
    wsWorkflow.Range("A2").Value = "S001"
    wsWorkflow.Range("B2").Value = "Material Preparation"
    wsWorkflow.Range("C2").Value = "Warehouse"
    wsWorkflow.Range("D2").Value = 2
    wsWorkflow.Range("E2").Value = ""
    wsWorkflow.Range("F2").Value = "Material Handler"
    wsWorkflow.Range("G2").Value = 0.5
    
    wsWorkflow.Range("A3").Value = "S002"
    wsWorkflow.Range("B3").Value = "Cutting"
    wsWorkflow.Range("C3").Value = "Fabrication"
    wsWorkflow.Range("D3").Value = 1
    wsWorkflow.Range("E3").Value = "S001"
    wsWorkflow.Range("F3").Value = "CNC Operator"
    wsWorkflow.Range("G3").Value = 1
    
    wsWorkflow.Range("A4").Value = "S003"
    wsWorkflow.Range("B4").Value = "Milling"
    wsWorkflow.Range("C4").Value = "Fabrication"
    wsWorkflow.Range("D4").Value = 3
    wsWorkflow.Range("E4").Value = "S002"
    wsWorkflow.Range("F4").Value = "Machinist"
    wsWorkflow.Range("G4").Value = 0.5
    
    wsWorkflow.Range("A5").Value = "S004"
    wsWorkflow.Range("B5").Value = "Drilling"
    wsWorkflow.Range("C5").Value = "Fabrication"
    wsWorkflow.Range("D5").Value = 2
    wsWorkflow.Range("E5").Value = "S002"
    wsWorkflow.Range("F5").Value = "Drill Operator"
    wsWorkflow.Range("G5").Value = 0.5
    
    wsWorkflow.Range("A6").Value = "S005"
    wsWorkflow.Range("B6").Value = "Assembly A"
    wsWorkflow.Range("C6").Value = "Assembly"
    wsWorkflow.Range("D6").Value = 4
    wsWorkflow.Range("E6").Value = "S003,S004"
    wsWorkflow.Range("F6").Value = "Assembler"
    wsWorkflow.Range("G6").Value = 1
    
    wsWorkflow.Range("A7").Value = "S006"
    wsWorkflow.Range("B7").Value = "Painting"
    wsWorkflow.Range("C7").Value = "Finishing"
    wsWorkflow.Range("D7").Value = 2
    wsWorkflow.Range("E7").Value = "S005"
    wsWorkflow.Range("F7").Value = "Painter"
    wsWorkflow.Range("G7").Value = 0.5
    
    wsWorkflow.Range("A8").Value = "S007"
    wsWorkflow.Range("B8").Value = "Quality Inspection"
    wsWorkflow.Range("C8").Value = "QA"
    wsWorkflow.Range("D8").Value = 1
    wsWorkflow.Range("E8").Value = "S006"
    wsWorkflow.Range("F8").Value = "QA Inspector"
    wsWorkflow.Range("G8").Value = 0.25
    
    wsWorkflow.Range("A9").Value = "S008"
    wsWorkflow.Range("B9").Value = "Packaging"
    wsWorkflow.Range("C9").Value = "Shipping"
    wsWorkflow.Range("D9").Value = 1
    wsWorkflow.Range("E9").Value = "S007"
    wsWorkflow.Range("F9").Value = "Packer"
    wsWorkflow.Range("G9").Value = 0.5
    
    ' Format the data
    FormatTestData wsData, wsWorkflow
    
    MsgBox "Test data generated successfully.", vbInformation, "Test Data"
End Sub

' Format test data
Private Sub FormatTestData(wsData As Worksheet, wsWorkflow As Worksheet)
    ' Format data sheet
    With wsData.Range("A1:F1")
        .Font.Bold = True
        .Interior.Color = RGB(200, 200, 200)
    End With
    
    With wsData.Range("D:E")
        .NumberFormat = "yyyy-mm-dd"
    End With
    
    wsData.UsedRange.Columns.AutoFit
    
    ' Format workflow sheet
    With wsWorkflow.Range("A1:G1")
        .Font.Bold = True
        .Interior.Color = RGB(200, 200, 200)
    End With
    
    wsWorkflow.Range("D:D,G:G").NumberFormat = "0.00"
    
    wsWorkflow.UsedRange.Columns.AutoFit
End Sub

' Check if a sheet exists
Private Function SheetExists(sheetName As String) As Boolean
    Dim ws As Worksheet
    
    On Error Resume Next
    Set ws = ThisWorkbook.Sheets(sheetName)
    On Error GoTo 0
    
    SheetExists = Not ws Is Nothing
End Function