VBA Express : Excel - Speed Up Code

Excel

Speed Up Code

Ease of Use

Easy

Version tested with

2003

Submitted by:

Kenneth Hobs

Description:

Two routines to turn macro code speed features on and off.

Discussion:

Calculation mode, screen updating and such can cause macro routines to be slow. This is particularly so when using routines that the macro recorder creates using Select. While this should generally be avoided, SpeedOn() can make even the use of Select faster. A global variable stores the users current calculation mode in SpeedOn(). SpeedOff() will reset the calculation mode from manual to the mode previously set by the user. This is a bit better than forcing calculation mode to automatic in some cases.

Code:

instructions for use

			Option Explicit 
 
Public glb_origCalculationMode As Integer 
 
Sub SpeedOn(Optional StatusBarMsg As String = "Running macro...") 
    glb_origCalculationMode = Application.Calculation 
    With Application 
        .Calculation = xlCalculationManual 
        .ScreenUpdating = False 
        .EnableEvents = False 
        .DisplayAlerts = False 
        .Cursor = xlWait 
        .StatusBar = StatusBarMsg 
        .EnableCancelKey = xlErrorHandler 
    End With 
End Sub 
 
Sub SpeedOff() 
    With Application 
        .Calculation = glb_origCalculationMode 
        .ScreenUpdating = True 
        .EnableEvents = True 
        .DisplayAlerts = True 
        .CalculateBeforeSave = True 
        .Cursor = xlDefault 
        .StatusBar = False 
        .EnableCancelKey = xlInterrupt 
    End With 
End Sub 
 
Sub FillSlow() 
    Dim c As Range, r As Range, startTime, EndTime 
    Set r = Range("A1:C1000") 
    r.ClearContents 
    startTime = Timer 
    For Each c In r 
        c.Select 
        c.Formula = "=Row()*Column()" 
    Next c 
    DoEvents 
    EndTime = Timer 
     
    MsgBox "Total Time: " & EndTime - startTime 
    [A1].Select 
End Sub 
 
 
 
Sub FillFast() 
    Dim c As Range, r As Range, startTime, EndTime 
    Set r = Range("A1:C1000") 
    r.ClearContents 
    startTime = Timer 
     
    On Error GoTo ResetSpeed 
    SpeedOn 
     
    For Each c In r 
        c.Select 
        c.Formula = "=Row()*Column()" 
    Next c 
    DoEvents 
    EndTime = Timer 
     
    MsgBox "Total Time: " & EndTime - startTime 
    [A1].Select 
     
ResetSpeed: 
    SpeedOff 
End Sub

How to use:

Open the Visual Basic Editor by Alt+11.
Click the Insert menu and select Module.
Paste the code into the Module.
Note how the Sub FillFast() makes use of an On Error routine to insure that SpeedOff() is executed should an error occur.
Type SpeedOff as the last line of your macro after some label that an error would go to.
Select the menu File and then Close.

Test the code:

Open the SpeedOnOff.xls file.
Click the "No Speed" button. Note the time shown in the MsgBox.
Click the "With Speed" button. If you glance in the statusbar, you will see a default message which can optional be set as the first parameter. Note the time shown in the MsgBox.

Sample File:

SpeedOnOff.zip 40.83KB

Approved by mdmackillop

This entry has been viewed 445 times.