Get Your Macros to work faster – Event Handling

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,

Related Articles

Interactive Calendar Template – Announcement

Features of this template This template allows to save your daily task and display it in an interactive way. Many things to learn. This template makes it handy to display all the tasks in a form of the calendar and combined with formulas and VBA one can make it even...

read more
Macro Efficiency: Disabling Screen Updating

Macro Efficiency: Disabling Screen Updating

This is part 2 of the Macro Efficiency series wherein we teach you how to code efficiently using Microsoft Excel VBA. This method which we are going to discuss today is recognized as one of the basic and the most important method for making the code efficient. The...

read more

Lorem ipsum dolor sit amet consectetur


Submit a Comment

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

Pin It on Pinterest

Share This