I am a supporter of
St. Joseph's hospice.
 If you find this site useful or if it helped you, consider a small donation to
St. Joseph's, please.

Information on
St. Joseph's

JustGiving - Sponsor me now!

 

SpeedUp

Some people find it sufficient to just write

With Application
    .Calculation = xlCalculationManual
    .DisplayAlerts = False
    .DisplayStatusBar = True
    .EnableEvents = False
    .ScreenUpdating = False
End With

at the beginning of a sub and to write

With Application
    .Calculation = xlCalculationAutomatic
    .DisplayAlerts = True
    .EnableEvents = True
    .ScreenUpdating = True
    .StatusBar = False
End With

at the end.

If you really deem this sufficient I suggest to put the following code into a class module named SpeedUp:

Option Explicit

'Instantiate in a regular module with:
'Dim speedupnow As SpeedUp
'Set speedupnow = New SpeedUp

Private Sub Class_Initialize()
    With Application
        .Calculation = xlCalculationManual
        .DisplayAlerts = False
        .DisplayStatusBar = True
        .EnableEvents = False
        .ScreenUpdating = False
    End With
End Sub
 
Private Sub Class_Terminate()
    With Application
        .Calculation = xlCalculationAutomatic
        .DisplayAlerts = True
        .EnableEvents = True
        .ScreenUpdating = True
        .StatusBar = False
    End With
End Sub

Use

Dim speedupnow As SpeedUp
Set speedupnow = New SpeedUp

at the beginning of your regular module which you like to speed up. Please note that I deem the use of the SystemState class superior. Why? Because it restores the environment on exit.

Sulprobil   Get it done   Contact   Disclaimer   Download