how to use Application.EnableEvents = False?

[ZionX]

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.

 

[Colin Legg]

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:

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:

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.

 

[ZionX]

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.