How Can I Make My Code Run Faster?

[SkipVought]

This suggestion will address 3 techniques you can use to increase performance of your VBA code (ie. decrease the run time). If you have any other suggestions that produce positive results, we all would welcome your suggestions.

1. The ScreenUpdating property
2. Avoiding the use of the Select and Activate methods
3. Employing With…End With

To make a procedure run faster, it is common practice to include a command at the BEGINNING & END of a procedure:

Application.ScreenUpdating = False
...Your code here
Application.ScreenUpdating = True

You can see how ScreenUpdating is used in the next technique.

The second technique: Most WorkBook, WorkSheet, Range and other Objects, Properties & Methods can be referenced without using the Select or Activate Method. Referencing objects instead of Selecting or Activating objects will increase your code’s performance.

The following comparison test will illustrate the point…

Sub CompareMethods()
    Dim TimeStart As Date, TimeEnd As Date, i As Byte, j As Long
    Application.ScreenUpdating = False
    For i = 1 To 2
        TimeStart = Now
        For j = 1 To 65000
            Select Case i
            Case 1
                Sheet1.Cells(j, 2).Value = j
                Sheet2.Cells(j, 2).Value = j
            Case 2
                With Sheet1
                    .Activate
                    .Cells(j, 2).Select
                    Selection.Value = j
                End With
                With Sheet2
                    .Activate
                    .Cells(j, 2).Select
                    Selection.Value = j
                End With
            End Select
        Next
        TimeEnd = Now
        Sheet1.Cells(i, 1).Value = TimeEnd - TimeStart
    Next
    Application.ScreenUpdating = True
End Sub

Case 1 runs 5 TIMES FASTER than Case 2!

Even a Copy/Paste can be done without Activate or Select.

Sub CopyAndPaste()
    Range(Sheet1.Cells(1, 1), Sheet1.Cells(3, 3)).Copy _
        Destination:=Sheet2.Cells(2, 5)
End Sub

The final technique is the use of With...End With.

The With statement allows you to perform a series of statements on a specified object without your code having to re-qualify an object, which takes time.

Running CompareMethods without With...End With, adds about 10% to the run time.

Organize your code. Look for opportunities to nest With...End With within With...End With.

Ask for help here at SimpiEngineering.com if you are working to optimize code that seems to run too long, after you’ve tried these techniques.

Happy coding