You are on the Home/Publications & Training/Case Studies/Sequence of Event Procedures page
Google
Web This Site

When does Excel trigger an event procedure and what is the sequence of those procedures?

Excel runs an event procedure whenever the associated event occurs.  So, for example, the SheetCalculate procedure will run after Excel calculates a worksheet.

The sequence in which event routines are executed has nothing to do with *which* routines are executed. Excel will execute the SheetCalculate routine for a worksheet, say, WS1, only if it recalculates something in that worksheet and it will fire the SheetCalculate routine for another worksheet, say WS2, only if it recalculates something in WS2.

Sometimes it might appear that Excel runs the SheetCalculate routine for WS1 even when the only change was in WS2.  The most likely causes for such an occurrence are: (1) there is a dependency in WS1 on something in WS2 that Excel recalculated, and (2) there is a volatile function in WS1.  A volatile function can be an Excel-native function such as the NOW function or it might be a user-defined function that is marked as volatile.  Of course, there is a third possibility and that is the presence of a bug in Excel.  However, I have yet to see an instance where Excel/VBA improperly runs an event procedure.

When multiple event procedures are executed, there is no guarantee as to the sequence in which they will be executed.

Here's a simple test.  Create 2 new workbooks, WB1 and WB2. In the code module of each workbook enter

Option Explicit

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
    MsgBox "In " & ThisWorkbook.Name
    End Sub

Now, in a worksheet in WB1 enter a number. *Neither* SheetCalculate routine will be run! After all, Excel has done no calculation.

Now, in some sheet in WB1 enter =1+{cell in which you entered last number}. The WB1 SheetCalculate routine will run.

Now, in a worksheet in WB2 enter =1+4. The SheetCalculate routine for WB2 will run but not the one in WB1.

Now, in the WB2 worksheet enter =NOW() The SheetCalculate routine for WB2 will run but not the one in WB1. Note that NOW is a volatile function.

Now, go to WB1 and enter a number in some cell. The WB2 SheetCalculate routine will run but not the WB1 routine!

Through trial-and-error it becomes apparent that when Excel has to run event procedures in multiple workbooks, it does so in alphabetic order.  So, the WB1 event procedure run before the corresponding WB2 procedure.