Two cents’ worth to get your VBA code to run faster

Today I am going to discuss simple ways to write the VB programming in the most efficient manner to, optimize the VBA code saving a lot of time for the developers/programmers.

  1. Turn off screen updating: Use Application.ScreenUpdating = False at the beginning of your code and Application.ScreenUpdating = True before ending of your code.
  2. Turn off automatic calculations: Use Application. Calculation = xlCalculationManual at the beginning of your code and Application. Calculation = xlCalculationAutomatic before ending of your code. Note: Another way of stopping calculation in the workbook is to change calculation mode to xlCalculationManual. So that Excel does not calculate values in the workbook cells.
  3. Disable events: Use Application. EnableEvents = False at the beginning of your code and Application. EnableEvents = True before ending of your code.
  4. Use ‘WITH’ statement: Use ‘WITH’ statement when working with Objects in macro.
  5. Avoid Recording Macro: It shall decrease the speed of your macro. Best understood with the following example. Change cell (“D6”) color to yellow and font is bold.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 same recorded macro can also be written as-
    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
    
  6. Use VBNullstring instead of “”: The ‘vbNullString is a Constant. It denotes a null String. It occupies less memory compares to “” and is faster in process and to assign.
  7. 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.

  8. Use better approach to copy-paste: For example, use
    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

    That’s it and your code is ready to work faster than before 🙂

Do subscribe for more Two Cents’ on VBA Programming. Also, check out extensive Excel VBA Macro programming here and learn to automate your Excel Reports, Process and tasks to a single click of button.

Happy Excelling
Team Excelgoodies
www.Excelgoodies.com

 

One thought on “Two cents’ worth to get your VBA code to run faster

  1. no credit check loans

    Hey there just wanted to give you a quick heads up. The text in your content seem to be running off the screen in Chrome. I’m not sure if this is a formatting issue or something to do with browser compatibility but I thought I’d post to let you know. The style and design look great though! Hope you get the problem solved soon. Many thanks

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *