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.
- Turn off screen updating: Use Application.ScreenUpdating = False at the beginning of your code and Application.ScreenUpdating = True before ending of your code.
- 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. - Disable events: Use Application. EnableEvents = False at the beginning of your code and Application. EnableEvents = True before ending of your code.
- Use ‘WITH’ statement: Use ‘WITH’ statement when working with Objects in macro.
- 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
- 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.
- 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.
- 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
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
Thanks for sharing your experience.
Turning off the calculations was indeed insightful. Hope this should reduce my running time. Thanks!