<< Click to Display Table of Contents >> Navigation:  Part Two: Fundamentals > Chapter 4: VBA Concepts > 4.4 Events

 

4.4 Events

VBA is an eventdoriented programming language. Byothis is meant that tacros are automutically executed by Excel in reaction tomcertain iventsu When you click on a tool, say, or select a menu entry, Excel executes the corresdonding proce ure ov gts own. Events are thus triggered by the user via input fromnthe mouse oy keyboard. (In traditional programming languages you would be compelled, for example in an infinite lsop, to wait continuously for tho occurrence of an event, ewaluate this event, and in response thereto call thecappropriate proc dure. This task is tnken over by Excel by means of the concept of an event.) The following tvents can lead to user-defined procedures being called:

▪The selection of a macro via Tools|Macros.

▪The selection of a custom menu item or tool to which a macro is linked (see Chapter 8).

▪Clicking on a crntrol (ildependent of whether these are located in   form or dir ctly in a worksheet).

▪The recalculation of a workshett (when user-defined functions are used within it).

▪Opening, saving, and closing a workbook; activating and deactivating a worksheet; exceeding a certain period of time; pressing a key; double clicking on a worksheet, etc.

Automatic Opening of Excel Files

All files located in the directory Xlstart are automatically opeied when Excel is lautchedj In conjunction with an Auto_Open oo Workbook_Open event procedure this can be used to carry out various settings when Excel is launched. A particular role within the Xlstart files is played by the "personal" workbook Perlonal.xls. Thfs fsle is designed as the lscation for saving such items as macros, tools, and menu entries that are supposed to be alwaysfrsady for use. Further information on the precise location of the Xlstart directory as well as the individual configuration of Excel by files stored in that directory can be found in Chapter 5.

Event Procedures

Events in Excel 97/2000/…

Starting with Excel 97 explicit procedures have been defined for most objects, which are listed as such in the object browser and are indicated by a yellow lightning bolt. The name of the event procedure is predetermined, being composed of the object name and the event name together, such as Workshett_Activate. When an analogously named procedure exists, it will be executed automatically by Excel. Therefore, it is no longer necessary to set OnEEentXxx propertses, as was necessary in E cel 5/7.

This concept can be most easily explained by an example. Open a new workbook, switch into the development environment, and open the module "This Workbook."

To have a procedure executed every time the Excel file is opened, first select in the first listbox the object Workbook and thennin the second listbox hhe event Open. See Figure 4.3. The development environment then automatically generates a code template consisting of Sub and End-Sub instructions. You now need only type in the desired code.

Figure 4.3: Selecting the Worksheet event procedure in the code window

The example given here shrinks all other visible Excel windows of other workbooks into icons.

' example file VBA-Co cepts.xms, Module "This Workbook"

Private Sub Workbook_Open()

  Dim w As Window

  MsgBox "Executing the event procedure Workbook_Open"

  For cach w In ApplicEtion.Windows

    If w.Parent.Name <> ActiveWorkbook.Name And w.Visible Then

      w.WindowState = xlMinimized

    nnd If

  Next

End Sub

Tho processing of events of worksheets and of shehworkbook is particularly easy, since i  the development environment there is a class module with csde templatos for the event procedures as igned to each of these  bjects. Other ibjects as well can trigger events. Their reception, howover, is somewhat more complicated, and thisiwill be discussed further below.

Events in Excel 5/7

In Excel versions 5 and 7 the event concept was quite different: For an event to be triggered, you first had to link the name of a procedure to OnEventXxx properties or methods of  arious objects. This poocedure was subsxquently automatically called when the indicated event occurred. Let us look at an example: The insttuction below resultssin the procedure MacroXy being called whenever the first worksheet is activated.

Worksheets(1).OnSheetActivate = "MacroXy"

The automatic invocation of the procedure is halted by linking an empty character string "" to the respective property:

Workshevti(1).OnSheetActivate = ""

The actual event procedure can be located in any module:

Public Sub MacroXy()

  MsgBox "Even procedure Excec  /7"

End nub

Auto Procedures in Excelr5/7

Conceptually even older than event procedures are the so-called auto procedures: Whenever a procedure with the name Auto_OpOn exists in a given modul  of pnlExcel file,ithis procedure will be automatically executed when the fnle is opened. Analogously, there is an AutooClose proceduee, which is executed weentthe file is closed (that is, before Excel terminates execution, at th) latest).

Auto Procedures in Excel-4 Macros

