×

Best Way To Improve VBA Macro Performance (Prevent Slow Code!)

By Chris Newman •  Updated: 02/25/15 •  5 min read
Increase VBA Macro Code Performance And Prevent Slow Code Execution

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 of finding "10 ways to speed up your code", I suggest you check out one of these great articles:

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 Sub

Second Piece of VBA Code

Sub OptimizeCode_End()

ActiveSheet.DisplayPageBreaks = PageBreakState
Application.Calculation = CalcState
Application.EnableEvents = EventState
Application.ScreenUpdating = True

End Sub

Next, 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 Sub

Why 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 can 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.

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'!

Keep Learning

Chris Newman

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.