Get Your Macros to work faster – Event Handling

Written by Amey Dabholkar

August 15, 2015

In the series of Blog posts we are going to make you will find various tips and tricks regarding how to speed up calculation of macros in Excel VBA. This is the first among the other articles.

Most of the time we make a great automation, great program and great software in Microsoft Excel. but we encounter a problem that all our automation is a bit slow. They take time to calculate and they are slow for each and every action. In a project of mine it once took around 3 minutes to run a simple sort macro. Lets learn the causes and the solution to this problem

VBA Enable Events Application usage

When you run a macro at that time many activities or processes are running behind the excel and that processes make the calculation of our macro a bit slow.

For Example in an event of sort we fist change some cells in worksheet and at that time worksheet_change() event gets triggered. Even if you add a save macro then it will first trigger Private Sub Workbook_BeforeSave() and then it will run and after running macro it will run Private Sub Workbook_AfterSave so such interlinking happens when you create each and every procedure

Now, if you want to avoid the unnecessary events to get triggered (Events colored with Red) then you have to add Application.Enableevents = False and then you need to write down the code. After completion of your code make sure to add application.enableevents = True so as to re enable the same.

Format of the Application Enable Events

Sub Enable_Events_Demo()
Application.EnableEvents = False
' //////Your Piece of Code Here //////
Application.EnableEvents = True
End Sub

This is the basic format required to accomplish the goal of making your calculation a bit quicker. You could add any code into it. Make sure to re enable the Enable Events option for making the next piece of code worked normal. Thanks

Liked this Article ? Let me Know in the comments.

Please Share,



Submit a Comment

Your email address will not be published. Required fields are marked *

Pin It on Pinterest

Share This