移除On Error Resume Next后SheetExists函数运行异常求助

我写了一个SheetExists函数，用来检查工作簿中是否存在指定名称的工作表。重构代码移除On Error Resume Next语句后，函数运行结果不符合预期。

预期效果 #

• 当目标工作表不存在时，宏复制对应的工作表
• 当目标工作表已存在时，将ErrorMsg设为"Unknown Error"

实际问题 #

即便工作表不存在，宏依然会输出ErrorMsg，同时还能完成工作表复制操作。

我不想用On Error Resume Next忽略错误，而是希望错误发生时输出"Unknown Error"。

相关代码 #

主程序Main #

Global Parameter As Long, RoutingStep As Long, wsName As String, version As String, ErrorMsg As String, SDtab As Worksheet
Global wb As Workbook, sysrow As Long, sysnum As String, ws As Worksheet

Public Sub Main()
    Dim syswaiver As Long, axsunpart As Long
    Dim startcell As String, cell As Range
    Dim syscol As Long, dict As Object, wbSrc As Workbook

Set wb = Workbooks("SD3_KW.xlsm")
Set ws = wb.Worksheets("Data Sheet") 


syswaiver = 3
axsunpart = 4


Set wbSrc = Workbooks.Open("Q:\Documents\Specification Document.xlsx")
Set dict = CreateObject("scripting.dictionary") 

If Not syswaiver = 0 Then
    startcell = ws.cells(2, syswaiver).Address 
Else
    ErrorMsg = "waiver number column index not found. Value needed to proceed"
    GoTo Skip
End If

For Each cell In ws.Range(startcell, ws.cells(ws.Rows.Count, syswaiver).End(xlUp)).cells 
    sysnum = cell.value
    sysrow = cell.row
    syscol = cell.column
    
    If Not dict.Exists(sysnum) Then 
        dict.Add sysnum, True
    
        If Not SheetExists(sysnum, wb) Then 
            If Not axsunpart = 0 Then
                wsName = cell.EntireRow.Columns(axsunpart).value 
                If SheetExists(wsName, wbSrc) Then 
                    wbSrc.Worksheets(wsName).copy After:=ws 
                    wb.Worksheets(wsName).Name = sysnum 
                Set SDtab = wb.Worksheets(ws.Index + 1)
                Else
                    ErrorMsg = ErrorMsg & IIf(ErrorMsg = "", "", "") & "part number for " & sysnum & " sheet to be copied could not be found"
                    cell.Interior.Color = vbRed
                GoTo Skip
                End If
      Else
                ErrorMsg = "part number column index not found. Value needed to proceed"
            End If 
            
        Else 
            MsgBox "Sheet " & sysnum & " already exists."
        End If
    End If
    
Skip:

Dim begincell As Long, logsht As Worksheet 
Set logsht = wb.Worksheets("Log Sheet") 
    With logsht ' wb.Worksheets("Log Sheet")
        begincell = .cells(Rows.Count, 1).End(xlUp).row
        .cells(begincell + 1, 3).value = sysnum
        .cells(begincell + 1, 3).Font.Bold = True
        .cells(begincell + 1, 2).value = Date
        .cells(begincell + 1, 2).Font.Bold = True

        If Not ErrorMsg = "" Then
            .cells(begincell + 1, 4).value = vbNewLine & "Complete with Error - " & vbNewLine & ErrorMsg
            .cells(begincell + 1, 4).Font.Bold = True
            .cells(begincell + 1, 4).Interior.Color = vbRed
        Else
            .cells(begincell + 1, 4).value = "All Sections Completed without Errors"
            .cells(begincell + 1, 4).Font.Bold = True
            .cells(begincell + 1, 4).Interior.Color = vbGreen
        End If
    End With

Next Cell 

End Sub

SheetExists函数 #

Function SheetExists(SheetName As String, wb As Workbook)  
On Error GoTo Message
SheetExists = Not wb.Sheets(SheetName) Is Nothing
Exit Function
Message:
    ErrorMsg = "Unknown Error"
End Function

问题根源与修复方案 #

问题原因 #

1. 函数无明确返回值：当工作表不存在时，wb.Sheets(SheetName)抛出错误，跳到Message分支后函数未返回布尔值，VBA默认返回Empty，导致主程序If Not SheetExists(...)的判断逻辑混乱。
2. 全局变量未重置：ErrorMsg是全局变量，一旦被设置就会保留到后续循环，导致错误信息残留。
3. 错误触发逻辑颠倒：工作表不存在是预期内的正常判断场景，不该触发"Unknown Error"，但当前函数只要出错就会设置该错误信息。

修复步骤 #

1. 修正SheetExists函数

确保函数在所有分支都返回明确的布尔值，仅在非预期错误时设置ErrorMsg：

Function SheetExists(SheetName As String, wb As Workbook) As Boolean
    Dim ws As Worksheet
    On Error GoTo ErrorHandler
    
    Set ws = wb.Sheets(SheetName)
    SheetExists = True
    Exit Function
    
ErrorHandler:
    ' 仅当错误不是工作表不存在（错误号9=下标越界）时，才设置未知错误
    If Err.Number <> 9 Then
        ErrorMsg = "Unknown Error"
    End If
    SheetExists = False
End Function

2. 循环内重置ErrorMsg

在For Each cell In ...循环的开头添加代码，清空上一次循环的错误信息：

ErrorMsg = ""

3. 调整主程序的错误触发逻辑

按照预期，仅当工作表已存在时设置ErrorMsg：

If Not SheetExists(sysnum, wb) Then 
    ' 复制工作表的原有逻辑...
Else 
    ErrorMsg = "Unknown Error"
    MsgBox "Sheet " & sysnum & " already exists."
End If

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