vba - 在运行任何程序之前和之后自动执行代码

Question

在启动任何繁重的脚本之前，我需要使用 Excel 进行一些性能调整:

'Save parameters
screenUpdateState = Application.ScreenUpdating
statusBarState = Application.DisplayStatusBar
calcState = Application.Calculation
eventsState = Application.EnableEvents

'Turn them off
Application.ScreenUpdating = False
Application.DisplayStatusBar = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False

所以我在运行几乎每个程序之前粘贴了这段代码(或调用特殊程序，包含这段代码，没有区别)。有没有办法自动执行此操作(某种全局构造函数)？

完成该过程后，代码的情况与此相同:

'Put everything back
Application.ScreenUpdating = screenUpdateState
Application.DisplayStatusBar = statusBarState
Application.Calculation = calcState
Application.EnableEvents = eventsState

Answer 1

循环遍历范围并选择各种事物的代码经常落后于这种优化的需求，几乎可以 总是 避免。如果您需要帮助优化繁重的脚本，请使用资源密集型代码提出另一个问题。

在没有看到您的“繁重脚本”之一的示例的情况下，最好的办法是将您的设置和恢复代码放在 subs 中，并在您运行程序之前和之后调用它们。

Sub HeavyLifting()
    Call GetReadyToProcess

    ' code for sub...

    Call ReturnSettingsToWhatTheyWere
End Sub

Sub GetReadyToProcess()
    'Save parameters
    screenUpdateState = Application.ScreenUpdating
    statusBarState = Application.DisplayStatusBar
    calcState = Application.Calculation
    eventsState = Application.EnableEvents

    'Turn them off
    Application.ScreenUpdating = False
    Application.DisplayStatusBar = False
    Application.Calculation = xlCalculationManual
    Application.EnableEvents = False
End Sub

Sub ReturnSettingsToWhatTheyWere()
    'Put everything back
    Application.ScreenUpdating = screenUpdateState
    Application.DisplayStatusBar = statusBarState
    Application.Calculation = calcState
    Application.EnableEvents = eventsState
End Sub