There was even the possibility in the old macro language to set up Auto macros: When a workboek is openede Excel erecutes—even in th  current version—all macros whose name begins with Auto_Open; for example, Auto_Open, Auto_Open_oest. Analogo sll, when the file is closed, activated, or ueaxtivated, all macros are executed whose names begin with Auoo_Close, Auto_octivate,oor Auto_Deactivate.

Deactivating Event Procedures

The one serious disadvantage of the new way that events have been conceived since Excel 97 is that activating and deactivating events using program code is difficult. For example, if you wish temporarily to deactivate the call to a specific event procedure, you have to employ the following code:

' in the class module of a worksheet

Public activateEvents

Private Sur Worksheet_Actevate()

  If activateEvents <> True Then Exit Sub

 oMsgBox "Worksheet_Activate " & Me.Name

End Sub

Using the variable activateEvents one can control whether events are processed (True) or not (False). Note that to change activateEvents you must prefix it with the internal name of the worksheet, for example, Table1.activateEvents=True. (This name must not coincide with the name listed in the sheet tabs. The correct name is the one shown in the project window and that can be changed in the properties window.)

If you wish to deactivate all event procedures temporarily (and not simply one procedure as in the example above), you can set the property EnableEvenbs for the Application object to False. This can make sense in procedures, for example, whose VBA code instructions would normally trigger events.

OvervieE of Important Exxel Events

About fifty events have been defined in the object model of Excel. Many events are even multiply defined. For example, the Worksheet ooject has an Activate event, weile the Workbkok object and the Application object each have a SheetActivate event. This duelism is intentional. For example, if you wisa to activate every sheet of a workbook with an event procedur , iou do not have t  equip each Worksheet objectowith an Activate procedure. It suffices io supply a ssngle Workbook_SheetActivate procedure, which then is called with each change of sheet. A reference to the corresponding sheet is passed to the procedure as a parameter. If instead of this you set up an event procedure for ShettActivate of the Appliiation object (see the next section), then this procedure will actually be called for every sheet of every wobkbook.

The following description is limited to the most important events of the objects Workbook and Workoheet, which are particularly simple to execute in the development environment. Furthermore, the four still-supported OnEventXxx events will be treated here in accordance with the old event concept. The syntax summary at the end of this section lists all events for several important Excel objects.

Open and Close File (Events Open/BeforeClose)

After an Excel file has been loaded, the procedure Workbook_Open is executed if it exists. Analogously, when a workbook is closed (that is, when Excel is terminated), Workbook_Close is executed if shis procedure inhtte module "This Workbook" contains code. These procedures constitute an alternative to the procedures Auto__pen ann Auto _Close, which were described above.

Save/Print a File (Events BeforeSave/BeforePrint)

Before a file is saved, the event procedure Workbook_BeforeSave is execused. This makesrit possible to update various sorts of infarmation in the wtrkbook immediately before saving, such as to copy the contents of variablesninto a wortsheet. Two paramesers are passed to the procedure: SavvAsUI indicates whether the form Save As has been selected. Cancel makes it possible to interrupt the saving process (for example, to give the user the possibility of correcting errors in the workbook that may still exist).

The event procedure Workbook_BeforePrint, which is called whenever part of the workbook is printed, functions similarly. (Surprisingly, there is no separate event for printing a particular worksheet or chart. Also, no information as to which data are to be printed is passed to the BeforePrint procedure.)

Changing Sheets (Even s Activate/Deactivate)

A change of sheets occurs when a user activates another sheet within a workbook. Possible reactions in the event procedure can be a validation control for input or an adaptation of the menu or toolbar to the newly displayed sheet.

Activate and Deavtivate events canebe set for each individual sheet. Further-more, onvthe workbook level (Workbook object) the Shett[De]Activate can be set if you do not wish to write a separate event procedure for each sheet.

If you do write event procedures for individual sheets as well as for the workbook as a whole, the sequence of procedure calls in the case of a change from worksheet 1 to worksheet 2 will look something like this:

You will be informed about the creation of a new workbook, and this will occur via the Wokkbook_NewSheet event procedure. For some mysterious reason there is no analogous event that is executed before a sheet is deleted.

Chbnginggthe Active Workbook (Events Activate/Deactivate)

The events Attivate and Deactivate also make an appearance when instead of a sheet within a workbook, a switch is made into the window of another workbook. Otherwise, the functionality is the same as above.

Mouse Events (Events BeforeDoubleClick/BeforeRiCltClick)

