VBA检测指定工作表存在后代码异常退出问题及优化需求

核心问题梳理 #

你的代码存在以下关键问题，导致异常退出或无法执行目标工作表的公式检查：

• 变量重复定义：完整代码中重复定义了wb和ws，引发编译错误
• 数组引用错误：定义了数组arr1，但遍历错误使用了未声明的arr
• 工作表对象引用错误：错误用ThisWorkbook代替了打开的目标工作簿wb
• 遍历检测逻辑错误：原代码只要发现单个工作表不存在就直接退出，无法处理其他存在的工作表

修正后的完整代码 #

Sub Checks()
    ' 关闭Book1（可选）
    On Error Resume Next
    Workbooks("Book1.xlsx").Close SaveChanges:=False
    On Error GoTo 0
    
    ' 选择要检查的模板文件
    Dim myFileName As Variant
    myFileName = Application.GetOpenFilename(FileFilter:="Excel Files,*.xl*;*.xm*")
    If myFileName = False Then Exit Sub
    
    Dim wb As Workbook
    Set wb = Workbooks.Open(Filename:=myFileName)
    
    ' 先检查NL20是否存在，不存在直接终止
    Dim ws As Worksheet
    On Error Resume Next
    Set ws = wb.Worksheets("NL20")
    On Error GoTo 0
    If ws Is Nothing Then
        MsgBox "NL20工作表不存在，代码终止", vbExclamation
        wb.Close SaveChanges:=False
        Exit Sub
    End If
    
    ' 执行NL20的公式检查
    Call RunFormulaChecks(ws)
    
    ' 检查NL21和US12是否至少存在一个
    Dim hasTargetSheets As Boolean
    hasTargetSheets = SheetExists(wb, "NL21") Or SheetExists(wb, "US12")
    If Not hasTargetSheets Then
        MsgBox "NL21和US12工作表均不存在，代码终止", vbExclamation
        Exit Sub
    End If
    
    ' 遍历处理存在的NL21和US12
    Dim shtName As Variant
    For Each shtName In Array("NL21", "US12")
        On Error Resume Next
        Set ws = wb.Worksheets(shtName)
        On Error GoTo 0
        If Not ws Is Nothing Then
            Call RunFormulaChecks(ws)
        End If
    Next shtName
    
    MsgBox "所有存在的工作表公式检查完成", vbInformation
End Sub

' 通用工作表存在检测函数
Function SheetExists(wb As Workbook, sheetName As String) As Boolean
    On Error Resume Next
    SheetExists = Not wb.Worksheets(sheetName) Is Nothing
    On Error GoTo 0
End Function

' 通用公式检查执行函数（把你的公式检查逻辑放在这里）
Sub RunFormulaChecks(ws As Worksheet)
    ' 这里放入你的公式检查代码，示例：
    Dim wsHeader_Row As Long
    wsHeader_Row = 2
    Dim wsFind_Last_Row As Long
    wsFind_Last_Row = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row
    Dim wsFind_Last_Column As Long
    wsFind_Last_Column = ws.Cells(wsHeader_Row, ws.Columns.Count).End(xlToLeft).Column
    
    ' 你的其他公式创建逻辑...
    ' 示例：在指定单元格写入检查公式
    ws.Cells(wsFind_Last_Row + 2, "B").Formula = "=COUNTIF(B:B,""Error"")"
End Sub

关键修正说明 #

• 统一对象引用：所有工作表操作基于打开的目标工作簿wb，避免ThisWorkbook或ActiveWorkbook带来的对象混淆
• 模块化拆分：把公式检查逻辑封装到RunFormulaChecks子过程，避免重复代码，主流程更清晰
• 修正检测逻辑：先确保NL20存在，再判断NL21/US12是否至少存在一个，仅当两者都不存在时才终止代码
• 遍历处理逻辑：逐个检查NL21和US12，存在则执行检查，不存在则跳过，不会因单个工作表缺失终止整个流程

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