Please find the following information to optimize VBA code to run macros faster, simple, easy to understand and efficient way of writing macro. It will save lot of time while running macros and best of writing or practice for effective VBA codes or macros in Excel. The following tips or tricks are the best choice for excellent VBA programmers to write codes in an efficient manner.
Why we need to Optimize VBA Code?
We need to Optimize VBA codes to run macros Faster while running VBA macro for efficient VBA programming, to generate good quality of output, to save time, easy to understand codes, etc.
VBA Optimize Technics to Run Macros Faster
We can fasten the execution of macros or VBA Procedures or functions by following or using the below tips or technics.
1.Turn off Screen Updating
It will help you to stop screen flickering or Screen updating while executing or running macro. So that it will greatly speed up your code and saves lot of time. Please find the following statements for better understand.
Sub Stop_ScreenUpdation () Application.ScreenUpdating = False '...Statemets (Your Code) Application.ScreenUpdating = True End Sub
Explanation: Use Application.ScreenUpdating = False at the beginning of your code and Application.ScreenUpdating = True before ending of your code.
2.Turn off ‘Automatic Calculations’
It will help you to prevent calculations while executing or running macro. So that it will greatly speed up your code and saves lot of time. If you don’t mention this may cause too often calculation in workbook. So that performance will come down. Please find the following statements for better understand.
Sub Stop_Calculation() Application.Calculation = xlCalculationManual '...Statemets(Your Code) Application.Calculation = xlCalculationAutomatic End Sub
Explanation: Use Application. Calculation = xlCalculationManual at the beginning of your code and Application. Calculation = xlCalculationAutomatic before ending of your code.
Note: Other way of stopping calculation in workbook is change calculation mode is xlCalculationManual. So that Excel does not calculate values in the workbook cells.
3.Disable Events
It will help you to prevent or stop endless loops while executing or running macro if you have worksheet or workbook events. So that it will greatly speed up your code and saves lot of time. Please find the following statements for better understand.
Sub Stop_Events() Application.EnableEvents = False '...Statemets Application.EnableEvents = True End Sub
Explanation: Use Application. EnableEvents = False at the beginning of your code and Application. EnableEvents = True before ending of your code.
5.Use ‘WITH’ Statement
Use ‘WITH’ statement when working with Objects in macro. If you are using several statements with same object, use a ‘WITH’ statement rather than using all the used statements. Please find the following statements for better understand.
Sub Use_WITH() With Worksheets("Sheet1").Range("A1") .Value = 100 .Font.Bold = True End With End Sub
‘Instead of using below macro use above macro for faster process.
Sub Without_WITH() Worksheets("Sheet1").Range("A1").Value = 100 Worksheets("Sheet1").Range("A1").Font.Bold = True End Sub
Explanation: In the above procedure we have used ‘With’ statement to optimize the macro.
6.Avoid Recording Macro
Always better avoid recording macro. It will show effect on his performance. Please find the below example for better understand on avoid recording macro.
Example: Change cell (“D6”) color to yellow and font is bold.
If you record macro, the code is looking like below.
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 recorded macro can also be written like below.
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
If you compare recorded macro and written macro so much of difference will be there. I think already you stood by seeing above examples.
7.Use vbNullString instead of “”
The ‘vbNullString is a Constant. It denotes a null String. It occupies less memory compares to “” and faster in process and to assign. And “” represents empty string and occupies more memory compares to vbNullString. So always better to use vbNullString instead of “”.
8.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.
Example2: Use colon (:) to write multiple statements in a single line in the following way.
Sub Use_Colon_ForMultipleLine() Dim iCnt As Integer, jCnt As Integer iCnt = 5: jCnt = 10 End Sub
‘Instead of using below macro use above macro for faster process.
Sub Use_Colon_ForMultipleLine1() Dim iCnt As Integer, jCnt As Integer iCnt = 5 jCnt = 10 End Sub
The first macro will process faster compares to second procedure.
9.Use best approach to Copy and Paste
Please find the below different examples for better understand to use best approach to Copy and Paste.
Example:
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
Premium Project Management Templates
50+ Professional Project Management Templates!
A Powerful & Multi-purpose Templates for project management. Now seamlessly manage your projects, tasks, meetings, presentations, teams, customers, stakeholders and time. This page describes all the amazing new features and options that come with our premium templates.
LIMITED TIME OFFER
50+ Project Management Templates Pack
Excel PowerPoint Word
Ultimate Project Management Template – Advanced
Excel Template
ULTIMATE PROJECT MANAGEMENT TEMPLATE
Excel Template
20+ Excel Project Management Pack
Excel Templates
20+ PowerPoint Project Management Pack
PowerPoint Templates
10+ MS Word Project Management Pack
Word Templates