Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
How Can I Make My Code Run Faster?
VBA How To
To make a procedure run faster, it is common practice to include a command at the BEGINNING & END of a process
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...
Case 1 runs 5 TIMES FASTER than Case 2!
Even a Copy/Paste can be done without Activating and Selecting
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
Code:
Application.ScreenUpdating = False
'...[i]Your code here[/i]
Application.ScreenUpdating = TrueThere 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 SubEven 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 SubThe 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