调用删除公式的子过程导致Excel工作簿出现闪烁问题

核心修改思路 #

屏幕闪烁源于Excel默认实时刷新屏幕以展示宏的操作过程，通过关闭屏幕更新、临时禁用不必要的Excel功能可彻底解决该问题。

修改后的完整代码 #

Sub Email_PDF()
    ' 保存Excel原始状态，避免影响后续正常操作
    Dim originalScreenUpdating As Boolean
    Dim originalEnableEvents As Boolean
    Dim originalCalculation As XlCalculation
    
    originalScreenUpdating = Application.ScreenUpdating
    originalEnableEvents = Application.EnableEvents
    originalCalculation = Application.Calculation
    
    ' 关闭屏幕刷新、事件触发，切换为手动计算模式
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual
    
    On Error GoTo Cleanup ' 确保出错时也能恢复原始设置
    
    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 = 1
            .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

Cleanup:
    ' 恢复Excel原始状态
    Application.ScreenUpdating = originalScreenUpdating
    Application.EnableEvents = originalEnableEvents
    Application.Calculation = originalCalculation
    
    ' 若运行出错，弹出提示
    If Err.Number <> 0 Then
        MsgBox "宏运行出错: " & Err.Description, vbExclamation
    End If
End Sub

关键修改点说明 #

• 关闭屏幕更新：Application.ScreenUpdating = False 暂停Excel的屏幕刷新，直到恢复为True，直接消除闪烁现象。
• 禁用事件触发：Application.EnableEvents = False 避免宏操作触发工作表/工作簿事件（如Worksheet_Change），减少额外的屏幕刷新和性能消耗。
• 手动计算模式：Application.Calculation = xlCalculationManual 防止公式删除过程中触发自动计算，提升运行速度的同时避免不必要的屏幕波动。
• 错误处理与状态恢复：通过On Error GoTo Cleanup确保即使宏运行出错，也能恢复Excel的原始设置，不会影响后续正常使用。

额外优化建议 #

如果DeleteSomeFormulas过程本身涉及大量单元格操作，建议在该过程内部也加入屏幕更新控制，进一步优化性能：

Sub DeleteSomeFormulas()
    Dim originalScreenUpdating As Boolean
    originalScreenUpdating = Application.ScreenUpdating
    Application.ScreenUpdating = False
    
    ' 你的原有删除公式代码
    ' 示例：Range("A1:A10").Value = Range("A1:A10").Value
    
    Application.ScreenUpdating = originalScreenUpdating
End Sub

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