3 Simple Ways To Speed Up Your Excel Macros

Hello Excellers time for some more #macromonday Excel fun. Today I want to share with you my top 3 ways to speed up your Excel macros. Depending on how complex your VBA macro becomes, you may find that your macros take a while to run. So, here are my go to top 3 ways to speed up your macros and to increase performance.

You may find that when you run an Excel macro, you get some screen flickering. So, here is the first recommendation to speed things up.

Disable The Application.ScreenUpdating Property.

By disabling the Application, ScreenUpdating property we can prevent that flickering in Excel, which in turn saves on the Excel memory resources. Once your macro has run you then turn on the screen updating again. Usually, this piece of VBA code is placed before your main coding instructions to Excel before any procedures have run. I have an example of how to write these instructions into your code below.

Sub MyMacro()

Application.ScreenUpdating = False

‘Place your macro code here

Application.ScreenUpdating = True

End Sub

You can see that your code sits in between the VBA instructions to turn off the screen updating (Application.ScreenUpdating = False) and turning it back on again (Application.ScreenUpdating=False).

Disable The Status Bar Updates.

The status bar in Excel will automatically display the progress of specific tasks it is running, and if your macro is crunching a lot of information this status bar can also take up precious Excel resources. This, therefore, brings me to my second recommendation to improve performance. Even if you apply the first recommendation of disabling the screen, status bar updates still are applied. So, by disabling this update also we can save some Excel memory again and help with performance. Once your code has run then you can instruct Excel to turn back on the updates. I have an example of how to write these instructions into your code below.

Sub MyMacro()

Application.DisplayStatusBar= False

‘Place your macro code here

Application.DisplayStatusBar = True

End Sub

You can see that again this code sits in between the VBA instructions to turn off the status bar and turn it back on again.

Disable The Application.EnableEvents Property

An event is fired when something happens while a user is in Excel. Events can range from a pop-up message box or some sort of warning to the user. These events can lead to extra lines of code being generated, and can sometimes interfere with your intended outcome of code. Turning off events whilst your code is running can help it to run better, but again you need to enter a line of code to turn events back on again after your VBA code has executed. I have an example of how to write these instructions into your code below.

Sub MyMacro()

Application.EnableEvents= False

‘Place your macro code here

Application.EnableEvents = True

End Sub

If you want to disable all of these events then simply combine the bits of code together before your VBA macro code and then enable the altogether again after your code.