Optimize VBA Code to run Macros Faster

1.Turn off Screen Updating

It will help you to stop screen flickering or Screen updating while executing or running macro. So that it will greatly speed up your code and saves lot of time. Please find the following statements for better understand.

Sub Stop_ScreenUpdation ()
Application.ScreenUpdating = False
'...Statemets (Your Code)
Application.ScreenUpdating = True
End Sub

Explanation: Use Application.ScreenUpdating = False at the beginning of your code and Application.ScreenUpdating = True before ending of your code.

2.Turn off ‘Automatic Calculations’

It will help you to prevent calculations while executing or running macro. So that it will greatly speed up your code and saves lot of time. If you don’t mention this may cause too often calculation in workbook. So that performance will come down. Please find the following statements for better understand.

Sub Stop_Calculation()
Application.Calculation = xlCalculationManual
'...Statemets(Your Code)
Application.Calculation = xlCalculationAutomatic
End Sub 

Explanation: Use Application. Calculation = xlCalculationManual at the beginning of your code and Application. Calculation = xlCalculationAutomatic before ending of your code.
Note: Other way of stopping calculation in workbook is change calculation mode is xlCalculationManual. So that Excel does not calculate values in the workbook cells.

3.Disable Events

It will help you to prevent or stop endless loops while executing or running macro if you have worksheet or workbook events. So that it will greatly speed up your code and saves lot of time. Please find the following statements for better understand.

Sub Stop_Events()
Application.EnableEvents = False
'...Statemets
Application.EnableEvents = True
End Sub

Explanation: Use Application. EnableEvents = False at the beginning of your code and Application. EnableEvents = True before ending of your code.

5.Use ‘WITH’ Statement

Use ‘WITH’ statement when working with Objects in macro. If you are using several statements with same object, use a ‘WITH’ statement rather than using all the used statements. Please find the following statements for better understand.

Sub Use_WITH()
With Worksheets("Sheet1").Range("A1")
.Value = 100
.Font.Bold = True
End With
End Sub

‘Instead of using below macro use above macro for faster process.

Sub Without_WITH()
Worksheets("Sheet1").Range("A1").Value = 100
Worksheets("Sheet1").Range("A1").Font.Bold = True
End Sub

Explanation: In the above procedure we have used ‘With’ statement to optimize the macro.

6.Avoid Recording Macro

Always better avoid recording macro. It will show effect on his performance. Please find the below example for better understand on avoid recording macro.
Example: Change cell (“D6”) color to yellow and font is bold.
If you record macro, the code is looking like below.
Recorder Macro:

Sub Macro1()
'
' Macro1 Macro
'
'
Range("D6").Select
Selection.Font.Bold = True
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
End Sub

The recorded macro can also be written like below.
Written Macro:
Please find the below macro, it will change the cell (“C5”) color and font as bold.

Sub Change_Cell_Font()
With Range("C5")
.Font.Bold = True
.Interior.Color = 65535
End With
End Sub

If you compare recorded macro and written macro so much of difference will be there. I think already you stood by seeing above examples.

7.Use vbNullString instead of “”

The ‘vbNullString is a Constant. It denotes a null String. It occupies less memory compares to “” and faster in process and to assign. And “” represents empty string and occupies more memory compares to vbNullString. So always better to use vbNullString instead of “”.

8.Reduce the number of lines using colon (:)

Few statements we can write in a single line instead of multiple lines. Please find the below examples for better understand.

Example1: We can declare the variables in the following way.

Sub Declare_Variables()
Dim iCnt As Integer, jCnt As Integer    
End Sub

‘Instead of using below macro use above macro for faster process.

Sub Declare_Variables1()
Dim iCnt As Integer
Dim jCnt As Integer    
End Sub

The first macro will process faster compares to second procedure.

Example2: Use colon (:) to write multiple statements in a single line in the following way.

Sub Use_Colon_ForMultipleLine()
Dim iCnt As Integer, jCnt As Integer
iCnt = 5: jCnt = 10
End Sub

‘Instead of using below macro use above macro for faster process.

Sub Use_Colon_ForMultipleLine1()
Dim iCnt As Integer, jCnt As Integer
iCnt = 5
jCnt = 10
End Sub

The first macro will process faster compares to second procedure.

9.Use best approach to Copy and Paste

Please find the below different examples for better understand to use best approach to Copy and Paste.
Example:

Sub CopyPaste_Ex2()
Sheets("Source").Range("A1:E10").Copy Destination:=Sheets("Destination").Range("A1")
End Sub

‘Instead of using below macro use above macro for faster process.

Sub CopyPaste_Ex1()
Sheets("Source").Range("A1:E10").Copy
Sheets("Destination").Range("A1").PasteSpecial
Application.CutCopyMode = False
End Sub