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
- Disabling Automatic Calculations
- Disabling Screen Updating
- Disabling Event Notifications
- 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
Make powerful macros with our free VBA Developer Kit Tutorials like this can be complicated. That’s why we created our free VBA Developer Kit to supplement this tutorial. Grab it below and you’ll be writing macros so much faster than you are right now.
Tutorials like this can be complicated. That’s why we created our free VBA Developer Kit to supplement this tutorial. Grab it below and you’ll be writing macros so much faster than you are right now.
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
Sub ReallySlowMacro() Call SpeedOn ' ' ' ' Your really slow macro goes here ' ' ' Call SpeedOff End Sub
Oh, and if you have a question, post it in our VBA Q&A community.
The best free VBA training on the web
I see people struggling with Excel every day and I want to help. That's why I'm giving away my 90-days to Master VBA eCourse and my entire personal macro library for free.