Avatar of Robert Berke
Robert Berke
Flag for United States of America asked on

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

 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.
                StackPush(aCalculation)
                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
Next
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
Microsoft Office* Excel AddinsVBA

Avatar of undefined
Last Comment
Robert Berke

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Martin Liss

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
SOLUTION
Robert Berke

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Your help has saved me hundreds of hours of internet surfing.
fblack61