To Optimize VBA Code for FASTER Macros_1

1. Analyze the Logic :-

Before optimizing the syntax, pay more attention in optimizing the logic. Without a good logic, a good written VBA macro program has no value. So streamline your program logic and get the best performance of macros.

2. Turn off ScreenUpdating

Avoid 'Screen Flickering' or 'Screen Repainting': Use
Application.ScreenUpdating = False 'To Turn Off at the start of code.
Application.ScreenUpdating = False 'To Turn on at the end of the code.

The ScreenUpdating property controls most display changes on the monitor while a procedure is running. When screen updating is turned off, toolbars remain visible and Word still allows the procedure to display or retrieve information using status bar prompts, input boxes, dialog boxes, and message boxes. You can increase the speed of some procedures by keeping screen updating turned off. You must set the ScreenUpdating property to True when the procedure finishes or when it stops after an error.

3. Turn off 'Automatic Calculations'

Whenever content(s) of a cell or range of cells are changed, the formulas dependent on them and Volatile functions are recalculated.

You may turn off the automatic calculation using
Application.Calculation = xlCalculationManual 'To turn off the automatic calculation
Application.Calculation = xlCalculationAutomatic 'To turn On the automatic calculation

Now, whenever due to the program logic(that due to macros dependent on existing formulas) you need to calculate the formulas, you may use the following code accordingly.

ActiveSheet.Calculate ' To calculate the formulas of Active Worksheet
Application.Calculate ' To calculate the formulas of Active workbook or all workbooks in current application.

4. Disable Events

Use Application.EnableEvents to tell VBA processor whether to fire events or not. We rarely fire an event for each cell we're changing via code. Hence, turning off events will speed up our VBA code performance.