作为加载项使用时，VBA删除的工作表重新出现的问题排查

我写了一个VBA宏，功能是删除指定工作表，同时删除DataStorage工作表BP列中对应工作表名称的文本。单独运行宏时一切正常，但把脚本做成加载项使用时，已经删除的工作表会重新出现。相关代码如下：

Private Sub CommandButton2_Click()
    Dim xWs As Worksheet
Dim wb As Workbook
    Dim sheetName As String

Dim z As Long
    Dim last As Long

ThisWorkbook.Activate

sheetName = Application.InputBox("Please enter the OSAT Name:", "OSAT", _
, , , , , 2)
    last = ThisWorkbook.Worksheets("DataStorage").Cells(Rows.Count, "BP").End(xlUp).Row
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Err.Clear

    On Error Resume Next

    Set xWs = Sheets(sheetName)

    If Err <> 0 Then

        MsgBox "The OSAT '" & sheetName & "'" & "does not exist!", vbInformation, "Excel 10 Tutorial"

        Exit Sub

    Else

        xWs.Delete

        MsgBox "The OSAT '" & sheetName & "'" & "has been deleted!", vbInformation, "Excel 10 Tutorial"
        
    For z = last To 2 Step -1
        If ThisWorkbook.Worksheets("DataStorage").Cells(z, "BP").Value = sheetName Then
            Sheets("DataStorage").Cells(z, "BP").Delete
        End If
    Next z
    End If
    Application.ScreenUpdating = True 
    Application.DisplayAlerts = True
End Sub

核心原因 #

1. ThisWorkbook指向错误：当脚本作为加载项（.xlam）运行时，ThisWorkbook指向的是加载项文件本身，而非你正在操作的目标工作簿。代码里的ThisWorkbook.Activate和ThisWorkbook.Worksheets都是在修改加载项文件，根本没碰用户打开的目标工作簿，所以看起来像是“删除后恢复”，实际是从来没删除过目标工作簿的工作表。
2. 未明确目标工作簿：代码定义了wb变量但未使用，没有锁定要操作的用户工作簿，导致上下文混乱。
3. 错误捕获逻辑不严谨：On Error Resume Next一直生效，会隐藏后续代码的错误，比如找不到DataStorage工作表时不会报错，进一步掩盖问题。

修正后的代码 #

Private Sub CommandButton2_Click()
    Dim xWs As Worksheet
    Dim wb As Workbook
    Dim sheetName As String
    Dim z As Long
    Dim last As Long
    Dim dataSheet As Worksheet

    ' 锁定当前操作的目标工作簿（而非加载项自身）
    Set wb = ActiveWorkbook
    If wb Is Nothing Then
        MsgBox "请先打开目标工作簿！", vbInformation
        Exit Sub
    End If

    sheetName = Application.InputBox("请输入OSAT名称:", "OSAT", , , , , , 2)
    If sheetName = "" Then Exit Sub ' 处理用户取消输入的情况

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

    ' 检查目标工作表是否存在
    On Error Resume Next
    Set xWs = wb.Sheets(sheetName)
    On Error GoTo 0 ' 恢复正常错误捕获
    If xWs Is Nothing Then
        MsgBox "OSAT '" & sheetName & "' 不存在！", vbInformation, "提示"
        GoTo Cleanup
    End If

    ' 删除指定工作表
    xWs.Delete
    MsgBox "OSAT '" & sheetName & "' 已删除！", vbInformation, "提示"

    ' 检查DataStorage工作表是否存在
    On Error Resume Next
    Set dataSheet = wb.Worksheets("DataStorage")
    On Error GoTo 0
    If dataSheet Is Nothing Then
        MsgBox "目标工作簿中不存在DataStorage工作表！", vbInformation
        GoTo Cleanup
    End If

    ' 删除BP列中对应名称的行
    last = dataSheet.Cells(dataSheet.Rows.Count, "BP").End(xlUp).Row
    For z = last To 2 Step -1
        If dataSheet.Cells(z, "BP").Value = sheetName Then
            dataSheet.Rows(z).Delete ' 明确删除整行，避免歧义
        End If
    Next z

Cleanup:
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
End Sub

关键修正点 #

• 用ActiveWorkbook替代ThisWorkbook，确保操作的是用户当前使用的工作簿
• 恢复错误捕获逻辑（On Error GoTo 0），避免隐藏后续代码的错误
• 增加工作表存在性检查，提升容错性
• 明确使用Rows(z).Delete删除整行，避免单元格删除的歧义
• 增加用户取消输入的处理逻辑

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