how to use Application.EnableEvents = False?

ZionX

New Member
Joined
Mar 17, 2011
Messages
11
Good days everyone, I know that if set Application.EnableEvents to false will boost up the speed of macro, but after a batch of googling, i still didn't get how to use it, can some one please explain to me? thank you so much.
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Colin Legg

MrExcel MVP
Joined
Feb 28, 2008
Messages
3,497
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hi,

Application.EnableEvents = False stops Excel event handlers from being called. Setting it to false is usually done because the effect of the event handler is undesirable or to prevent an infinite loop. For example, the below procedure:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$A$1" Then
        Target.Value = 1
    End If
End Sub
This is a worksheet event handler which is called when the value of any cell on the worksheet is changed. The code checks to see if A1 was changed and, if it was, to override the value in A1 with the value 1.

The problem with this code is, if it changes the value in A1, it results in the Worksheet_Change event handler being called again. It ends up in an infinite loop, calling itself over and over. To stop this behaviour, you can set Application.EnableEvents to False like this:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    
    On Error GoTo ErrorHandler
    
    If Target.Address = "$A$1" Then
        Application.EnableEvents = False
        Target.Value = 1
    End If
ErrorExit:
    
    Application.EnableEvents = True
    
    Exit Sub

ErrorHandler:

    Debug.Print Err.Number & vbNewLine & Err.Description
    Resume ErrorExit
End Sub
It's important to introduce an error handler to ensure that Application.EnableEvents is set back to True again before the procedure ends - even if an error occurs.


So, Application.EnableEvents is not particularly used to boost up the speed of VBA code, although it can have that effect in certain situations. Perhaps you were thinking of Application.ScreenUpdating? This will often greatly improve performance because Excel doesn't have to keep repainting the screen each time you do something to the grid.
 
Last edited:

ZionX

New Member
Joined
Mar 17, 2011
Messages
11
Thanks Colin, i think i get what it mean. I already put in the manual calculation and disable screenupdating, anyway really appreciate and thanks for your advice.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,115
Messages
5,768,198
Members
425,459
Latest member
Danniey

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top