Optimize VBA Code to run Macros Faster

Home/VBA/Optimize VBA Code to run Macros Faster

Please find the following information to optimize VBA code to run macros faster, simple, easy to understand and efficient way of writing macro. It will save lot of time while running macros and best of writing or practice for effective VBA codes or macros in Excel. The following tips or tricks are the best choice for excellent VBA programmers to write codes in an efficient manner.

PREMIUM TEMPLATES LIMITED TIME OFFER

ON SALE80% OFF

BROWSE ALL TEMPLATES

50+ Project Management Templates Pack
Excel PowerPoint Word

VIEW DETAILS

Advanced Project Plan & Portfolio Template
Excel Template

VIEW DETAILS

Business Presentations Templates Pack
PowerPoint Slides

VIEW DETAILS

20+ Excel Project Management Pack
Excel Templates

VIEW DETAILS

20+ PowerPoint Project Management Pack
PowerPoint Templates

VIEW DETAILS

10+ MS Word Project Management Pack
Word Templates

VIEW DETAILS


Why we need to Optimize VBA Code?

We need to Optimize VBA codes to run macros Faster while running VBA macro for efficient VBA programming, to generate good quality of output, to save time, easy to understand codes, etc.

Excel VBAOptimize VBA Code to run Macros Faster

VBA Optimize Technics to Run Macros Faster

We can fasten the execution of macros or VBA Procedures or functions by following or using the below tips or technics.

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
LIMITED TIME OFFER
By |March 14th, 2015|VBA|7 Comments

About the Author:

Excel VBA Developer having around 8 years of experience in using Excel and VBA for automating the daily tasks, reports generation and dashboards preparation. Valli is sharing to helps us automating daily tasks.

7 Comments

  1. tomas April 29, 2015 at 2:11 PM - Reply

    in first code snipet there is twice screen updating = false

    • PNRao April 29, 2015 at 9:37 PM - Reply

      I have rectified the code. It’s a typo error. Thanks for notifying me.

      Regards,
      Valli

  2. Venkat August 31, 2015 at 5:02 PM - Reply

    Hi Mr.P.N.Rao ! In your blog section “OPTIMIZE TOUR VBA CODE ” the following is the text .It seems the code written is contradicting..Kindly let me know if I am wrong ….

    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 = False
    End Sub

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

  3. Charollete February 15, 2016 at 6:00 PM - Reply

    I have followed your script, and utilized it in my code to move files(converted files) from source to destination folder. I have few extensions which cannot be moved from source tod estination, since the error is file not found.

    I have traced the issue that the file which is getting converted and placed in source is more than one file. To be simple the source file is pdf, let say it contains 7 pages and it converts into jpeg image and that will be 7 jpeg files ofthe source. When my amcro executes, it looks for the filename of the source the destination file and concatenates with the destination pathto move the file. since the file name contains a suffix as Filename_Page_1 to Filename_Page_7, this error occurs and couldnt process for few extensions like this.

    If the file extension is word or excel of my source file(pdf- 7 pages) the output is always 1 page. However the issue occurs only when the file extensions or other than this (jpx, jpf, j2k, jpc).

  4. Naveen prajapati March 3, 2017 at 9:30 PM - Reply

    Thank you so much 2 no. Automatic Calculation is good for me

  5. Nick April 12, 2017 at 2:20 PM - Reply

    I’m an Excel developer with 12 years experience, and thought I’d let you know that I never knew that the colon (:) to reduce lines in the VBA script could speed up the VBA process. Great tip !

Leave A Comment