按钮触发VBA时DeleteSomeFormulas子程序未执行，手动运行正常

问题描述 #

编写了Email_PDF宏，实现以下功能：

• 将工作表保存为PDF并存入指定文件夹
• 自动生成邮件并附加该PDF
• 过程中调用DeleteSomeFormulas子程序，清除指定区域公式并保留数值

手动从开发选项卡运行时所有功能正常，但通过按钮触发Email_PDF时，PDF生成、邮件发送等功能正常，唯独DeleteSomeFormulas未执行。

相关代码 #

Email_PDF宏 #

Sub Email_PDF()
Application.ScreenUpdating = False
Call DeleteSomeFormulas
Dim Path As String, File As String
Path = "Z:\Team\Staff Communications\Staff Briefings\Staff Briefings 2024" & "\"
With ActiveWorkbook: File = Left(.Name, InStr(.Name, ".") - 1): End With
With ActiveSheet
    File = "QF28 - Staff Information - Weekly Briefing week commencing " & .Name
    With .PageSetup
        .Zoom = False
        .FitToPagesTall = False
        .FitToPagesWide = 1
        .CenterHorizontally = True
        .CenterVertically = False
    End With
    .Range("A1:F110").ExportAsFixedFormat xlTypePDF, Path & File
End With
With CreateObject("Outlook.Application").CreateItem(0)
    .Display
    .To = "Technicians"
    .Subject = "Briefing Sheet"
    .Body = "Please find attached the latest Briefing Sheet."
    .Attachments.Add Path & File & ".pdf"
End With
End Sub

DeleteSomeFormulas子程序 #

Sub DeleteSomeFormulas()
    With ActiveSheet
        Call fileProtection(False)
        Range("A2:F9").Copy
        Range("A2:F9").PasteSpecial xlPasteValues
        Call fileProtection(True)
    End With
End Sub

排查与解决方法 #

1. 避免依赖ActiveSheet，明确指定工作表 #

按钮触发宏时，ActiveSheet可能不是你预期的工作表，导致DeleteSomeFormulas操作了错误的表，看起来没执行。修改代码，直接指定目标工作表：

修改DeleteSomeFormulas：

Sub DeleteSomeFormulas()
    Dim targetSheet As Worksheet
    '替换成你实际操作的工作表名称
    Set targetSheet = ThisWorkbook.Worksheets("你的工作表名")
    
    '如果fileProtection需要指定工作表，同步修改该子程序的参数
    Call fileProtection(targetSheet, False)
    targetSheet.Range("A2:F9").Copy
    targetSheet.Range("A2:F9").PasteSpecial xlPasteValues
    Call fileProtection(targetSheet, True)
    '清除复制状态，避免弹窗提示
    Application.CutCopyMode = False
End Sub

同时修改Email_PDF中的With ActiveSheet为指定工作表，保持操作一致性。

2. 检查宏的存放位置 #

确保DeleteSomeFormulas和Email_PDF都存放在标准模块（如Module1）中。如果子程序在工作表模块，调用时需要加上工作表前缀，例如Sheet1.DeleteSomeFormulas。

3. 添加错误捕获，排查潜在错误 #

按钮触发宏时默认关闭错误提示，若DeleteSomeFormulas或依赖的fileProtection存在错误，会直接跳过执行。添加错误捕获代码，定位问题：

修改Email_PDF：

Sub Email_PDF()
    Application.ScreenUpdating = False
    On Error GoTo ErrorHandler '开启错误捕获
    
    Call DeleteSomeFormulas
    '以下保留原有代码...
    Dim Path As String, File As String
    Path = "Z:\Team\Staff Communications\Staff Briefings\Staff Briefings 2024" & "\"
    With ActiveWorkbook: File = Left(.Name, InStr(.Name, ".") - 1): End With
    With ActiveSheet
        File = "QF28 - Staff Information - Weekly Briefing week commencing " & .Name
        With .PageSetup
            .Zoom = False
            .FitToPagesTall = False
            .FitToPagesWide = 1
            .CenterHorizontally = True
            .CenterVertically = False
        End With
        .Range("A1:F110").ExportAsFixedFormat xlTypePDF, Path & File
    End With
    With CreateObject("Outlook.Application").CreateItem(0)
        .Display
        .To = "Technicians"
        .Subject = "Briefing Sheet"
        .Body = "Please find attached the latest Briefing Sheet."
        .Attachments.Add Path & File & ".pdf"
    End With

ErrorHandler:
    If Err.Number <> 0 Then
        MsgBox "错误信息：" & Err.Description & vbCrLf & "错误代码：" & Err.Number
    End If
    Application.ScreenUpdating = True
End Sub

4. 检查fileProtection子程序 #

确认fileProtection是否正确处理工作表保护：

• 解除保护时密码是否正确（如果工作表有密码）
• 参数传递是否正确，比如是否需要接收工作表对象作为参数

如果fileProtection解除保护失败，后续的复制粘贴操作会被阻止，导致DeleteSomeFormulas看似未执行。

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