Posted by: Craig Hatmaker
database, development, excel, Microsoft Excel, ms query, odbc, sql, vba
Last post we added some small routines that tell us if various things exist so we can avoid trying to recreate what’s already there. That avoids errors and speeds things along. Another speed enhancer for just about any VBA routine is to shut off screen updating and calculations. And if you ever create a routine that handles worksheet events (and I do), disabling those events while you make changes to worksheets prevents infinite loops and other problems.
The Settings routine handles turning those things off and back on. Specifically, it is intended to:
- Save the current ActiveSheet, ScreenUpdating, EnableEvents, and Calculation properties
- Disable them before processing begins
- Restore them after processing ends
Three values alert Settings to which of those three functions it’s supposed to perform. Those values are: “Save”, “Disable”, and “Restore”. There are two other values Settings responds to. They are: “Clear” and “Debug” which are intended to facilitate development and debugging. “Clear” removes any saved settings. “Debug” displays the current settings in the Immediate Window.
I like to call “Settings” from button handlers, other event handlers, and routines that could potentially be long running and make changes to the screen.
Below is the code. To copy this routine:
- Get to the VBE (Alt-F11)
- From the VBE menu navigate File > Import File…
- Load modGeneral (see previous post)
- Select and copy the code below
- Paste into the Code Window *
- Make any corrections to code that didn’t paste correctly
- From the VBE menu navigate File > Export File…
- Save modGeneral
* Unfortunately, the code won’t paste 100% properly. You will have to add carriage returns and perhaps fix a few things until your code looks exactly like what you see here. If someone knows a better method, please let me know so I can improve this blog. Thanks.
Next Post: Setup_Pivot – a function for creating Pivot Tables from code.
Function Settings(sMode As String) As Boolean
' Settings: Saves, sets, and restores current application settings
' Parameters: sMode - "Save", "Restore", "Clear", "Disable", "Debug" ' Example: bResult = Settings("Disable")
' Date Init Modification ' 01/01/01 CWH Initial Programming Copyright 2009 Craig Hatmaker
On Error GoTo ErrHandler Settings = Failure 'Assume the worst Static Setting(999, 4) As Variant 'Limit to 1,000 settings, prevent loops Static iLevel As Integer
Select Case UCase(Trim(sMode)) Case Is = "SAVE" Setting(iLevel, 0) = ActiveSheet.Type Setting(iLevel, 1) = ActiveSheet.Name Setting(iLevel, 2) = Application.EnableEvents Setting(iLevel, 3) = Application.ScreenUpdating Setting(iLevel, 4) = Application.Calculation iLevel = iLevel + 1 Case Is = "RESTORE" If iLevel > 0 Then iLevel = iLevel - 1 If Setting(iLevel, 0) = -4167 Then Worksheets(Setting(iLevel, 1)).Activate Else Charts(Setting(iLevel, 1)).Activate End If Application.EnableEvents = Setting(iLevel, 2) Application.ScreenUpdating = Setting(iLevel, 3) Application.Calculation = Setting(iLevel, 4) End If Case Is = "CLEAR" iLevel = 0 Application.EnableEvents = True Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic Case Is = "DISABLE" Application.EnableEvents = False Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Case Is = "DEBUG" Debug.Print iLevel, _ Setting(iLevel, 0), _ Setting(iLevel, 1), _ Setting(iLevel, 2), _ Setting(iLevel, 3), _ Setting(iLevel, 4), _ End Select
Settings = Success 'Normal end - no errors
ErrHandler: If Err.Number <> 0 Then MsgBox _ "Settings - Error#" & Err.Number & vbCrLf & Err.Description, _ vbCritical, "Error", Err.HelpFile, Err.HelpContext On Error GoTo 0