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.
- The ScreenUpdating property
- Avoiding the use of the Select and Activate methods
- 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 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
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.