VBA Tip: Disable Events
Sometimes it can be useful to disable events... Imagine, for example, that the Worksheet_Change event (which is triggered after a modification in the worksheet) is used to modify cell A1 on the same worksheet.
In such a case, upon the first modification of any cell on that worksheet, cell A1 will be modified... But since cell A1 is modified, the event is triggered again... Cell A1 will be modified once more and will trigger the event again... And so on... Creating an infinite loop and crashing Excel.
Therefore, it's necessary to prevent the event from being triggered when A1 is modified.
Disable All Events using "Application.EnableEvents"
The simplest solution to execute code without triggering events is to place the code between these two lines using Application.EnableEvents
:
Private Sub Worksheet_Change(ByVal Target As Range)
'Disable all events
Application.EnableEvents = False
'Example action
[A1] = [A1] + 1
'Re-enable all events
Application.EnableEvents = True
End Sub
In this case, modifying A1 will not trigger the event since events have been disabled before performing the action.
This solution is straightforward to implement but can sometimes lead to problematic situations if there is a bug after disabling events (as they are not automatically re-enabled).
Disable an Event using a Variable
This alternative solution uses a variable to "disable" the event by exiting if it is already in progress:
Dim stopEvent
Private Sub Worksheet_Change(ByVal Target As Range)
'If the event is already in progress: exit!
If stopEvent = 1 Then Exit Sub
'Disable the Worksheet_Change event
stopEvent = 1
'Example action
[A1] = [A1] + 1
'Re-enable the Worksheet_Change event
stopEvent = 0
End Sub
In this case, when the event is triggered, the stopEvent variable is not equal to 1, so the procedure is not exited prematurely (at the first line with Exit Sub
).
When cell A1 is modified, the event will be triggered again, but this time, stopEvent will be equal to 1 (thus, the procedure will stop at the first line).
Note that you can also use a Static variable if the variable is only needed for the event:
Private Sub Worksheet_Change(ByVal Target As Range)
Static stopEvent
'If the event is already in progress: exit!
If stopEvent = 1 Then Exit Sub
'Disable the Worksheet_Change event
stopEvent = 1
'Example action
[A1] = [A1] + 1
'Re-enable the Worksheet_Change event
stopEvent = 0
End Sub