Page 292 - Microsoft Office Excel 2003 Programming Inside Out

Microsoft Office Excel 2003 Programming Inside Out

Not all events are defined by all objects. In general, if an object has an event, its parent object
will also have the same event. For example, the Change event is contained at the “lowest” level
by the Worksheet object. The Worksheet object’s parent is the Workbook object; it also has the
same event called Workbook_SheetSelectionChange. The Workbook object’s parent, the
Application, also has a Change event. The “lower” event does not need to be programmed in
order to receive the event at a higher level. For example, the Workbook_SheetChange event is
triggered regardless of whether you have a Worksheet_Change event coded.
When evaluating the results you want to achieve with your event procedure, keep in mind at
which level it should occur. A common mistake is programming the event procedure in the
wrong location. The Visual Basic Editor window displays all open projects, arranging all
components in a collapsible list, as shown in Figure 12-1. Ensure you have the correct object
active before programming your event procedure.

Figure 12-1. The components for each VBA Project are displayed in an expandable list.

Enabling and Disabling Events
The Excel Application object has an EnableEvents property that you can use to enable or disable
event triggers. Because VBA code, including event procedures, can cause events to be trig­
gered, you might find situations where you will be required to disable events. For example,
changing a cell’s value from VBA will trigger the Change events in all object levels. If you have
code in the Worksheet_Change event procedure that modifies another cell, you must disable
events to prevent Worksheet_Change from calling itself repeatedly. If the procedure continu­
ally called itself, Excel would either overflow its call stack (an internal record of the pending
jobs within Excel) or run out of memory.
By default all events are enabled. To disable all events, add the following line of code to your
VBA procedure:

Application.EnableEvents = False
To enable the events, use this line of code:

Application.EnableEvents = True
Chapter 12

266
Part 4: Advanced VBA