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.

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
Premium Project Management Templates
120+ Professional Project Management Templates!

A Powerful & Multi-purpose Templates for project management. Now seamlessly manage your projects, tasks, meetings, presentations, teams, customers, stakeholders and time. This page describes all the amazing new features and options that come with our premium templates.

PREMIUM TEMPLATES
LIMITED TIME OFFER
ON SALE85% OFF
BROWSE ALL TEMPLATES

Advanced Project Planning Templates

Excel Templates

VIEW DETAILS

120+ Project Management Templates Pack

Excel | PowerPoint | Word

VIEW DETAILS

ULTIMATE RESOURCE MANAGEMENT TEMPLATE

Excel Template

VIEW DETAILS

50+ Essential Project Management Templates

Excel | PowerPoint | Word

VIEW DETAILS

Project Portfolio Management Templates

Excel | PowerPoint Templates

VIEW DETAILS

50+ Excel Project Management Templates

Excel Templates

VIEW DETAILS

By Published On: March 14th, 2015Categories: VBATags: , ,

Share This Story, Choose Your Platform!

About the Author: Valli

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.

11 Comments

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

    in first code snipet there is twice screen updating = false

    • PNRao April 29, 2015 at 9:37 PM

      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

    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

    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

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

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

    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 !

  6. Sabhajeet kumar November 4, 2017 at 3:15 AM

    Great explain sir.
    Heartly thanks

  7. Paul August 8, 2019 at 8:27 AM

    Disable Events
    This should be done within a code block being careful to note when an event should be triggered for proper function and when it should not e.g. should you change the active worksheet and there is a macro attached to the activate event of that worksheet, it will not be triggered if events are disabled.

  8. Jaka Strojansek September 28, 2020 at 3:10 PM

    Hello! I have written an app in excel environment that includes multiple macros. After a few runs the app is noticeably slower. Can I send you vba so you can double check if there is any room for improvement? Thank you for your help. Greetings from Slovenia, Jaka.

  9. Hak Sokly November 29, 2020 at 6:56 AM

    I could not run the code of Copy and Paste Approach. Could you explain me about the sheets(“destination”) and sheets(“source”)? What do they mean to you?

Leave A Comment