In the past, I’ve given an introduction to building excel macros, either by recording or by directly writing code into the program. Macros are an amazing way to automate and optimize tasks while also diminishing errors. That being said, especially when starting, macros sometimes take a bit of time to run. Why? They do not have an optimal code. Here are 3 important things to do in order to avoid having a macro run for ages:
-Only Use Loops For Specific Circumstances
Loops can be the best thing ever but if you have to do a loop through a few hundred lines, you might grow grey hair in the process. It’s important to use loops only if you know that the amount of data can support it. If you are unsure, I would suggest using other options when possible such as an advanced filter, formulas, etc.
-Use A Log
One great option when you have macros with many different subsections is to write down in a separate sheet, the time at specific parts of the macro. This will help you determine which steps are taking longer to run, in order to further optimize those parts.
-Reduce Calculations/Processing Time
Several things can be done in order to improve the speed of your macro. One thing is to allow Excel to run it without you seeing every step. You can also diminish the number of warnings that excel will give you, set calculations to manual (instead of automatic). Here are some lines that can be added at the start of your macro:
With Application
.EnableEvents = False
.ScreenUpdating = False
.DisplayAlerts = False
.Calculation = xlManual
End With
Then you would simply set everything back at the end of the macro:
With Application
.EnableEvents = True
.ScreenUpdating = True
.DisplayAlerts = True
.Calculation = xlAutomatic
End With
All of those things can end up making your macro 2 times, 5 times or even 100 times faster depending on what you’re working on so they are without a doubt worth looking into.
***************************************************
Look Good at Work and Become Indispensable Become an Excel Pro and Impress Your Boss
***************************************************
