Speed up your VBA code with this pair of macros from the wellsrPRO community. These subroutines can dramatically increase the speed of your VBA macros by

  1. Disabling Automatic Calculations
  2. Disabling Screen Updating
  3. Disabling Event Notifications
  4. Disabling Alerts

The macros in this post were submitted by user Jomili.


Reader’s Note: This is the second article in a new series featuring macros submitted by my incredible wellsrPRO community members. These articles are similar to my Code Library articles in that they usually won’t contain an accompanying detailed tutorial.

If you’re an existing wellsrPRO member, don’t forget to submit your own macro to the wellsrPRO community using the Share My Macros button.

wellsrPRO users can automatically import this community submission directly into their spreadsheet. Just look for “Community Submissions” in the Auto-Import dropdown menu.


Speed up your VBA Macros

Created by Jomili

Sub SpeedOn()
    'Turns off the time wasters
    With Application
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
        .EnableEvents = False
        .DisplayAlerts = False
        .Cursor = xlWait
        .EnableCancelKey = xlErrorHandler
    End With
End Sub

Like Jomili says in his comment, the macro above turns off time wasters that may slow down your VBA code. To turn them back on, use the following macro:

Return to Defaults

Created by Jomili

Sub SpeedOff()
    'Turns on the time wasters
    With Application
        .Calculation = xlCalculationAutomatic
        .ScreenUpdating = True
        .EnableEvents = True
        .DisplayAlerts = True
        .Cursor = xlDefault
        .StatusBar = False
        .EnableCancelKey = xlInterrupt
    End With
End Sub

Tutorials like this can be complicated. That’s why we created our Automatic Macro Generator to supplement this tutorial. Fill out the form at the bottom of this tutorial and you’ll be writing better macros in no time. Still need more guidance? You can work with me directly by contacting me from my VBA Consulting page.

You can adapt these codes to disable and enable other application features that may be slowing down your macro.

Using thes macros to speed up your code

To speed up your macro, all you have to do is call the SpeedOn macro at the beginning of your slow macro and call the SpeedOff macro at the end of it. Here’s an example:

Sub ReallySlowMacro()
Call SpeedOn
'
'
'
' Your really slow macro goes here
'
'
'
Call SpeedOff
End Sub

If this tutorial helped speed up your macros, please share it on Facebook and Twitter. Then, when you’re ready to take your VBA to the next level, subscribe using the form below.

This is how experts write better macros in half the time

I see people struggling with Excel every day and I want to help. That's why I'm giving away my entire personal macro library for free. This free gift lets you automatically import over 200 of my best macros directly into your spreadsheet so you can dramatically speed up your VBA writing process.

Show me more VBA tips made for experts!