As your knowledge of VBA grows and you begin to write more complex macros, it’s important that you understand how to optimize your code for speed and efficiency. In this article, we’ll take a look at 10 ways to improve your macros’ performance by using good coding practices and applying some simple tricks.URL: https://dzone.com/articles/7-effective-strategies-to-optimize-your-vba-macro
Grabbing data from the spreadsheet over and over again is a big drag on macro performance, so avoid reading and writing worksheet data within loops whenever possible. It is much faster to read the data once and save it into memory than to reread the same data over and over again.
Mastering VBA Optimization: Techniques for Lightning-Fast Macros
Screen flickering, while a macro is running, is a result of Excel having to redraw the screen for each action that your macro takes. It would be nice if you could turn off this feature to make your macros run a little faster.
You can use the ScreenUpdating property to disable screen updates while a macro is running. Then, after the macro finishes, you can set the property back to true to enable updates. Similarly, you can use the CalculationType property to set the calculation type to Manual before executing your macro and then return it to automatic after the macro is finished. This will prevent calculations from updating during your macro’s execution and thereby reduce the total running time of your macro.
