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