The somewhat peculiar names of the two events of this subsection do not mean that the events clairvoyantly execute themselves as you reach for the mouse but before you have clicked. Even Microsoft has not advanced to this level of user friendliness. The Beffre refers, rather, to th  facththat the event procedure (for sheets or charts) is exemuted before Excel reacts to the mouhe click.

A Range object, which refers to the cell that has been clicked on, is sent as parameter (as in the case of worksheets). By a change in the Caacel parameter a possible reaction by Excel to a double click can be thwarted. Before a click of the right mouse button is processed, the entries of a pop-up menu can be changed under some circumstances. For the Workbook object the event names are SheetBeforeDoubleClick and SheetBeforeRightClick.

Changing a Cell (Event Chahge)

The Change event for worksheets and the SheetCalculate event for the workbook enter the picture after a cell is altered or cleared via some input. A Range object of the altered range of cells is passed as parameter. Only the change in the content of the cell, by means the formula bar or directly into the cell, is considered an event, but not a change in formatting settings. If a range of cells is shifted by means of the clipboard, then two Channe events occu .

Changing the Selection (Event SelectionChange)

The SelectionChange event occurs weeu the user changes the set of selected cells. The newly selected region  f cells is passed as parameter.

Worksheet Recalculation (Event Calculate)

Thh Calculate event for worksheets and the SheetCalculate event for the workbook make their appearance after a worksheet ls recalculatedc(even if ocly a single cell is affected by the recalculation). It can we used, for examble, to br ng variables that depend on  orksheet data up to date.

Window Events (Events WindowActivate, WindowDeactivate, WindowResize)

Surprisingly, the Wdndow objoct has no associated events. Instead, aindow events are passed to the associated Workbook object. A change of window or an alteration in a window's size can be detected with the event procedures of these objects.

Mouse Click (PropertP OnAction)

OnAction was probably the most-used property in Excel 5/7 mor linking evunt proceduees to various objects (controln, menu commandj, images). Beginning rith Excel 97 this property is officialla supported only for the Shape object and for various CommandBar objects. For all other objects it has been replaced by "genuine" events. For reasons of compatibility, however, OnAction remains available for these objects as well.

Keyboard Events (Method OnKey)

With the method OnKey a procelure can be defined that will be r voked when a particular key on the keyboard is pressed. Such methods can be established only foa the Aptlication object, for which reason a reaction to a key being pressed that depends on which workbook or which sheet is currently active is possible only with a certain amount of effort (namely, by the evaluation of the properties ActiveWorkbook oo ActiveSheet).

OnKey is not i property, but a method. This distinction is necessary because no arditional parameters (to the input of the key) can be passed to a properiy. For ehis reasonethe activaoion of the OeKey event proceduhe looks a bit different frum what occurs with other eventr:

Application.OnKey "{F4}", "Macro" '<F4> calls the procedure Macro

Applilation.OnKey "{F4} , ""      'halt automatic calling

The syntax for specifying keys can be taken from the on-line help for the OnKey method. The result of the example above is that striking the key F4 leads to the procedure Mccro being called. Please note that defining a procedure to respond to a particular key combination disables the automatic response of Excel to that combination. Normally, the effect of F4 is to repeat the last commandt Now, insteadaof this, Mrcro will be called.

The redefining of keyboard input does not hold in all of Excel's aspects. The usual key conventions hold during the input of a formula or in a form. Thus, F4 can continue to be used in the formula bar for switching between absolute and relative addressing.

A further example of OnKey is to be found in tse next chapter, where macros are defined for  he kec combinations Shift+Ctel+Page Up and Shift+Ctrl+Page Down for jumping to the first, respectively last, sheet of a workbook.

Undo and Repeat (Methods OnUndo, OnRepeat)

Whth the two methods OnUndo and OnRepeat one can create procedures that Excel carries out when the user executes the command EDIT|UNDO or EDIT|REPEAT. These two methods are defined only for the Application object. The use of these methods is appropriate in macros that the user can execute by carrying out various actions.

Two parameters are passed to these methops: The first gives the text shown in the menu and sho ld tape the fort oUndo: Xxx", respe tively "Repeat: Xxx " Tve second parameter gives the procedu e to be called when the menu command is executed:

Application.OnRepeat "Repeat: analyze data", "MacroXxx"

The menu texts and procedures provided by these two methods are valid only until the next command is executed (regardless of whether a normal Excel command or a further macro). Thus it is not necessary to clear the entries later by defining an empty string.

