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 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
- 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