Best Way To Improve VBA Macro Performance (Prevent Slow Code!)
This topic has been written about in detail many times, but those who are new to VBA or even computer coding, in general, may want a simple approach without all the nerd jargon. If you began reading this article in hopes to find “10 ways to speed up your code”, I suggest you check out one of these great articles:
- 17 ways to Optimize VBA Code for FASTER Macros
- Excel 2010 Performance: Tips for Optimizing Performance Obstructions
Now I’m going to share with the rest of you a simple copy & paste snippet of code to guarantee faster speeds in your code’s execution. There are two steps. First, you have to copy the two subroutines below and paste them at the top of a module within your VBA Project.
First Piece of VBA Code
Public CalcState As Long
Public EventState As Boolean
Public PageBreakState As Boolean
Sub OptimizeCode_Begin()
Application.ScreenUpdating = False
EventState = Application.EnableEvents
Application.EnableEvents = False
CalcState = Application.Calculation
Application.Calculation = xlCalculationManual
PageBreakState = ActiveSheet.DisplayPageBreaks
ActiveSheet.DisplayPageBreaks = False
End SubSecond Piece of VBA Code
Sub OptimizeCode_End()
ActiveSheet.DisplayPageBreaks = PageBreakState
Application.Calculation = CalcState
Application.EnableEvents = EventState
Application.ScreenUpdating = True
End SubNext, you will need to Call the respective subroutines at the beginning and end of your macro. An example could look something like this:
Sub MyAwesomeMacro()
Dim x As Long
Dim y As Long
Dim cell As Range
'Optimize Code
Call OptimizeCode_Begin
'Insert your macro code here...
x = Range("A1")
y = Range("A3")
For Each cell In Range("C2:C2000)")
cell.Value = (x - cell.Row) / y
Next cell
'Optimize Code
Call OptimizeCode_End
End SubWhy Does This Code Speed Up My Macros?
The above VBA code I just shared with you is simply turning off settings from within your Office application that wants to use your computer’s memory to report out on your computer screen every single thing your macro is doing (in real-time). This slows your computer’s processing power down and in turn decreases your computer’s ability to run your code as fast as it can. Your computer is simply trying to do too many things at once.
Let’s look at these settings in detail to appease those of you who want to know why this code works.
1. Screen Updating
The first macro I ever ran was a co-worker’s that took 30 minutes to finish executing (it did a lot of stuff and was very poorly written). I would always say she was trying to brainwash me through the macro because there was non-stop screen-flickering for 30 straight minutes! Little did I know that a single line of code would have saved my eyes (and mind) from deterioration and at the same time speed up the code’s runtime dramatically. 99.9% of the time, you will ALWAYS want to turn OFF Screen Updating. The only scenario I can think of where you might want it on is if your code is displaying some sort of animation on the screen.
2. Displaying Page Breaks
I have a personal hatred for the visualization of page breaks, but I suppose there are a few of you out there who might like them. One bad thing about page breaks when you have VBA code running is they want to recalculate the “breaks” whenever a change is made to the spreadsheet. You could imagine the time consumption that might take place if you are running VBA code that is deleting or adding thousands of rows to a spreadsheet. Because Page Breaks need to be constantly recalculated, it is good to play it safe and shut them off while your code is being executed
Now Page Breaks are different from the other settings I cover in this article. A Page Break is a worksheet-level setting rather than an application-level setting. This means there is a “light switch” in each worksheet of your workbook. To keep this article’s code clean, I assumed the macro would only affect the ActiveSheet. If your code will be modifying multiple worksheets, you may want to turn off the displaying of Page Breaks for all the sheets getting affected. If you want to be an overachiever, you can add some VBA that turns off every single Page Break currently in existence during your code’s runtime.
3. Calculation Modes
If your code is affecting cells that feed cell formulas, Excel will try to recalculate the values in real-time. Significant runtime efficiency can be made if you turn automatic calculations off while your VBA code is executing. But make sure you turn the calculation setting back to automatic or semi-automatic or you will cause extreme confusion to yourself or your user.
4. Enable Events
VBA has the ability to be triggered by “events” that occur while a user is in Excel. These “events” can range from activating a specific worksheet to being triggered by any change made on the spreadsheet. Events will still be triggered and run while your VBA code is executing. This could lead to extra lines of code being executed and in a worst-case scenario, your spreadsheet being altered and screwing up what your VBA code was intended to do. Turning off these event triggers temporarily will make your code run much more smoothly if you or your user happens to be using VBA code that is kicked off via events.
More Great Articles To Improve Speed
Below are a few links to more articles on the subject of speeding up your VBA code.
Go Forth And Optimize!
So now you have a very simple way to make your code run much more efficiently and you (hopefully) understand why you need to temporarily shut off these settings! Let me know how much time this method shaved off your code’s runtime in the comments section below. I’d love to hear your story! (Is it as crazy as my 30-minute macro experience?)
Bonus points go to the winner for ‘Most Time Saved’!
After 10+ years of creating macros and developing add-ins, I've compiled all the hacks I wish I had known years ago!
Keep Learning
Chris Newman
Chris is a finance professional and Excel MVP recognized by Microsoft since 2016. With his expertise, he founded TheSpreadsheetGuru blog to help fellow Excel users, where he shares his vast creative solutions & expertise. In addition, he has developed over 7 widely-used Excel Add-ins that have been embraced by individuals and companies worldwide.