Building a Library of Routines – Settings

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:

1. Save the current ActiveSheet, ScreenUpdating, EnableEvents, and Calculation properties
2. Disable them before processing begins
3. 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:

1. Get to the VBE (Alt-F11)
2. From the VBE menu navigate File > Import File…
3. Load modGeneral (see previous post) 
4. Select and copy the code below
5. Paste into the Code Window  *
6. Make any corrections to code that didn’t paste correctly
7. From the VBE menu navigate File > Export File…
8. 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

End Function