Beyond Excel: VBA and Database Manipulation

Oct 23 2009   4:45PM GMT

Building a Library of Routines – Settings

Craig Hatmaker Craig Hatmaker Profile: Craig Hatmaker

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
                    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
    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

 Comment on this Post

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

Share this item with your network: