Switch to tell subroutines not to run a Sub

[Dr. Demento]

I have a sub that optimizes performance by shutting off certain functions (.ScreenUpdating, .DisplayAlerts, etc) at the beginning of a sub or function and turning them back on at the end. It works great and I have it in almost every sub and function I've made.

However, when it's run in a nested fashion, I wind up with a mixed bag by the end of the parent sub. For example:
Sub A (OPTIMIZE_On), call Sub B
Sub B (OPTIMIZE_On), call Sub C
Sub C (OPTIMIZE_On), (OPTIMIZE_Off), Return to Sub B
Sub B (OPTIMIZE_Off), Return to Sub A
Sub A (OPTIMIZE_Off)

This means that everything after Sub C returns to Sub B is enabled (OPTIMIZE_Off).

My desire is to have a switch of some type where by any call to (OPTIMIZE_) that is NOT from the Parent Sub is ignored. My first thought was to have the (OPTIMIZE_) depend on the procedure name (rtsProcName) but I'm at a loss on how to make the sub recognize the first call, set some switch, and then test the current sub name against the parent sub; if FALSE then Quit Sub.

Any thoughts??? Thanks y'all

Public gbl_nlbOptimize As Boolean  ' ~~ Switch to tell subroutines NOT to run OptimizeVBA, but to only run from main routine
 
Public Sub OptimizeVBA(isOn As Boolean, _
                       rtsProcName As String)
' ~~ Procedure for increasing VBA performance
' [URL]http://analystcave.com/excel-improve-vba-performance/[/URL]
'  Excel VBA Performance Coding Best Practices: Turn off some Excel functionality so your code runs faster
'  [URL]http://blogs.office.com/2009/03/12/excel-vba-performance-coding-best-practices/[/URL]
'  With Application: [URL]https://msdn.microsoft.com/en-us/library/office/ff835544.aspx[/URL]
On Error Resume Next
    
    With Application
      .ScreenUpdating = Not (isOn)
      .EnableEvents = Not (isOn)
      .DisplayAlerts = Not (isOn)
  '    .DisplayStatusBar = Not (isOn)
      .PrintCommunication = Not (isOn)
      .Calculation = IIf(isOn, xlCalculationManual, xlCalculationAutomatic)
    End With
    
    ActiveSheet.DisplayPageBreaks = Not (isOn)
    
On Error GoTo 0
End Sub

 

[RoryA]

I'd suggest you use a class that stores the current status of each variable in its Initialize routine and turns them off if need be. Then in its Terminate event put each property back the way it was - which may or may not be enabled depending on when the class instance was created.

 

[Dr. Demento]

Thank you for the tip. Unfortunately, I don’t know anything about classes (on the learning To-Do list). Any pointers?

 

[Jon Peltier]

What I do is save the value of each relevant parameter at the beginning of a sub, turns it off (which has no effect if it's already off, as opposed to toggling the parameter), and sets it to the original state at the end. If the parameter was on at the beginning, it is turned off, then back on at the end; if the parameter was off at the beginning, it is kept off, then at the end it stays off.

Sub MyRoutine()
  Dim bScreenUpdating As Boolean
  bScreenUpdating = Application.ScreenUpdating
  Application.ScreenUpdating = False

  ' code does stuff here

  Application.ScreenUpdating = bScreenUpdating
End Sub

 

[mikerickson]

You could write the optimize routine to remember how many times it has been set and act accordingly.
Every routine should start by calling the Optimize sub to optimize and finish by calling it to de-optimize.

Sub Optimize(blnSet As Boolean, Optional ForceOff As Boolean)
    Static OptimizeLevel As Long

    If ForceOff Then
        OptimizeLevel = 0
    Else
        If blnSet Then
            OptimizeLevel = OptimizeLevel + 1
        Else
            OptimizeLevel = OptimizeLevel - 1
        End If
    End If

    If OptimizeLevel = 0 Then
        Application.ScreenUpdating = True
        Application.Calculation = xlCalculationAutomatic
        ' etc.
    Else
        Application.ScreenUpdating = False
        Application.Calculation = xlCalculationManual
        ' etc.
    End If
End Sub

Sub One()
    Optimize True
    
    ' code
    
    Optimize False
End Sub

Sub Two()
    Optimize True
    
    ' other code
    
    Optimize False
End Sub

Sub Three()
    Optimize True
    
    'code
    Call One
    Call Two
    ' more code
    
    Optimize False
End Sub

Note the optional ForceOff argument to restore normal function for error situations.

 

[Dr. Demento]

Jon & Mike,

Thank you for the suggestions. I went with Mike's suggestion because I was attempting to use a single sub instead of including all the code in each sub.

Mike, I did have a question about how to use ForceOff. I'm unsure how to implement this other than to explicitly set it. I keep envisioning the ForceOff switch = TRUE if there's an error; how could this be accomplished?

Thanks again.