Excel VBA评分系统仅录入1-2份成绩时无法正常运行的问题排查

问题说明 #

开发的Excel VBA评分报告系统需支持1-3份试卷的评分，但当前代码仅在录入全部3份成绩时正常运行，单份或双份成绩录入时无法正确输出评分。

原代码问题分析 #

1. 等级判断逻辑缺失：仅单份成绩时处理了8分对应O等级的规则，双份/三份成绩中存在单个8分时，会错误返回F等级
2. 输入校验不足：仅通过IsEmpty判断单元格是否有值，未校验内容是否为有效数值，非数字内容会干扰评分
3. 代码可读性差：变量声明紧凑在一行，降低了代码维护性

修复后的代码 #

Sub gradeLuganda()
    Dim grades As Variant
    Dim grade As String
    Dim containsNine As Boolean
    Dim countEights As Integer
    Dim validGrades() As Variant
    Dim i As Integer, j As Integer
    
    ' 初始化有效成绩数组
    ReDim validGrades(0 To 2) As Variant
    j = 0
    
    ' 填充有效成绩：仅收集非空且为数值的内容
    If Trim(Range("G29").Value) <> "" And IsNumeric(Range("G29").Value) Then
        validGrades(j) = Range("G29").Value
        j = j + 1
    End If
    If Trim(Range("G30").Value) <> "" And IsNumeric(Range("G30").Value) Then
        validGrades(j) = Range("G30").Value
        j = j + 1
    End If
    If Trim(Range("G31").Value) <> "" And IsNumeric(Range("G31").Value) Then
        validGrades(j) = Range("G31").Value
        j = j + 1
    End If
    
    ' 无有效成绩时清空输出并退出
    If j = 0 Then
        Range("H29").Value = ""
        Exit Sub
    End If
    
    ' 调整数组大小为有效成绩数量
    ReDim Preserve validGrades(0 To j - 1)
    
    ' 统计9分和8分的数量
    containsNine = False
    countEights = 0
    For i = LBound(validGrades) To UBound(validGrades)
        If validGrades(i) = 9 Then
            containsNine = True
        ElseIf validGrades(i) = 8 Then
            countEights = countEights + 1
        End If
    Next i
    
    ' 调试输出（可按需保留）
    Debug.Print "Valid Grades: "; Join(validGrades, ", ")
    Debug.Print "Contains Nine: "; containsNine
    Debug.Print "Count of Eights: "; countEights
    
    ' 等级判断逻辑
    Select Case True
        ' 存在9分或2个及以上8分 → F
        Case containsNine Or countEights >= 2
            grade = "F"
        ' 存在单个8分 → O（覆盖1/2/3份成绩的场景）
        Case countEights = 1
            grade = "O"
        ' 单份成绩的评分规则
        Case j = 1
            Select Case validGrades(0)
                Case 1 To 3: grade = "B"
                Case 4: grade = "C"
                Case 5: grade = "D"
                Case 6 To 7: grade = "E"
            End Select
        ' 双份成绩的评分规则
        Case j = 2
            Select Case True
                Case validGrades(0) <= 3 And validGrades(1) <= 3: grade = "B"
                Case validGrades(0) <= 4 And validGrades(1) <= 4: grade = "C"
                Case validGrades(0) <= 5 And validGrades(1) <= 5: grade = "D"
                Case validGrades(0) <= 7 And validGrades(1) <= 7: grade = "E"
                Case Else: grade = "F"
            End Select
        ' 三份成绩的评分规则
        Case j = 3
            Select Case True
                Case validGrades(0) <= 3 And validGrades(1) <= 3 And validGrades(2) <= 3: grade = "A"
                Case (validGrades(0) <= 4 And validGrades(1) <= 3 And validGrades(2) <= 3) Or _
                     (validGrades(0) <= 3 And validGrades(1) <= 4 And validGrades(2) <= 3) Or _
                     (validGrades(0) <= 3 And validGrades(1) <= 3 And validGrades(2) <= 4): grade = "B"
                Case (validGrades(0) <= 5 And validGrades(1) <= 4 And validGrades(2) <= 4) Or _
                     (validGrades(0) <= 4 And validGrades(1) <= 5 And validGrades(2) <= 4) Or _
                     (validGrades(0) <= 4 And validGrades(1) <= 4 And validGrades(2) <= 5): grade = "C"
                Case (validGrades(0) <= 6 And validGrades(1) <= 5 And validGrades(2) <= 5) Or _
                     (validGrades(0) <= 5 And validGrades(1) <= 6 And validGrades(2) <= 5) Or _
                     (validGrades(0) <= 5 And validGrades(1) <= 5 And validGrades(2) <= 6): grade = "D"
                Case validGrades(0) <= 7 And validGrades(1) <= 7 And validGrades(2) <= 7: grade = "E"
                Case Else: grade = "F"
            End Select
        ' 默认情况 → F
        Case Else
            grade = "F"
    End Select
    
    ' 输出最终等级
    Range("H29").Value = grade
End Sub

关键修改点 #

1. 新增单个8分的统一处理：在等级判断中加入countEights = 1的分支，确保1/2/3份成绩中只要有单个8分就返回O等级
2. 增强输入校验：新增Trim(...) <> ""和IsNumeric(...)判断，仅收集有效数值成绩
3. 优化代码可读性：拆分变量声明为多行，调整分支缩进格式
4. 保留原核心评分规则：未改动单/双/三份成绩的基础等级判断逻辑，仅补全缺失的8分场景

内容的提问来源于stack exchange，提问作者ssekatawa