Performing multiple tasks in an event
procedure
There are times when one wants to perform 2 or more tasks in an event procedure. Those who are not familiar with the VBA architecture try and duplicate the event procedure, which, of course, leads to an 'Ambiguous Name' error. An example of what someone wanted to do was to do one thing if column V changed and do another thing if any of columns L, N, P, R, or T changed. The original attempt of code in the code module for the worksheet looked like:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim C As Range, D As Range
Set D = Intersect(Range("V:V"), Target)
If D Is Nothing Then Exit Sub
For Each C In D
'...
Next C
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("L:L,N:N,P:P,R:R,T:T")) Is Nothing Then
'...
End If
End Sub
In the next three sections we look at three ways to solve the above problem. The first section is something many recommend as "the solution" to the above problem. I, personally, prefer the more modular approach of the second section...actually, even better is the independence of the technique in the third section.
In the fourth section we look at an enhancement available through the .Net platform that allow us to take an arguably more direct approach.
In the fifth, and final, section, we look at event procedures which can tell Excel to cancel whatever the user wants done. For example, in a BeforeClose event procedure, one can cancel the close process. This section looks at options on how to handle such return values.
One popular approach to the above kind of problem is to stack the different code segments one after another. In the above case the code would look like:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim C As Range, D As Range
Set D = Intersect(Range("V:V"), Target)
If D Is Nothing Then Exit Sub
For Each C In D
'...
Next C
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("L:L,N:N,P:P,R:R,T:T")) Is Nothing Then
'...
End If
End Sub
The above, of course, does not work! In addition, it suffers from four major problems.
First and foremost, is maintenance. It is quite possible that the two tasks belong to two different people. They have to coordinate with each other as to who is changing the event procedure and when.
The second issue is that the code segments are no longer independent. The person responsible for the 1st task has a 'Exit Sub' statement if the Target variable does not include column V. So, there is no way that the code would ever reach the 2nd segment when the changed range was something other than V. And, of course, the 2nd segment works only with a non-V range! Similarly, the 2nd person included a 'Exit Sub' statement in case the Target variable did not meet another requirement. But, now, what if someone else wanted to add a third capability to the event procedure? That code would never run as long as Target.Cells.Count >1 was true! In addition, the different code segments are also intertwined in terms of names and the scope of the variables they each declare and use. Yes, all these issues are addressable but the different developers need to be aware of them and interact closely with each other.
Third, a fault in the code performing one task would cause the rest of the tasks to be skipped. True, this can be addressed with the appropriate use of On Error clauses but it both complicates and further intertwines the code segments.
Fourth, and finally, the code can easily get cumbersome and extremely ungainly when each '... above gets replaced with a large number of lines of code.
A more robust approach to the above problem is to introduce multiple procedures, each of which addresses one task. The 'main' event procedure, then, simply farms out the processing to one procedure at a time. By including error handling in the main procedure, one can minimize the impact of an error in any one procedure. The code below lets each developer do their own thing. The only interaction is the sequence of procedures called in the 'main' event procedure. An additional benefit is the subroutines do not have to be in the same code module. Each could be in a module that is the responsibility of a specific developer. Of course, the Worksheet_Change procedure still has to be in the code module of the worksheet.
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
doFormatting Target
addDate Target
End Sub
Sub doFormatting(ByVal Target As Range)
Dim C As Range, D As Range
Set D = Intersect(Range("V:V"), Target)
If D Is Nothing Then Exit Sub
For Each C In D
'...
Next C
End Sub
Sub addDate(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("L:L,N:N,P:P,R:R,T:T")) Is Nothing Then
'...
End If
End Sub
There is an additional benefit to code written as in this section. On occasion, developers, and this includes some with a fair amount of experience, set Excel's EnableEvents property to False and then because of a fault in their code, fail to turn it on. This is one of those properties that Excel does not "reset" when code finishes execution. Add this additional level of protection as shown below.
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
doFormatting Target
addDate Target
Application.EnableEvents = True
End Sub
This section illustrates how, for the most part, I handle event programming. Rather than put code in the worksheet's code module (or the workbook's code module), I use a class that includes a WithEvents variable hooked to the appropriate object. Then, I write event procedures that respond to this object's events. While this requires more "set up" than the previous methods, the benefits more than outweigh the costs. Since this risks coming across as an "advanced" process, I will only outline the mechanism here and skip the details of the implementation.
The idea is for each functionality (formatting and date-entry in the above example) to work independently. Consequently, for each create a class module that has a 'with events' variable of type worksheet. In the class's Initialize procedure (or through a method) connect the variable to the actual worksheet of interest. Now, add an appropriate event procedure for this variable and include the code to implement the desired functionality in there (alternatively, have this procedure simply call a separate procedure in a normal module as in the last section).
Since each functionality uses its own class module (and normal module) the two code instances are independent of each other. Of course, the normal caveats apply. Each procedure is responsible for cleaning up after itself -- in particular, it must leave the EnableEvents property as True.
In the .Net platform any procedure with the correct signature can handle an event. This lets different developers write their own routines, each of which responds independently to the event of interest. In the below example FormatChanges and UpdateDate both handle the aSheet.Change event.
'...
Dim WithEvents
aSheet As Excel.Worksheet
'...
Private Sub
FormatChanges( _
ByVal Target
As Microsoft.Office.Interop.Excel.Range) _
Handles aSheet.Change
If aSheet.Name <>
"Sheet1" Then
Exit Sub
Dim aTarg As
Excel.Range _
= ThisApplication.Intersect(Target, Target.Parent.range("V:V"))
If aTarg
IsNot Nothing
Then
MsgBox(aTarg.Address)
'...
End If
End Sub
Private Sub
UpdateDate( _
ByVal Target
As Microsoft.Office.Interop.Excel.Range) _
Handles aSheet.Change
If aSheet.Name <>
"Sheet1" Then
Exit Sub
Dim aTarg As
Excel.Range _
= ThisApplication.Intersect(Target, _
Target.Parent.Range("L:L,N:N,P:P,R:R,T:T"))
If aTarg
IsNot Nothing
Then
MsgBox(aTarg.Address)
'...
End If
End Sub
Certain event procedures require a ByRef Boolean variable, typically called Cancel. If the event procedure returns a value of True, Excel will cancel whatever event is about to happen. For example, the code below disallows any double-click in the worksheet.
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
Cancel As Boolean)
Debug.Print "Worksheet " & Now
Cancel = True
End Sub
It turns out that if there are multiple procedures waiting on the BeforeDoubleClick event Excel calls all those procedures with the same Cancel variable. Consequently, at the end of the calls to all waiting procedures Excel processes the last change in the value of the variable. As an aside, it also means that an event procedure can check if a prior procedure has told Excel to cancel the event. We can simulate the same behavior in the modularized approach above with code along the lines of the below.
Private Sub doBeforeDoubleClick1(ByVal Target As Range, _
Cancel As Boolean)
'...
End Sub
Private Sub doBeforeDoubleClick2(ByVal Target As Range, _
Cancel As Boolean)
'...
End Sub
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
Cancel As Boolean)
On Error Resume Next
doBeforeDoubleClick1 Target, Cancel
doBeforeDoubleClick2 Target, Cancel
Application.EnableEvents = True
End Sub
