如何避免VBA全局变量ErrorMsg被后续错误信息覆盖？

我定义了全局变量ErrorMsg，可由多个函数赋值。当前宏在错误发生、ErrorMsg已赋值后仍会继续执行所有数据处理流程。但由于函数间存在依赖关系，若GetColumnIndex出错赋值后，依赖其输出的GetData也会出错并覆盖ErrorMsg原有值。需要实现：宏继续执行，但ErrorMsg一旦被赋值就不再被后续错误信息覆盖。

原代码 #

主过程代码 #

Global ErrorMsg As String 
Sub Main 
Dim cell As Range, ws As Worksheet, sysnum As String, sysrow As Integer, wb As Workbook, logsht As Worksheet 
Dim power_col As Long, power_value As Double 

Set wb = ActiveWorkbook 
Set ws = 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

power_col = GetColumnIndex(ws, "Power (mW)")
power_value = GetJiraData(ws, sysrow, power_col) '注：此处疑似笔误，应为GetData

Dim begincell As Long
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
        .cells(begincell + 1, 3).Value = "Complete with Erorr - " & ErrorMsg '注：Error拼写错误，应为Error
        .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 

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
    ElseIf paramname Is Nothing Then 
       ErrorMsg = colname & " column index could not be found. Check before running again."
    End If
End Function

GetData函数 #

Function GetData(sht As Worksheet, WDrow As Integer, parametercol As Long) 
GetData = sht.cells(WDrow, parametercol)
If GetData = -999 Then
ElseIf GetData < 0 Then
    ErrorMsg = "Data cannot be a negative number. Check before running again." 
End If
End Function

修改方案 #

要实现需求，只需做两处关键修改：

1. 每次处理单元格前重置ErrorMsg：在For Each cell循环内部开头添加ErrorMsg = ""，保证每个单元格的错误信息独立，不受上一次循环的错误遗留影响。
2. 仅当ErrorMsg为空时才赋值：在所有给ErrorMsg赋值的地方增加判断条件，只有当前ErrorMsg是空字符串时才更新错误信息，避免后续错误覆盖已有内容。

修改后的完整代码 #

主过程代码

Global ErrorMsg As String 
Sub Main 
Dim cell As Range, ws As Worksheet, sysnum As String, sysrow As Integer, wb As Workbook, logsht As Worksheet 
Dim power_col As Long, power_value As Double 

Set wb = ActiveWorkbook 
Set ws = ActiveWorksheet 
Set logsht = wb.Worksheets("Log Sheet") 

For Each cell In ws.Range("E2", ws.cells(ws.Rows.Count, "E").End(xlUp)).cells 
    ' 重置错误信息，保证当前单元格处理的独立性
    ErrorMsg = ""
    sysnum = cell.Value
    sysrow = cell.row

    power_col = GetColumnIndex(ws, "Power (mW)")
    power_value = GetData(ws, sysrow, power_col) '修正笔误：GetJiraData改为GetData

    Dim begincell As Long
    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
            .cells(begincell + 1, 3).Value = "Complete with Error - " & ErrorMsg '修正拼写错误：Erorr改为Error
            .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 

修改后的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
    ElseIf paramname Is Nothing Then 
        ' 仅当ErrorMsg为空时赋值，避免覆盖已有错误
        If ErrorMsg = "" Then
            ErrorMsg = colname & " column index could not be found. Check before running again."
        End If
    End If
End Function

修改后的GetData函数

Function GetData(sht As Worksheet, WDrow As Integer, parametercol As Long) 
GetData = sht.cells(WDrow, parametercol)
If GetData = -999 Then
ElseIf GetData < 0 Then
    ' 仅当ErrorMsg为空时赋值，避免覆盖已有错误
    If ErrorMsg = "" Then
        ErrorMsg = "Data cannot be a negative number. Check before running again." 
    End If
End If
End Function

说明 #

• 循环内重置ErrorMsg确保每个单元格的错误信息互不干扰，不会把上一行的错误带到下一行。
• 每个赋值ErrorMsg的地方增加空值判断，保证第一个发生的错误信息会被保留，后续错误不会覆盖它，同时宏仍然会继续执行完所有流程。

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