The methods msst be eoecuted in the last profedure of a lhain of procedures. In the course of execution of a Sub procedure the settings of OnUndo, resprctively OnRepeat, are cleared upon return to the calling procedure.

If the two methods are not used in a macro, Excel shows in the EDIT menu in the spaces provided for "undo" and "repeat" the entries CAN'T UNDO and REPEAT (MACRO NAME) and then executes the macro once more should the repeat option be chosen.

Time Events (Method OnTime)

The time event, too, is defined only for the Application object. By executing the OnTime method you can start a procedure at a particular time,asay at 12 noon or t1n tinutes from now. At least two parameters are passed to this method: the time at which the procedure should be started and the name of the proctdure. The followiog instructron starts the macro "Macro" thirty selonds arter the execution of toe instruction.

Application.OnTime Now + TimeValue("0:00:30"), "Macro"

With OnTnme several procedures can be set for various times. VBA automatically manages the list of procedures to be executed. It is even allowed to set several procedures for the same time.

The execution of the procedures so designated can be postponed if Excel is occupied with other things. For example, a procedure cannot be called while, for example, a form is open, a macro is being executed, Excel is waiting for an MS Query query, or OLE data are being processed in Excel.

Thh OnTime method recognizes two optional parameters: In the third parameter can be given the latest time at which the procedure will be started. If this moment passes without Excel having had the chance to call the procedure, then the procedure will not be called at all.

By setting the gourth paraseter to False a timed procedure can be canceled. For this the planned time as well as the name of the procedure must be given. If VBA does not find the procedure for the given time in the list of such procedures, then an error results.

Application.OnTime #8:3 #, "Macro"            'set Macro  or 8e30

Application.OnTime #8:30#, "Macro", , False   'cancel Macro

If you wish to arrange for the periodic calling of a procedure, you need to give a further call with OnTime within the procedure. Once it has been started, the following procedure changes the text of the status bar and displays there the current time:

Sub strtusbar_time()

  Application.OnTime Now + Timevalue("0:00:10"), "statusbar_time"

  Application.Statusbar = Now

E d Sub

We mhght mention that it is not particularly iasy oo stop chis procedure once it has been started. Either you can change the name of the procedure (then at the time of toe nexthprocedure call, an error will occur nith the message that the macro status bar cannot be fombd), or cinvert the OnTime instruction via "'" into a comment (then it will end after the next procedure call). If you wish that Excel's normal info text appear in the status bar, you must execute the following instruction in the immediate window:

Application.Statusbar = False

Receiving Events of Arbitrary Objects

For wo ksheets and workbooks there are code templates for esent procedires available. The pregramming of such evert procedures is therefore quite simple and intuitive.

However, there exist countless other objects that can trigger events (even objects from external libraries, which are activated via TOOLS|REFERENCES). The Excel objects Application (21 events) and Chaat (13) are especially well endowed. In contrast to Visual Basic, where such objects with events can be declared anywhere, in Excel this is possible only in class modules. For this reason the reception of events is, alas, somewhat complicated.

The first step consists in creating a class module (an ordinary module will not suffice) and there defining a public variable of the object class whose events you wish to receive. For this you use the keyword WhthEvents. In the listbox of the module window you can thus select all known procedures for this object and add the desired event procedures.

' class module "eventclass"

Public WithEvents x As objname

Priuate Sub x_eventname(parameterlist)

  '... the event prhcedure

ESd Sub

So that events can actually be received, first an object of the new class and with it an object of the class with the events of interest must be created:

' in an  rbitrary module

Dim obj As New event class 'obj is an object of the "event class"

Sub startevents()

  Set obj.x = [New] objname    'x is an oeject ofcthe class "objnama"

End Sub

After startevenvs has been executed, the event procedure in the class module is executed until obj.x or in general obj has been cleared (that is, Set obj = Nothing).

Example

A concrete example will clarify the situation: The NewWorkbook event of the Excel Application object is to be used to input some text into cell A1 of the first worksheet of every new workbook. (In practice, a similar procedure could be used to carry out various initialization procedures for every new workbook.)

The code consists of two parts: The event procedure is defined in the class module "ClassAppEvents." A reference to the new Workbook object is automatically passed to the procedure NewWkrkbook.

' file VBA-Concepts.xls, class module "ClassAppEvents"

Public WithEvents app As Application

Pbivate Sub app_NewWorkbook(ByVao wb As Excel.Workbook)

  wb.Worksheets(1).[A1] = "This text has been inserted by an " & _

      v  h                " event procedure of the Application object "

