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
'....Your process here
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
Learn methods and guidelines for using stereolithography (SLA) 3D printed molds in the injection molding process to lower costs and lead time. Discover how this hybrid manufacturing process enables on-demand mold fabrication to quickly produce small batches of thermoplastic parts. Download Now
Examine how the principles of DfAM upend many of the long-standing rules around manufacturability - allowing engineers and designers to place a part’s function at the center of their design considerations. Download Now
Metal 3D printing has rapidly emerged as a key technology in modern design and manufacturing, so it’s critical educational institutions include it in their curricula to avoid leaving students at a disadvantage as they enter the workforce. Download Now
This ebook covers tips for creating and managing workflows, security best practices and protection of intellectual property, Cloud vs. on-premise software solutions, CAD file management, compliance, and more. Download Now
Talk To Other Members
