Sub CompareASILLevels()
Dim ws As Worksheet, reportWs As Worksheet
' Ensure the specific input sheet exists
On Error Resume Next
Set ws = Worksheets("ASIL_Data")
On Error GoTo 0
If ws Is Nothing Then
MsgBox "Error: 'ASIL_Data' sheet not found.", vbExclamation
Exit Sub
End If
' Check if the report sheet exists, create or clear it
On Error Resume Next
Set reportWs = Worksheets("ASIL_Report")
On Error GoTo 0
If reportWs Is Nothing Then
Set reportWs = Worksheets.Add
reportWs.Name = "ASIL_Report"
Else
reportWs.Cells.Clear
End If
' Set report headers
reportWs.Cells(1, 1).Value = "ID"
reportWs.Cells(1, 2).Value = "Receiver Arch Element"
reportWs.Cells(1, 3).Value = "SubElement"
reportWs.Cells(1, 4).Value = "Receiver ASIL"
reportWs.Cells(1, 5).Value = "Sender ASIL"
reportWs.Cells(1, 6).Value = "Sender Arch Element"
reportWs.Cells(1, 7).Value = "Status"
' ASIL priority mapping
Dim asilPriority As Object
Set asilPriority = CreateObject("Scripting.Dictionary")
asilPriority("ASIL D") = 5
asilPriority("ASIL C") = 4
asilPriority("ASIL B") = 3
asilPriority("ASIL A") = 2
asilPriority("QM") = 1
asilPriority("-") = 0
Dim i As Long, j As Long, reportRow As Long
reportRow = 2
' Loop through Receivers
For i = 2 To ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
If Trim(ws.Cells(i, 5).Value) = "Reciever" Then
Dim receiverASIL As String, receiverSubElement As String
receiverASIL = Trim(ws.Cells(i, 4).Value)
receiverSubElement = Trim(ws.Cells(i, 3).Value)
Dim senderFound As Boolean: senderFound = False
' Match with all Senders
For j = 2 To ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
If Trim(ws.Cells(j, 5).Value) = "Sender" And Trim(ws.Cells(j, 3).Value) = receiverSubElement Then
senderFound = True
Dim senderASIL As String, senderArchElement As String
senderASIL = Trim(ws.Cells(j, 4).Value)
senderArchElement = Trim(ws.Cells(j, 2).Value)
' Populate report
reportWs.Cells(reportRow, 1).Value = ws.Cells(i, 1).Value
reportWs.Cells(reportRow, 2).Value = ws.Cells(i, 2).Value
reportWs.Cells(reportRow, 3).Value = receiverSubElement
reportWs.Cells(reportRow, 4).Value = receiverASIL
reportWs.Cells(reportRow, 5).Value = senderASIL
reportWs.Cells(reportRow, 6).Value = senderArchElement
' ASIL Check
If IsEmpty(senderASIL) Or IsEmpty(receiverASIL) Then
reportWs.Cells(reportRow, 7).Value = "Error: Missing ASIL Level"
ElseIf senderASIL = "-" Or receiverASIL = "-" Then
reportWs.Cells(reportRow, 7).Value = "Error: Missing ASIL Level"
ElseIf asilPriority(senderASIL) < asilPriority(receiverASIL) Then
reportWs.Cells(reportRow, 7).Value = "Invalid: Sender ASIL is lower"
Else
reportWs.Cells(reportRow, 7).Value = "Valid"
End If
reportRow = reportRow + 1
End If
Next j
' Handle missing Sender
If Not senderFound Then
reportWs.Cells(reportRow, 1).Value = ws.Cells(i, 1).Value
reportWs.Cells(reportRow, 2).Value = ws.Cells(i, 2).Value
reportWs.Cells(reportRow, 3).Value = receiverSubElement
reportWs.Cells(reportRow, 4).Value = receiverASIL
reportWs.Cells(reportRow, 5).Value = "Not Found"
reportWs.Cells(reportRow, 6).Value = "Not Found"
reportWs.Cells(reportRow, 7).Value = "Error: No matching Sender"
reportRow = reportRow + 1
End If
End If
Next i
' Auto-arrange the report
With reportWs
.Columns("A:G").AutoFit
.Rows("1:1").Font.Bold = True
.Sort.SortFields.Clear
.Sort.SortFields.Add Key:=.Range("B:B"), Order:=xlAscending
.Sort.SortFields.Add Key:=.Range("C:C"), Order:=xlAscending
.Sort.SetRange .Range("A:G")
.Sort.Header = xlYes
.Sort.Apply
End With
MsgBox "ASIL Level Comparison and Report Generation Completed", vbInformation
End Sub
