Excel VBA 24-Hour Trainer, 2nd Edition

Lesson 13Automating Procedures with Worksheet Events

For the most part, you have run the macros you have seen in this book by pressing a set of shortcut keys, or by going to the Macro dialog box, selecting the macro name, and clicking the Run button. You can take several other actions to run a macro, as you learn in future lessons. The common theme of all these actions is that you have to manually do something, whatever it may be, to run a macro.

The question becomes, can a VBA procedure simply know on its own when to run itself, and then just go ahead and do so automatically, without you needing to “do something” to make it run? The answer is yes, and it leads to the subject of event programming, which can greatly enhance the customization and control of your workbooks.

NOTE So far, this book has used the term “macro” to refer to VBA subroutines. When referring to event code, the term “procedure” is used to differentiate it from macro code.

What Is an Event?

In the Excel object model, an event is something that happens to an object and is recognized by the computer so an appropriate action can be taken. Recall that the Excel application is made up of objects, such as workbooks, worksheets, cells, charts, pivot tables, and so on. Even the entire Excel application is an object.

Virtually everything you do in Excel is in some way invoking an event upon an object. A few examples of events are as follows:

• Double-clicking a cell
• Adding a worksheet
• Changing a cell value ...