Automatically execute code before and after running any procedure

Question

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

Not necessarily what you were asking, but I have exactly this stored in an .xlam file I always load up, so it is only a click away when I want to copy/paste when starting a new script. Would be interesting to know if there is an easier way — bmgh1985
– bmgh1985, Commented Oct 31, 2014 at 15:11
No, it is not possible to do what you're asking, put these statements in their own subroutines and call them as needed with a Call mySubroutine() statement or Application.Run "mySubroutine". — David Zemens
– David Zemens, Commented Oct 31, 2014 at 15:35
@bmgh1985 you can use the "PERSONAL.XLSB" file to contain frequently used macros, code snippets, etc., you can put this in the XLSTART folder and hide the file with the Workbook_Open event so it doesn't get inadvertently used like a spreadsheet. — David Zemens
– David Zemens, Commented Oct 31, 2014 at 15:37
I have some other bits I use as as an addin anyway (history logs mainly) so I just use the same file. — bmgh1985
– bmgh1985, Commented Oct 31, 2014 at 15:39

Jon Crowell · Accepted Answer · 2014-10-31 15:30:37Z

2

Code that loops through ranges and selects various things is frequently behind the need for this kind of optimization, and can almost ALWAYS be avoided. If you want help optimizing a heavy script, please ask another question with the resource-intensive code.

Without seeing an example of one of your "heavy scripts", the best thing to do is put your setup and restore code in subs and call them before and after you run your procedures.

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

edited Oct 31, 2014 at 15:30

answered Oct 31, 2014 at 15:13

Jon Crowell

22.4k15 gold badges94 silver badges113 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

tm- Over a year ago

That's the way I'm doing it now. The question is whether it's possible to launch this stuff automatically, without inserting Call GetReadyToProcess into every procedure. P.S. This code gives a good performance increase when working with worksheets with a lot of formulas in it and recalculation takes 10+ seconds.

bmgh1985 Over a year ago

This works (and is a very efficient way to do it) if you are handling multiple subs in one file, but the way I read the question it was more about having to constantly type it every time you wanted to write some new vba in a new file each time.

Jon Crowell Over a year ago

What is causing your need to do this? AFAIK, you can't call it automatically in each new sub.

Miguel Febres · Accepted Answer · 2014-10-31 15:53:37Z

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

In your ModuleTest, before the 1st execution:

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

And after the 1st execution:

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

Finally, after the 2nd execution:

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

This will break if "ModuleTest" is a class module with any properties.

bmgh1985 · Accepted Answer · 2014-10-31 16:00:07Z

0

Will take a little bit of playing with, but something along these lines should work and you can set it how you want it

NOTE: You need access to the VBA Project Object Model

Sub a()
Dim l as VBIDE.VBComponent
Dim strng As String
strng = "Sub b()" & vbCrLf & "***your routine***" & vbCrLf & "End Sub"
Set l = ThisWorkbook.VBProject.VBComponents.Add(vbext_ct_StdModule)
l.CodeModule.AddFromString strng

End Sub

You could expand this more and turn it into a function where you pass the sub name you are wanting or pass a module name if you want it attaching to an existing module.

~~If someone could point out what you should declare l as, I left it blank, but if there is a way to set it correctly, then it would mean you get Intellisene on it.~~ - Got this from Miguel's answer, which is in a similar vein

Now, I am off to have some fun with this! :)

edited Oct 31, 2014 at 16:00

answered Oct 31, 2014 at 15:46

bmgh1985

7891 gold badge15 silver badges38 bronze badges

1 Comment

RubberDuck Over a year ago

You might be interested in this. I've been working on a project to make it easier to work with VBIDE.

Collectives™ on Stack Overflow

Automatically execute code before and after running any procedure

3 Answers 3

3 Comments

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related