Often if procedures are called from existing code they change these settings, which makes it hard to track the current settings when a call returns. This code provides a stack to be used for Excel Application based settings, it provides two base functions; Push and Pop. The Push function stores the provided settings (or current settings for all optionals left blank) and sets them. Upon Pop, the previous settings on the stack are restored.

Application properties included

• Application.Calculation : Calculation, manual or automatic
• Application.Cursor : Mousepointer shown
• Application.DisplayAlerts : Should alerts be displayed
• Application.EnableCancelKey : How to respond to cancel keys (escape, break, etc.)
• Application.ScreenUpdating : Should the screen be updated while running code
• Application.StatusBar : False if statusbar text is Excel controlled, or string if custom message
• Application.Visible : Is the Excel application itself visible
• Application.EnableEvents : Excel events enabled

Sample use

• In each function or sub that changes settings, at its start, call Push, with the required settings. Upon exiting call Pop to restore previous settings.
• Everytime foreign code is called which might change settings, call Push just before calling the code. And call Pop after returning, this way your code will not be influenced by changes made to the settings by the foreign code.

Sample code

Create a module and add the following statement to create a public stack;

At the start of your function/sub add;

Add your code here…

At the end of your code add;

Remember: Always use Pop after Push or it will be out-of-sync!

Methods

• clsExcelSettingsStack.Push : Puts the provided settings on the stack and applies them
• clsExcelSettingsStack.Pop : Restores the previous settings, returns true/false for success
• clsExcelSettingsStack.Size : Read-only, reports back the size of the stack
• clsExcelSettingsStack.Reset : Resets the stack to the size requested, reports true/false for success
• clsExcelSettingsStack.Apply: Applies the last settings on the stack, without popping them.

Download

Download sample with code (downloaded 126 times).

Be Sociable, Share!

•