在运行任何过程之前和之后自动执行代码 [英] Automatically execute code before and after running any procedure

问题描述

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

Before launching any heavy script I need to make some performance tweaks with 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

所以我在运行几乎每个过程之前粘贴这个代码（或调用特殊的过程，包含这个代码，没有区别）。有没有办法自动执行（某种全局构造函数）？

So I paste this code (or call special procedure, containing this code, no difference) before running almost every procedure. Is there a way to do this automatically (some kind of global constructor)?

在完成过程之后与代码一样的情况：

And just the same situation with code after finishing the procedure:

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

推荐答案

使用1个程序的1个模块，但可以迭代所有模块的所有过程并使用相同的逻辑。它使用Jon Crowell的程序。

This example only work with 1 procedure of 1 module but you can iterate all the procedures of all modules and use the same logic. It uses the procedures from Jon Crowell.

Private Sub SwitchHeaderFooter()

    Dim lineNr As Long
    Dim procName As String
    Dim strHeader As String
    Dim strFooter As String

    procName = "TestProc"
    strHeader = "Call GetReadyToProcess"
    strFooter = "Call ReturnSettingsToWhatTheyWere"

    Dim vbComp As VBIDE.VBComponent
    Dim vbModule As VBIDE.CodeModule
    Set vbComp = ThisWorkbook.VBProject.VBComponents("ModuleTest")
    Set vbModule = vbComp.CodeModule

    lineNr = vbModule.ProcBodyLine(procName, vbext_pk_Proc)
    If (vbModule.Lines(lineNr + 1, 1) = strHeader) Then
        vbModule.DeleteLines lineNr + 1, 1
    Else
        vbModule.InsertLines lineNr + 1, strHeader
    End If

    lineNr = vbModule.ProcCountLines(procName, vbext_pk_Proc)
    If (vbModule.Lines(lineNr - 1, 1) = strFooter) Then
        vbModule.DeleteLines lineNr - 1, 1
    Else
        vbModule.InsertLines lineNr, strFooter
    End If

End Sub

在ModuleTest中，在第一次执行之前：

In your ModuleTest, before the 1st execution:

Sub TestProc()
    MsgBox "This is a test procedure!"
End Sub

第一次执行后：

Sub TestProc()
Call GetReadyToProcess
    MsgBox "This is a test procedure!"
Call ReturnSettingsToWhatTheyWere
End Sub

最后，第二次执行后： p>

Finally, after the 2nd execution:

Sub TestProc()
    MsgBox "This is a test procedure!"
End Sub

这篇关于在运行任何过程之前和之后自动执行代码的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！