VBA宏需求：循环中整合全局变量至单元格并标记错误所属模块

需求说明 #

现有两个全局变量ErrorMsg和SectionName，需实现VBA宏以下功能：

• 代码运行时，若ErrorMsg被赋值，需列出对应SectionName及模块产生的错误信息
• 支持识别并列出多模块下的多个错误值
• 输出格式示例：Complete with Error - Section: **Wavelength** - Wavelength column index could not be found, Section: **Power** - data could not be found（Section名称需加粗）

现有代码问题分析 #

1. 错误信息未关联SectionName，无法区分不同模块的错误来源
2. Main过程中未在每次循环开始时重置ErrorMsg，会导致错误信息跨条目累积
3. Wavelength和Power子过程的错误处理未正确捕获并关联Section的错误
4. Main过程中存在拼写错误（"Complete with Erorr"）

修改后的完整代码 #

全局变量定义 #

Global ErrorMsg As String, SectionName As String

Main主过程 #

Sub Main()
    Dim cell As Range, ws As Worksheet, sysnum As String, sysrow As Integer, wb As Workbook, logsht As Worksheet
    Dim begincell As Long
    
    Set wb = ActiveWorkbook
    Set ws = ActiveSheet ' 修正原代码的ActiveWorksheet拼写错误
    Set logsht = wb.Worksheets("Log Sheet")
    
    For Each cell In ws.Range("E2", ws.Cells(ws.Rows.Count, "E").End(xlUp)).Cells
        sysnum = cell.Value
        sysrow = cell.Row
        
        ' 每次处理新条目时重置错误信息，避免累积
        ErrorMsg = ""
        
        ' 调用各模块子过程
        Call Wavelength
        Call Power
        
        With logsht
            begincell = .Cells(Rows.Count, 1).End(xlUp).Row
            .Cells(begincell + 1, 2).Value = sysnum
            .Cells(begincell + 1, 2).Font.Bold = True
            
            If Not ErrorMsg = "" Then
                ' 拼接带Section标识的错误信息，Section名称用**标注格式
                .Cells(begincell + 1, 3).Value = "Complete with Error - " & ErrorMsg
                .Cells(begincell + 1, 3).Font.Bold = True
                .Cells(begincell + 1, 3).Interior.Color = vbRed
            Else
                .Cells(begincell + 1, 3).Value = "Completed without Errors"
                .Cells(begincell + 1, 3).Font.Bold = True
                .Cells(begincell + 1, 3).Interior.Color = vbGreen
            End If
        End With
    Next cell
End Sub

Wavelength模块子过程 #

Sub Wavelength()
    Dim wavelength_col As Long, wavelength_value As Double
    SectionName = "Wavelength"
    On Error GoTo errormessage
    
    wavelength_col = GetColumnIndex(ws, "Wavelength (nm)")
    wavelength_value = Getdata(ws, sysrow, wavelength_col)
    
    Exit Sub
errormessage:
    ' 捕获运行时错误并关联SectionName
    ErrorMsg = ErrorMsg & IIf(ErrorMsg = "", "", ", ") & "Section: **" & SectionName & "** - " & Err.Description
End Sub

Power模块子过程 #

Sub Power()
    Dim power_col As Long, power_value As Double
    SectionName = "Power"
    On Error GoTo errormessage
    
    power_col = GetColumnIndex(ws, "Average Power (mW)")
    power_value = Getdata(ws, sysrow, power_col)
    
    Exit Sub
errormessage:
    ' 捕获运行时错误并关联SectionName
    ErrorMsg = ErrorMsg & IIf(ErrorMsg = "", "", ", ") & "Section: **" & SectionName & "** - " & Err.Description
End Sub

GetColumnIndex函数 #

Function GetColumnIndex(sht As Worksheet, colname As String) As Double
    Dim paramname As Range
    Set paramname = sht.Range("A1", sht.Cells(2, sht.Columns.Count).End(xlToLeft)).Cells.Find(What:=colname, LookAt:=xlWhole, LookIn:=xlFormulas, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, MatchCase:=True)
    
    If Not paramname Is Nothing Then
        GetColumnIndex = paramname.Column
    Else
        ' 拼接错误信息时关联当前SectionName
        ErrorMsg = ErrorMsg & IIf(ErrorMsg = "", "", ", ") & "Section: **" & SectionName & "** - " & colname & " column index could not be found"
    End If
End Function

Getdata函数 #

Function Getdata(sht As Worksheet, WDrow As Long, parametercol As Long) As Variant
    Getdata = sht.Cells(WDrow, parametercol)
    
    If Getdata = -999 Then
        ' 可按需添加对应错误处理
    ElseIf Getdata = Empty Then
        ' 拼接错误信息时关联当前SectionName
        ErrorMsg = ErrorMsg & IIf(ErrorMsg = "", "", ", ") & "Section: **" & SectionName & "** - data could not be found"
    End If
End Function

关键修改点说明 #

• 每次处理新的cell条目时重置ErrorMsg，避免错误信息跨条目累积
• 所有错误信息拼接时加入SectionName并以**标注，明确错误来源模块
• 修正原代码中ActiveWorksheet的拼写错误为ActiveSheet
• 修正Main过程中的拼写错误"Complete with Erorr"为"Complete with Error"
• 在子过程的错误捕获分支中，添加运行时错误描述并关联Section

注：若需在Excel单元格中实现Section名称的视觉加粗效果，需额外编写代码定位文本中的Section名称并单独设置Font.Bold属性，上述代码已按需求完成格式拼接。

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