I usually use a procedure I defined which I named “Optimize VBA”.
It is not only more efficient but also much easier to read, and simplifies your code.
This is better: It is good to handle turning off some of the optimizations mentioned above.
There are many different articles and post on improving Excel performance.
What I personally was missing was a simple, comprehensive, short overview of how the performance of VBA macros can be improved without needing to read through long articles on how the Excel compiler works bla bla bla.
Option Explicit Sub Optimize VBA(is On As Boolean) Application. Display Page Breaks = Not(is On) End Sub 'Some macro Sub Example Macro() Optimize VBA True 'Your code here Optimize VBA False End Sub Turning Screen Updating and Calculations off although improves VBA performance can sometimes causes some issues.
Calculation = IIf(is On, xl Calculation Manual, xl Calculation Automatic) Application. In case calculations are turned off do not expect that Excel with recalculate its formulas based on any changes made to your Worksheets.
This causes some additional overhead when your VBA code is executed especially if this variable is used often in your code: A known way to improve Excel VBA speed and efficiency, especially fore large Excel files, is to save your Workbooks in binary XLSB format.
To read more on the benefits read my post on the XLSB format.
There are several known methods for measuring VBA code execution time.
The Performance Module of the VBA Time Saver contains at least 2 methods for measuring execution time as well as other way for Improving VBA Performance.
Option Explicit requires that you declare ALL VARIABLES.