Avatar of Robert Berke
Robert BerkeFlag 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
ASKER CERTIFIED SOLUTION
Avatar of Martin Liss
Martin Liss
Flag of United States of America image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
SOLUTION
Avatar of Robert Berke
Robert Berke
Flag of United States of America image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Microsoft Office
Microsoft Office

Microsoft Office is an integrated suite of applications that includes Outlook, Word, Excel, Access, PowerPoint, Visio and InfoPath, along with a number of tools to assist in making the individual components work together. Coding within and between the projects is done in Visual Basic for Applications, known as VBA.

80K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo