Eng-Tips is the largest engineering community on the Internet

Intelligent Work Forums for Engineering Professionals

Congratulations KootK on being selected by the Eng-Tips community for having the most helpful posts in the forums last week. Way to Go!

Make My Code Run Faster

General Use

Make My Code Run Faster

by SkipVought Posted Sep 18, 2017 (Edited Sep 18, 2017)

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

Code:

Application.ScreenUpdating = False
'....[i]Your process here[/i]
Application.ScreenUpdating = True

There is another technique that can contribute to faster, more efficient code processing. Most WorkBook, WorkSheet, Range and other Objects Properties & Methods can be referenced without using the Select or Activate Method. The following comparison test will illustrate the point...

Code:

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 Activating and Selecting

Code:

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 the With...End With construct. The With statement allows you to perform a series of statements on a specified object without requalifying the name of the object. 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

Happy coding

Please Note: 1 is Bad, 10 is Good :-)

Part and Inventory Search

Back

Top Bottom