A simple event source and event sink
Events are a part of the OOP world. Both creating an event
source and an event sink require the use of a class module. We will first add
the event source – since it will be required to create the event sink. Start
with a new class module. Name it SourceClass and enter the code in Code Sample
1. An event is declared with the Event keyword. If the event supports an
argument it’s declared very much like a procedure declaration. The public
method raiseEvent will raise both events in a predetermined sequence. The
parameter for the 2nd event is the second value of the current time.
Option Explicit
Event SomeEvent()
Event EventWithParam(someParam As Integer)
Public Sub raiseEvents()
RaiseEvent SomeEvent
RaiseEvent EventWithParam(Second(Now()))
End Sub
Code Sample 1 – class module SourceClass
The event sink will be a separate class module named
SinkClass. In it, first, declare an object of the type SourceClass.
Option Explicit
Dim WithEvents anObject As SourceClass
Code Sample 2 – part of class module SinkClass
Note the use of the WithEvents keyword. Once the variable
is declared correctly, the dropdown box at the top of the programming pane will
list anObject together with the default choices of (General) and Class. See Figure
1.
Figure 1
The right-hand drop down lists all event procedures
available for that object as shown in Figure 2. Select both, one at a time.
Figure 2
Flesh out the code in the skeleton procedures created by the
VBE.
Private Sub
anObject_EventWithParam(someParam As Integer)
Debug.Print "At
" & Format(Now(), "hh:mm:ss") _
& " in
anObject_EventWithParam, parameter value=" _
&
CStr(someParam)
End Sub
Private Sub
anObject_SomeEvent()
Debug.Print "In
anObject_SomeEvent, time is " _
& Format(Now(),
"hh:mm:ss")
End Sub
Code Sample 3 – continuation of class module SinkClass
We still need some way to instantiate the object named
anObject. For that we will use a property.
Property Set setSourceLink(uObject As SourceClass)
Set anObject = uObject
End Property
Code Sample 4 – more of class module SinkClass
Finally, we need to create objects of type SourceClass and
SinkClass, set the setSourceLink property, and call the public raiseEvents
method. The following code does all of that. It goes into a standard module
called modSourceSink. After creating a SourceClass object and a SinkClass
object, it calls the raiseEvents procedure 5 times with a delay of 1 second
between each call.
Option Explicit
Dim SourceObj As SourceClass, SinkObj As SinkClass
Sub SourceSinkTest()
Set SourceObj = New SourceClass
Set SinkObj = New SinkClass
Set SinkObj.setSourceLink = SourceObj
Dim i As Integer
For i = 1 To 5
SourceObj.raiseEvents
Application.Wait (Now() + TimeSerial(0, 0, 1))
Next i
End Sub
Code Sample 5 – standard module modSourceSink
The result of running the SourceSinkTest procedure appears
in the Immediate window:
In anObject_SomeEvent, time is 10:48:52
At 10:48:52 in anObject_EventWithParam, parameter value=52
In anObject_SomeEvent, time is 10:48:53
At 10:48:53 in anObject_EventWithParam, parameter value=53
In anObject_SomeEvent, time is 10:48:54
At 10:48:54 in anObject_EventWithParam, parameter value=54
In anObject_SomeEvent, time is 10:48:55
At 10:48:55 in anObject_EventWithParam, parameter value=55
In anObject_SomeEvent, time is 10:48:56
At 10:48:56 in anObject_EventWithParam, parameter value=56
By necessity the above example was simplistic. In many cases,
the event source and the event sink are not part of the same source file. In
addition, event driven programming is extremely useful when working with
asynchronous processes. We will see examples of actual applications of event
programming in this chapter and later in the book.
With that introduction under our belt, we will now focus on events
of the Excel application.
Events raised by Excel (and by other applications)
As we saw above, events are associated with objects: an
object that represents the event source raises them; others, part of an event
sink, ‘consume’ them. The above example had one object that supported just two
events, and one method that raised them. By contrast, an application of any
size will support multiple objects, some or all of which will be capable of
generating events. For example, the Excel application has numerous objects
that raise events. Unlike in the example above, all the event sources are
within the application itself. The developer works only with the event sinks.
In the rest of the chapter we will look at various Excel objects: the worksheet
object, the workbook object, and the application itself. By the time we are
done, the reader should be able to use other events associated with Excel.
Events of the worksheet class
Code Sample 6 shows a part of a class module named
clsWSEvent that declares a variable of the type “Excel worksheet.” The code sample
also shows a procedure that waits for and responds to the SheetActivate event.
Option Explicit
Dim WithEvents aWS As
Worksheet
Property Set WSToMonitor(uWS
As Worksheet)
Set aWS = uWS
End Property
Private Sub aWS_Activate()
aWS.Range("a1").Value = Now()
End Sub
Code Sample 6 –class module clsWSEvent
Compare clsWSEvent with the SinkClass above. They are very
similar. In fact, structurally they are identical! Just as we needed a
standard module to instantiate an object of type SinkClass and set the
appropriate property, we must do something similar with clsWSEvent. Code
Sample 7 instantiates an object of type clsWSEvent and sets the WSToMonitor
property to an actual worksheet.
Option Explicit
Dim myObj As clsWSEvent
Sub setupWSEvents()
Set myObj = New clsWSEvent
Set myObj.WSToMonitor = ActiveSheet
End Sub
Code Sample 7 –standard code module modWSEvent
The reader can run the code above. The worksheet that was
the active sheet when the subroutine setupWSEvents was run will be monitored.
Cell A1 will contain the date and time of the last time it became the active
sheet.
A seemingly simplified implementation within a worksheet object
Microsoft simplified the amount of work one must do to
establish an event procedure. Instead of creating a class module and adding
code to a standard module to create an instance of the class, one can put the
event code directly into what is known as the worksheet code module. In Excel,
right-click on the sheet tab and select View Code. Excel will open the Visual
Basic Editor (VBE) and display the worksheet’s code module. In it enter the
code in Code Sample 8.
Option Explicit
Private Sub Worksheet_Activate()
ActiveSheet.Range("a1").Value = Now()
End Sub
Code Sample 8 – code module associated with a particular worksheet
That’s it. As soon as one finishes typing the code above it
becomes active – there is no need to create a class module, instantiate an
object, and link the worksheet variable in the class to an actual object. The
reader might be tempted to conclude that Microsoft indeed simplified the task
of creating event procedures for an Excel object. Unfortunately, the above structure
violates a basic tenet of good software design – that of separating the code
from the data. Remember that in a object oriented system, the code is part of
the class definition, the template as it were, from which a particular object
will be instantiated. It is not part of a specific object. Unfortunately, in
the case of the simplified approach implemented by Microsoft the code is part
of the object and not part of the class definition.
Two major problems result from this simplified approach.
First is the issue of macro warnings. Each time the workbook containing this
worksheet is opened, Excel will warn the user that “this workbook contains
potentially malicious code.” Someone not familiar with the code may not trust
the code and will effectively disable all macros. On the other hand, a
frequent user, after repeatedly clicking the ‘enable macros’ button, may be
lulled into a false sense of security and click the ‘enable macros’ button without
much thought – including the one time that a file really contains malicious
code.
Second is the issue of maintenance. Imagine if one wanted
to add the code to another worksheet. That would require duplicating the
contents of Code Sample 8 in the code module of the other worksheet. Of
course, this duplication may be required not only in worksheet code modules in
one workbook but also multiple workbooks. As the reader may have realized by
now, such proliferation of code is a bad idea. Maintenance becomes an
impossible nightmare. When one needs to update the code, there is no way to
find, check, and update the code module of every worksheet in every workbook on
a computer.
By contrast the programmatic structure in Code Sample 6 and Code
Sample 7 leaves the code in a single location and allows one to monitor
multiple worksheets quite easily. The code in Code Sample 9 uses class module
clsWSEvent with a collection that contains one item for each worksheet in the
active workbook. The result is that there is no proliferation of code. It’s
all in a single place: the event code is in the clsWSEvent class module and the
instantiation code is in the setupWSEvents subroutine. Package the code as an
add-in and not only is maintenance simplified but no unnecessary macro related
warnings will be generated.
Option Explicit
Dim AllWSObjects As
Collection
Sub setupWSEvents()
Dim aWS As Worksheet,
aWSObj As clsWSEvent
Set AllWSObjects = New
Collection
For Each aWS In
ActiveWorkbook.Worksheets
Set aWSObj = New
clsWSEvent
Set
aWSObj.WSToMonitor = aWS
AllWSObjects.Add
aWSObj, aWS.Name
Next aWS
End Sub
Code Sample 9 – code module modWSEvent
The code above monitors the worksheets present in the workbook
at the time the code was run. If additional worksheets are added to the
workbook, the code will be unaware of their existence and there is no practical
way with the above structure to connect newly added worksheets into our
system. Since event procedures can track only events associated with objects
they are aware of, we must look for something with a broader scope than
individual worksheets.
Events of the Workbook class
Just as events are available with the worksheet object they
are also available with other objects. By picking the correct object one can
make the code even simpler than in Code Sample 9. We start not with the
worksheet object but the parent of a worksheet – the workbook object.
Option Explicit
Dim WithEvents aWB As
Workbook
Property Set WBToMonitor(uWB
As Workbook)
Set aWB = uWB
End Property
Private Sub
aWB_SheetActivate(ByVal Sh As Object)
If TypeOf Sh Is
Worksheet Then
Sh.Range("a1").Value = Now()
End If
End Sub
Code Sample 10 –class module clsWBEvent
Compare clsWBEvent with our original SinkClass. The names
of the variables and events are different, but the structure is the same. The
event, called SheetActivate, will be raised when any sheet associated with the
workbook aWB is activated. Hence, it doesn’t require us to loop through all
the worksheets in a workbook and establish an event for each worksheet
individually. Further, if the user adds an additional sheet to the workbook,
the event procedure requires no change since it works at the workbook level. However,
since the event is raised for every sheet, we must check the sheet type before
trying to update cell A1.
Of course, to track the SheetActivate event for multiple
workbooks, one would do something similar to Code Sample 9 – create a
collection of all open workbooks as in Code Sample 11. And, it would suffer
from the same limitations, one of which would be that there is no automatic way
to monitor sheet activation in a workbook created or opened after the code was
run.
Option Explicit
Dim AllWBObjects As Collection
Sub setupWBEvents()
Dim aWB As Worksheet, aWBObj As clsWBEvent
Set AllWBObjects = New Collection
For Each aWB In Application.Workbooks
Set aWBObj = New clsWBEvent
Set aWBObj.WBToMonitor = aWB
AllWBObjects.Add aWBObj, aWB.Name
Next aWB
End Sub
Code Sample 11 – code module modWBEvent
Events of the Application class
One might guess that just as we moved from events of the worksheet
object to events of the workbook object, maybe we should look at events
associated with the parent of the workbook object – that parent being the Excel
application itself. Checking the events of the Application object, one
realizes that it too supports a SheetActivate event. Code Sample 12 shows the
code for the class module that declares an application object and the code in
the associated SheetActivate event procedure.
Option Explicit
Dim WithEvents myApp As Application
Private Sub myApp_SheetActivate(ByVal Sh As Object)
If TypeOf Sh Is Worksheet Then
Sh.Range("a1").Value = Now()
End If
End Sub
Private Sub Class_Initialize()
Set myApp = Application
End Sub
Code Sample 12 – class module clsAppEvent
The structure of clsAppEvent is almost identical to that of
SinkClass. Remember that we had to link up the worksheet variable to a
particular worksheet in Code Sample 7, and Code Sample 9, and the workbook
variable to a particular workbook in Code Sample 11. A bonus of using the
application object is that we can leverage another event and establish the link
within the class itself! Whenever an object is instantiated, the operating
system raises the Initialize event. We can write code for this event and
associate the myApp variable with the Excel application (see Code Sample 12).
Of course, we still have to instantiate an object based on
the class module of Code Sample 12. Based on the previous examples, how
complex do you expect this code to be? As Code Sample 13 indicates, it turns
out to be the simplest of the lot.
Option Explicit
Dim myAppEvents As clsAppEvent
Sub setupAppEvents()
Set myAppEvents = New clsAppEvent
End Sub
Code Sample 13 – code module modAppEvent
A great advantage of using application level events is that
the code can be isolated in a file separate from any of the workbooks
containing data. Save this file as an application specific add-in (a XLA file)
and both problems with Microsoft’s simplified code module approach disappear. Of
course, the astute reader will realize that one question remains. How would
one run the setupEvents procedure? The answer is through the add-in’s Workbook_Open
event procedure – and this would go in the workbook code module of the add-in.
For more details on writing an add-in see Chapter xx.
Pitfalls of event programming
Event programming is appealing at many levels and provides
many capabilities that are impossible with procedural programming. However,
the additional power does not come for free. The developer must be alert to a
variety of programming traps that do not exist in a traditional procedural
program. In their purest form, event procedures are executed when the
associated event occurs. As noted earlier in the chapter if two different
procedures are waiting on two different events and both events occur, there is
no there is no guarantee that procedures will occur in any particular
sequence. In fact, if two procedures are waiting on the same event,
there is no guarantee of the sequence in which they will execute. This makes
debugging code extremely difficult especially for those accustomed to stepping
through code in a traditional environment. Further compounding the issue is
that an operating system that employs preemptive multi-tasking (all
contemporary versions of Windows fall into this category) may interrupt one
event procedure partway through its execution.
Another trap one should be careful of is that the code in an
event procedure will do something that will raise the same event! If that were
to happen, the operating system will execute the event procedure again, which
would raise the event once more, and the system would crash with a fault known
as a stack overflow.
We look at each of these pitfalls in more detail below.
Unreliable program state – do not trust global variables
Since the operating system does not guarantee that event
procedures will be executed in any particular sequence we cannot be sure of the
state of any global variable. As it happens while this is a serious issue in
true event driven programs, it may not be major issue for code in a single file
within the Excel environment. The reason is that Excel is a single-threaded
application. What that means is that Excel itself will run the code in only one
add-in (or workbook) at a time. However, if one was using a COM Add-In or
using events with the Excel object in a standalone program, or running an add-in
in another program such as Word or PowerPoint, this issue would come into
play. See Chapter yyy.
Recursive calls to the same event procedure
This arguably is the single most common mistake programmers
make in developing event driven programs. What it means is that the code in an
event procedure does something that directly or indirectly raises the
associated event again. When that happens, the operating system will call the
event procedure again even though the execution of the procedure from the
previous event has not completed. As this happens repeatedly the call stack
builds up leading to an eventual ‘stack overflow’ program error. To see this,
add the code below in a worksheet code module. The code adds 1 to cell A1 of
that worksheet each time any cell is changed. If there are 10 or more changes
the code executes a breakpoint letting us examine the state of the system.
Option Explicit
Private Sub
Worksheet_Change(ByVal Target As Range)
With
Target.Parent.Cells(1, 1)
If .Value >= 10 Then
Stop
.Value = .Value + 1
End With
End Sub
Code Sample 14
Now, enter zero into cell A1. The value will become 10 and
the code will enter interrupt mode. In the VBE, examine the call stack with
View | Call Stack… It will become apparent that the code in the Worksheet_Change
procedure did something that caused some Non-Basic Code to be executed and that
code, in turn, called the Worksheet_Change procedure again.
Figure 3
Double click any of the Worksheet_Change lines and the VBE
will show:
Figure 4
The yellow arrow indicates where the code currently is. The
green triangle indicates the line that resulted in the last call to some
procedure. Note that it is the .Value=.Value+1 line. Effectively, by changing
the value in cell A1 the code caused Excel to trigger the Worksheet_Change
event again. That happened repeatedly until the IF statement interrupted the process.
This example demonstrated a direct and rather obvious
infinite recursion problem. In other instances, the cause can be more subtle
making debugging very difficult. One event procedure may do something that
raises some event. A procedure associated with that event may do something
that raises yet another event, which would trigger yet another event procedure.
At some point one of the event procedures does something that raises the first
event itself and the process would be back at the start of all the recursive
calls! The recursive loop would keep on repeating itself all over again.
Excel offers a property – EnableEvents – by which one can
suppress events associated with the Excel application. Setting this property
to False will cause Excel to stop raising any event until the property is
changed back to True again. It appears to be very tempting, doesn’t it? We
could rewrite our previous code
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
With Target.Parent.Cells(1, 1)
.Value = .Value + 1
End With
Application.EnableEvents = True
End Sub
Code Sample 15
Now, when Excel adds 1 to the content of cell A1 it will not
raise the Change event. This is a very powerful tool that when used correctly
can dramatically reduce the risk of unintended recursion. However, there are
two cases that the developer should be aware of.
An Excel specific trap: the EnableEvents property
The first problem with the EnableEvents property is that it
applies only to the Excel application. There are many objects that a developer
uses within the Excel environment that are not actually part of Excel object
heirarchy. Event associated with those objects will continue to be raised.
One of the most common objects is a userform! The userform is part of the
Office library and setting Excel’s EnableEvents property to False will have no
effect on events raised by a userform or by objects inside a userform. As it
turns out there is no built-in method to block userform events. For a
workaround see chapter xx.
The other problem with the EnableEvents property is that it
is persistent in that it is not reset to True when user written code stops
executing. By contrast, if one programmatically set ScreenUpdating to False,
Excel will reset it to True when the VBA code stops executing. Since this doesn’t
happen in the case of EnableEvents, it is imperative that the developer reset
it to True. This must happen with no ifs or buts. The easiest way to do so is
to always use an error trap with the EnableEvents property. There are a few
different ways to do this and Code Sample 16 illustrates one.
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ErrXIT
Application.EnableEvents = False
With Target.Parent.Cells(1, 1)
.Value = .Value + 1
End With
ErrXIT:
Application.EnableEvents = True
End Sub
Code Sample 16
Summary
In this chapter, we looked at how events can be used to
significantly enhance the power of object oriented programming. We also saw
how to create event sinks for Excel related events and looked at an apparently
simplified model provided by Microsoft wherein event procedures are declared in
objects directly rather than the associated class definition. Too many
programmers, especially of the hobby variety, are seduced by this seeming ease
and use worksheet and workbook modules for their event procedures. Unfortunately,
the apparent simplicity is more than negated by the security and maintenance
problems. One is best off implementing event level code in an add-in. This is
a discipline that may seem to impose an unnecessary overhead on the developer.
However, as every experienced developer knows the overhead is nothing of the
sort; isolating code from data is a necessity borne out by decades of
experience.
We also looked at some of the more subtle pitfalls of event
programming, including one unique to Excel. Since there is no guarantee of the
sequence in which event procedures are executed, it is easy to use (either read
or write) global variables that are not in a stable state. While these
problems are unlikely for code in a single add-in executed strictly within the
confines of a single-threaded application such as Excel, it can be a
significant source of errors in COM add-in, standalone programs, or add-ins
that execute in other applications but contain Excel-related event procedures.
Finally, we looked at the problem of infinite recursion
resulting from a procedure associated with an event executing code that causes
the same event to be raised again. We also looked at one mechanism provided by
Excel to address this specific problem, the EnableEvents property and the
subtle issues that the developer should be aware of when using this property.
|
