troubleshooting Question

How I Push and Pop vba system settings like Application.Calculation. Can it be improved?

Avatar of rberke
rberkeFlag for United States of America asked on
Microsoft OfficeVBA* Excel Addins
2 Comments2 Solutions12 ViewsLast Modified:
 For 25 years I have written hundreds of subroutines that did something like this.
                Application.calculation = false
                Call LongRunningSub
                Application.calculation = true
That approach always reset the specific setting to its default value which is not what I really wanted.
Instead I wanted the following approach, but I never knew how to do it generically.
                Push Application.calculation
                Call LongRunningSub
                Pop Application.calculation
Today, I finally figured out a fairly easy solution which I have shown in the attached .xlsm file. The above code becomes.
                Call LongRunningSub
                StackPop (aCalculation)
My  Push routine is very simple (see the .xlsm file for the actual subroutines and more details).
Private stack As Object
Sub StackPush(ParamArray setting())
‘ see .xlsm for most current version of this routine
Dim item As Variant, curr As Variant
If stack Is Nothing Then Set stack = CreateObject("New:{4599202D-460F-3FB7-8A1C-C2CC6ED6C7C8}") ' CreateObject("System.Collections.Stack")
For Each item In setting
    Select Case item
        Case aEnableEvents: stack.Push Application.EnableEvents
        Case aScreenUpdating: stack.Push aScreenUpdating
        Case aDisplayAlerts: stack.Push aDisplayAlerts
        Case aCalculation: stack.Push aCalculation
        Case Else: MsgBox "110659 err"
    End Select
End Sub
So I am asking my fellow experts for advice and suggestions.
  1. Have any of you ever seen a similar or better solution?
  2. Do you think this idea is worthy of an experts-exhange article?
  3. Do you think you would use this code  yourself?
  4. Currently, I support aEnableEvents, aScreenUpdating, aDisplayAlerts, aCalculation, and aCustom
     what other settings should I support?
  5. My .xlsm file currently supports pushing and popping of user defined variables.  I wonder if that is a bad idea?
  6. I currently have very little error checking. I feel that it is the developers job to make sure the Push and Pop calls match properly..  
  7. Any other improvements you can think of?
 ee aug22 Push and Pop settings.xlsm
Join our community to see this answer!
Unlock 2 Answers and 2 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 2 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros