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 by the following example. Change cell (“D6”) color to yellow and the 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-

    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

 

3 thoughts 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 to no credit check loans Cancel reply

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