Using a technique described in this StackOverflow answer by @David Murdoch I created a custom class with methods to speed up Excel (
Activate turns off screen updates and set the calculation mode to manual) and to restore it to normal settings (
Shutdown). When the object reaches the end of its life and
Class_Terminate is triggered it runs the
The purpose of all this is to avoid problems that occur if (when) I forget to reset these settings at the end of a script or if my code (contains a bug that) skips the section resetting these settings.
I am looking for a broad critique of the nit-picky variety. In other words, how you would code this.
This code intentionally ignores the initial state of the settings and always turns on screen updating and sets the calculation mode to automatic when the object is destroyed.
The following is in a class module called
Option Explicit ' ' Use the code below to turn off screen updates and automatic ' calculations. Screen updates and automatic calculations will be ' restored when the calling procedure terminates. ' ' EXAMPLE USAGE ' ====================================================================== ' Dim objExcelTurboBooster As New ExcelTurboBooster ' objExcelTurboBooster.Activate ' Activates booster ' objExcelTurboBooster.Shutdown ' Use to deactivate before procedure ' ' terminates ' ====================================================================== ' ' Public Sub Activate() Application.Calculation = xlCalculationManual Application.ScreenUpdating = False End Sub Public Sub Shutdown() On Error Resume Next Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True End Sub Private Sub Class_Terminate() Shutdown End Sub