VBA编写系统故障自动进行分级分类判定

本文链接：https://blog.csdn.net/weixin_40908896/article/details/105681960

闲来无事，用vba写了一个EXCEL的小程序，根据系统名称、故障发生时间、是否工作时间段、以及恢复时间或截止时间，来判断系统的分类、累计中断时间、安全事件判定结果，以及随着时间的拉长可能会造成的事件升级。

Sub caculate()
Dim app As String
Dim break_down_time
Dim work_time

If Cells(3, 1) = "" Then
MsgBox "请选择发生故障系统！", vbOKOnly + vbInformation, "调度组提示您！"
ElseIf Cells(3, 2) = "" Then
MsgBox "请输入故障发生时间！", vbOKOnly + vbInformation, "调度组提示您！"
ElseIf Cells(3, 3) = "" Then
MsgBox "请选择故障是否发生在工作时间段！", vbOKOnly + vbInformation, "调度组提示您！"
End If


app = Sheets(1).Range("A3")
break_down_time = Sheets(1).Range("B3")
work_time = Sheets(1).Range("C3")
recovery_time = Sheets(1).Range("D3")



'判断恢复时间
If recovery_time = "" Then
recovery_time = Now
End If
'计算中断时间
Dim count_interrupt_time As Integer
count_interrupt_time = CInt(DateDiff("n", break_down_time, recovery_time))
'MsgBox TypeName(count_interrupt_time)
'MsgBox count_interrupt_time
If break_down_time <> "" Then
Cells(3, 6) = count_interrupt_time
End If
'判断系统分类
Select Case app
Case "ERP": Cells(3, 5) = "二类系统"
Case "ERP高级应用": Cells(3, 5) = "三类系统"
Case "内网网站": Cells(3, 5) = "二类系统"
Case "统一交换": Cells(3, 5) = "三类系统"
Case "通信管理": Cells(3, 5) = "监控类系统"
Case "企业门户": Cells(3, 5) = "二类系统"
Case "网络系统": Cells(3, 5) = "-----"
End Select
'判断事件等级
system_category = Sheets(1).Range("E3")
Select Case system_category
Case "二类系统": Call II_category_system
Case "三类系统": Call III_category_system
Case "监控类系统": Call monitor_category_system
Case "-----": Call network_system
End Select

End Sub

Sub II_category_system()
Dim count_interrupt_time As Long
count_interrupt_time = Sheets(1).Range("F3")
'MsgBox TypeName(360)
'MsgBox count_interrupt_time
'MsgBox TypeName(180)
If Cells(3, 3) = "否" Then
If count_interrupt_time < 180 Then
Cells(3, 7) = "B类故障"
Cells(3, 8) = "还有" & 180 - count_interrupt_time & "分钟，将会升级为八级事件。"
ElseIf count_interrupt_time > 180 And count_interrupt_time < 360 Then
Cells(3, 7) = "八级事件"
Cells(3, 8) = "还有" & 360 - count_interrupt_time & "分钟，将会升级为七级事件。"
ElseIf count_interrupt_time > 360 And count_interrupt_time < 720 Then
Cells(3, 7) = "七级事件"
Cells(3, 8) = "还有" & 720 - count_interrupt_time & "分钟，将会升级为六级事件。"
ElseIf count_interrupt_time > 720 And count_interrupt_time < 1440 Then
Cells(3, 7) = "六级事件"
Cells(3, 8) = "还有" & 1440 - count_interrupt_time & "分钟，将会升级为五级事件。"
ElseIf count_interrupt_time > 1440 Then
Cells(3, 7) = "五级事件"
Cells(3, 8) = "最高事件等级为五级事件。"
End If
ElseIf Cells(3, 3) = "是" Then
If count_interrupt_time < 180 Then
Cells(3, 7) = "A类故障"
Cells(3, 8) = "还有" & 180 - count_interrupt_time & "分钟，将会升级为八级事件。"
ElseIf count_interrupt_time > 180 And count_interrupt_time < 360 Then
Cells(3, 7) = "八级事件"
Cells(3, 8) = "还有" & 360 - count_interrupt_time & "分钟，将会升级为七级事件。"
ElseIf count_interrupt_time > 360 And count_interrupt_time < 720 Then
Cells(3, 7) = "七级事件"
Cells(3, 8) = "还有" & 720 - count_interrupt_time & "分钟，将会升级为六级事件。"
ElseIf count_interrupt_time > 720 And count_interrupt_time < 1440 Then
Cells(3, 7) = "六级事件"
Cells(3, 8) = "还有" & 1440 - count_interrupt_time & "分钟，将会升级为五级事件。"
ElseIf count_interrupt_time > 1440 Then
Cells(3, 7) = "五级事件"
Cells(3, 8) = "最高事件等级为五级事件。"
End If
End If
End Sub

Sub III_category_system()
Dim count_interrupt_time
count_interrupt_time = Sheets(1).Range("F3")
If Cells(3, 3) = "否" Then
If count_interrupt_time < 540 Then
Cells(3, 7) = "D类故障"
Cells(3, 8) = "还有" & 540 - count_interrupt_time & "分钟，将会升级为八级事件。"
ElseIf count_interrupt_time > 540 And count_interrupt_time < 1080 Then
Cells(3, 7) = "八级事件"
Cells(3, 8) = "还有" & 1080 - count_interrupt_time & "分钟，将会升级为七级事件。"
ElseIf count_interrupt_time > 1080 And count_interrupt_time < 2160 Then
Cells(3, 7) = "七级事件"
Cells(3, 8) = "还有" & 2160 - count_interrupt_time & "分钟，将会升级为六级事件。"
ElseIf count_interrupt_time > 2160 And count_interrupt_time < 4320 Then
Cells(3, 7) = "六级事件"
Cells(3, 8) = "还有" & 4320 - count_interrupt_time & "分钟，将会升级为五级事件。"
ElseIf count_interrupt_time > 4320 Then
Cells(3, 7) = "五级事件"
Cells(3, 8) = "最高事件等级为五级事件。"
End If
ElseIf Cells(3, 3) = "是" Then
If count_interrupt_time < 540 Then
Cells(3, 7) = "C&B类故障" '此处需要进行核实
Cells(3, 8) = "还有" & 540 - count_interrupt_time & "分钟，将会升级为八级事件。"
ElseIf count_interrupt_time > 540 And count_interrupt_time < 1080 Then
Cells(3, 7) = "八级事件"
Cells(3, 8) = "还有" & 1080 - count_interrupt_time & "分钟，将会升级为七级事件。"
ElseIf count_interrupt_time > 1080 And count_interrupt_time < 2160 Then
Cells(3, 7) = "七级事件"
Cells(3, 8) = "还有" & 2160 - count_interrupt_time & "分钟，将会升级为六级事件。"
ElseIf count_interrupt_time > 2160 And count_interrupt_time < 4320 Then
Cells(3, 7) = "六级事件"
Cells(3, 8) = "还有" & 4320 - count_interrupt_time & "分钟，将会升级为五级事件。"
ElseIf count_interrupt_time > 4320 Then
Cells(3, 7) = "五级事件"
Cells(3, 8) = "最高事件等级为五级事件。"
End If
End If
End Sub

Sub monitor_category_system()
Cells(3, 7) = "A类故障"
End Sub

Sub network_system()
Dim count_interrupt_time
count_interrupt_time = Sheets(1).Range("F3")
If count_interrupt_time < 60 Then
Cells(3, 7) = "B类故障"
Cells(3, 8) = "还有" & 60 - count_interrupt_time & "分钟，将会升级为七级事件。"
ElseIf count_interrupt_time > 60 And count_interrupt_time < 240 Then
Cells(3, 7) = "七级事件"
Cells(3, 8) = "还有" & 240 - count_interrupt_time & "分钟，将会升级为六级事件。"
ElseIf count_interrupt_time > 240 And count_interrupt_time < 480 Then
Cells(3, 7) = "六级事件"
Cells(3, 8) = "还有" & 480 - count_interrupt_time & "分钟，将会升级为五级事件。"
ElseIf count_interrupt_time > 480 Then
Cells(3, 7) = "五级事件"
Cells(3, 8) = "最高事件等级为五级事件。"
End If
End Sub

Sub reset()
Dim i%
For i = 1 To 8
Cells(3, i) = ""
Next
End Sub