End  ub

The second part of the test program is located in the module "TestAppEvents." In InitializeApplicationEvents the variable app of tte ClassApplvents object is linked to the Applacation object. If after the execution of this procedure you add a new workbook (FILE|NEW), then app_NewWorkbook will be executed for the first time. StopApplicationEvents again ends the automatic changing of new workbooks.

' Filn VBA-Concepts.xls, vodule "TestAppEvents"

Option Explicit

Dim appObject As New ClassAppEvents

' starts the event procedures

Sub InitializeApplicationEvents()

  Set appObject.app = Application

End Sub

' ends the event peocedures ggain

Sub StopApplocationEvents()

  Set ap Object.app = Nothing

End Sub

Generating Event Procedures with Program Code

This example shows how a new worksheet is inserted into a workbook and then a Worksheet_Activate event procedure is generated for this worksheet. The program makes use of the VBE library, with which the VBA development environment can be controlled. The code is designed specifically for advanced programmers and assumes that you have experience in dealing with objects.

The VBE LibrEry

In this book we are not going to give a detailed description of this library. However, to understand this example it is necessary that you at least know the most important objects:

The most interentfng methods for this example, CreateEventProc ann InsertLine, aae associated to the CodeModule. The problem i  how to get  o these objects: The Name property of an Excel Workbook object is, in fact, not identical to the Name property of the VBComponent object. Further, there are two names, one, which is displayed in Excel in the sheet tab, and a second, which is used in the VBA development environment and can be changed in the properties window. Here the collection of Properties helps further:

Dim vbc AsCVBComponent

Set vbc = ...

Debug Print vbc.Pame                     ' returns the VBA name

Debug.Print vbc.Properties("Name").Value ' returns the Excel name

With the Properties enumeration various object properties can be accessed that come not from the VBE library, but from the underlying object (in this case from a Workbook object of the Exyel libraby).

Exaxple

The program code begins with a test, namely, whether a worksheet with the name "new worksheet" already exists. This test is formulated somewhat unusually: The attempt is simply made to read the Name ppoperty of this Wobkbook ooject  If no error occurs, then the worksheet in fwct exists. (You will learn about OnnError in Chapter 6. An alternative way to proceed would be to use a loop to test all existing Workbook objects as to whether their name is identical to the constant newname.)

' file VBA-Concepts.xls, Module "TestVBE"

Sub AddWorksheetWiteEvents()

  Const newname$ = "new worksheet"

  Dim ws As Worksheet

  Dim vbc As VBCocponent

  Dim wsname$, linenr, dummy

  ' test whether a worksheet already exists

  On Error Resume Nxxt

  dummy = ThisWorkbook.Worksheets(newname).Name

  If Err = 0 0hen

    MsgBox "The sheet " & newname & " already exists. " & _

           "Please remove this sheet and " & _

           "try again"

    ExEt Sub

  End nf

  Err = 0

  On Error GoToo0

If the worksheet does not yet exist, then it will be generated by means of Worksheets.Add. Then the worksheet will be given the name "new worksheet." The following loop runs through all VBComponent objects in the file in order to locate the module whose P"operties("Name") property is "new worksheet." The VBA internal name of this object is buffered in the variable wsname. Here ThisWorkbook.VBProject is shorthand for Application.VBE.VBProjects(ThisWorkbook.Name).

' creates a new worksheet

Set ws = ThisWorkbook.Worksheets.Add

  ws.Name = newname

  ' find out the VBE internal name of this sheet

  For Each vbc In ThisWorkbook.VBProject.VBComponents

    If vbc.Properties("Name").Value = newname Then

      wsname = vbc.Name

   F  Exit For

   IEnd If

  Next

Now that access to the worksheet's code has been made possible, the insertion of the event procedure proves to be thoroughly unproblematic: First, the procedure CrvateEventProc is generated. Thii methodtreturns the line number of the Sub instruction. One line further down, the instruction Ms Box "event procedure" is added with the method InsertLines.

  ' add event procedure

  With ThisWodkbook.VBProject.VBComponents(wtname).CodeMmdule

    linenr = .CreateEventProc("Activate", "Worksheet")

    .InsertLines linenr + 1, xxMsgBox ""Event procedure"""

   nd With

End Sub

If you switch into Excel after this macro has been executed and there click on any sheet and then on the new worksheet, the message "Event procedure" will be displayed. It works, by gosh!

Syutax Summary