The Best Way To Improve VBA Macro Performance And Prevent Slow Code Execution

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 to find "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 want 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 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 image 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 constantly recalculate, 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.  You can find this code over at the Code Vault section of my website.

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 trigger 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 at a worst-case scenario, your spreadsheet being altered and screwing up what your VBA code was intending 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'!

Share This Post!

Did you find this post helpful? Do you want to support this blog because you're just that awesome?!  By sharing this post on FacebookTwitter, or Google+ you are not only providing others with great information, you are creating exposure for The Spreadsheet Guru website.  Growing this community is my number one goal as I have found learning to increase exponentially when lots of people are contributing to the conversation.  Learning is the whole reason why this blog exists!  If you want to spread the word just click on the Share button right below this paragraph (next to the Like button -- I enjoy "likes" too!) or on the Floating Share Bar to the left and select your preferred social medium.  Thank you so much for reading and I hope I can continue to provide you with great content in the future!  Cheers!