Custom Events in Excel VBA

[mangeshmm]

Greetings from Mumbai/India. I have a question on custom events. While the native events in Excel (eg worksheet change, worksheet open, workbook before save, etc) are intuitive and lend themselves fairly well to where and how to apply them, I continue to be puzzled about custom events. To be clear, I am not referring to those custom objects qualified by a WithEvents that actually have a native excel object 'underneath'. For eg, Dim WithEvents mWks as Excel.worksheet. In such cases, I am aware that selecting an event from the dropdown menu qualifies only mWks to respond to event triggers.

What I am yet to get my head around are the events which are triggered through a RaiseEvents command. I am unable to understand the need, and consequently, the relevance of going through the whole effort of defining an event and raising it - when it seems to me that a simple Call someProcedure will give you exactly the same results. Further, unlike the native Excel events, or events tied to native underlying excel objects, where the processor is responsible for detecting that an event has triggered, it appears that the RaiseEvents command places the onus on the developer to raise it, thereby eliminating the intuitiveness of a set of actions being triggered once the event fires.

Not having clarity/answers to the above has left me feeling that I am not harnessing the full power of user defined custom events. What has not helped is a singular lack of relevant examples online that once can go through which can help you divine a few answers.

Thanking everyone in advance for their time and attention to help out here.

Mangesh

 

[Jaafar Tribak]

This is a good and legitimate question.. I believe one reason is to keep in line with Object Oriented Programming and the other reason is that RaiseEvent can call\execute a Private event Procedure defined in a Class module whereas a simple Call somePrivateEventProcedure won't work.

The Standard way is that Event Procedures should always be declared as Private.

 

[mangeshmm]

This is a good and legitimate question.. I believe one reason is to keep in line with Object Oriented Programming and the other reason is that RaiseEvent can call\execute a Private event Procedure defined in a Class module whereas a simple Call somePrivateEventProcedure won't work.

The Standard way is that Event Procedures should always be declared as Private.

Thanks Jaffar - if one part of the reason is to induce an OOP imperative, then so be it. The point of calling a private procedure is well noted. However, this ability/entitlement to call a private procedure manifests itself in precisely the same manner as calling a public Sub within a class module.

Since, therefore, there is no incremental functionality built in by the ability to call a private procedure, is it fair to say that there is probably no more than an OOP design angle to the custom events set up?

Happy to understand any thoughts out there. Also, if someone has an example which can shed light on the utility and relevance of using a custom event (where nothing else would do), it would certainly go a long way in cementing some impressions and modifying others.

Thanks
Mangesh

 

[Jaafar Tribak]

Maybe an example can illustrate this better .

1- Event Source Class: Clss_EventSource

Option Explicit

Public Event MaxValueReached(ByVal NewValue As Integer)

Public Property Let GetSomeValue(ByVal vNewValue As Integer)
   RaiseEvent MaxValueReached(vNewValue)
End Property

2- Event Sink Class : Clss_EventSink

Option Explicit

Public WithEvents oSinkClass As Clss_EventSource

Private Sub Class_Initialize()
    Set oSinkClass = New Clss_EventSource
End Sub

Public Property Let GetSomeValue(ByVal vNewValue As Integer)
   oSinkClass.GetSomeValue = vNewValue
End Property

Private Sub oSinkClass_MaxValueReached(ByVal MaxValue As Integer)
    If MaxValue = 10 Then
        MsgBox "Max Value Reached !" & vbCrLf & "Values Cannot exceed : " & MaxValue, vbCritical
    End If
End Sub

3- Test in a Standard Module :

Option Explicit

Sub Test()
    Dim oClass As Clss_EventSink
    Dim i As Long
    
    Set oClass = New Clss_EventSink
    
    For i = 1 To 100
        oClass.GetSomeValue = i
    Next i
End Sub

As you can see in the event source class, there is no way to raise the event in the event sink class other than by using the RaiseEvent statement.

You cannot use something like the following even if the event handler routine (oSinkClass_MaxValueReached) was declared Public in the event sink class :

Call oSinkClass_MaxValueReached.MaxValueReached(vNewValue) [COLOR=#006400][B]' <== Compile Error[/B][/COLOR]

Having said that, you could use some ugly and complicated workaround(s) to avoid the need of using RaiseEvent and use Call instead by making the event handler routine Public and passing the current instance of the event sink class to the source event class .. Something as follows:

1- Event Source Class: Clss_EventSource

Option Explicit

Public Event MaxValueReached(ByVal NewValue As Integer)

Public Property Let GetSomeValue([COLOR=#ff0000][B]ByVal SinkClass As Clss_EventSink[/B][/COLOR], ByVal vNewValue As Integer)
  [B][COLOR=#ff0000] Call SinkClass.oSinkClass_MaxValueReached(vNewValue[/COLOR][/B])
End Property

2- Event Sink Class : Clss_EventSink

Option Explicit

Public WithEvents oSinkClass As Clss_EventSource

Private Sub Class_Initialize()
    Set oSinkClass = New Clss_EventSource
End Sub

Public Property Let GetSomeValue(ByVal vNewValue As Integer)
   oSinkClass.GetSomeValue([COLOR=#ff0000][B]Me[/B][/COLOR]) = vNewValue
End Property

[COLOR=#ff0000][B]Public[/B][/COLOR] Sub oSinkClass_MaxValueReached(ByVal MaxValue As Integer)
    If MaxValue = 10 Then
        MsgBox "Max Value Reached !" & vbCrLf & "Values Cannot exceed : " & MaxValue, vbCritical
    End If
End Sub

 

[mangeshmm]

Maybe an example can illustrate this better .

This is brilliant Jaafar! The way you have brought out the efficiency of using a RaiseEvent is simply fantastic. Not only has it cleared a lot of webs in my mind, it has also opened my eyes to a few other things which you have used in the example to compare and contrast. Thanks a million for lucidly laying out the point you made earlier. Much appreciated mate!

 

[Jaafar Tribak]

This is brilliant Jaafar! The way you have brought out the efficiency of using a RaiseEvent is simply fantastic. Not only has it cleared a lot of webs in my mind, it has also opened my eyes to a few other things which you have used in the example to compare and contrast. Thanks a million for lucidly laying out the point you made earlier. Much appreciated mate!

mangeshmm,

I am glad you found the post useful,

Just for the record, I have made a few small changes to the codes to make the example more accurate and to ensure that the GetSomeValue Property doesn't store/allow values greater than 10 as it should :

1- Event Source Class: Clss_EventSource

Option Explicit

Public Event MaxValueReached(ByVal NewValue As Integer)

Public Property Let GetSomeValue(ByVal vNewValue As Integer)
   RaiseEvent MaxValueReached(vNewValue)
End Property

2- Event Sink Class : Clss_EventSink

Option Explicit

Public WithEvents oSinkClass As Clss_EventSource

Private NewValue As Integer
Private bool As Boolean

Private Sub Class_Initialize()
    Set oSinkClass = New Clss_EventSource
End Sub

Public Property Let GetSomeValue(ByVal vNewValue As Integer)
    If bool Then Exit Property
    oSinkClass.GetSomeValue = vNewValue
    NewValue = vNewValue
End Property

Public Property Get GetSomeValue() As Integer
    GetSomeValue = NewValue
End Property

Private Sub oSinkClass_MaxValueReached(ByVal MaxValue As Integer)
    If MaxValue = 10 Then
        bool = True
        MsgBox "Max Value Reached !" & vbCrLf & "Values Cannot exceed : " & MaxValue, vbCritical
    End If
End Sub

3- Test in a Standard Module :

Option Explicit

Sub Test()
    Dim oClass As Clss_EventSink
    Dim i As Integer
    
    Set oClass = New Clss_EventSink
    
    For i = 1 To 100
        oClass.GetSomeValue = i
        Debug.Print oClass.GetSomeValue
    Next i
End Sub

 

[mangeshmm]

mangeshmm,

I am glad you found the post useful,

Just for the record, I have made a few small changes to the codes to make the example more accurate and to ensure that the GetSomeValue Property doesn't store/allow values greater than 10 as it should :

1- Event Source Class: Clss_EventSource

Option Explicit

Public Event MaxValueReached(ByVal NewValue As Integer)

Public Property Let GetSomeValue(ByVal vNewValue As Integer)
   RaiseEvent MaxValueReached(vNewValue)
End Property

2- Event Sink Class : Clss_EventSink

Option Explicit

Public WithEvents oSinkClass As Clss_EventSource

Private NewValue As Integer
Private bool As Boolean

Private Sub Class_Initialize()
    Set oSinkClass = New Clss_EventSource
End Sub

Public Property Let GetSomeValue(ByVal vNewValue As Integer)
    If bool Then Exit Property
    oSinkClass.GetSomeValue = vNewValue
    NewValue = vNewValue
End Property

Public Property Get GetSomeValue() As Integer
    GetSomeValue = NewValue
End Property

Private Sub oSinkClass_MaxValueReached(ByVal MaxValue As Integer)
    If MaxValue = 10 Then
        bool = True
        MsgBox "Max Value Reached !" & vbCrLf & "Values Cannot exceed : " & MaxValue, vbCritical
    End If
End Sub

3- Test in a Standard Module :

Option Explicit

Sub Test()
    Dim oClass As Clss_EventSink
    Dim i As Integer
    
    Set oClass = New Clss_EventSink
    
    For i = 1 To 100
        oClass.GetSomeValue = i
        Debug.Print oClass.GetSomeValue
    Next i
End Sub

Thanks again Jaafar. Your code has left me, at once, feasting on the concepts embedded in there and partially envious at the beauty within simplicity at the manner in which the example and the compare case has been drawn up. Someday..........!

 

[mangeshmm]

Hi Jaafar - I went through your code and studied it quite closely. As I mentioned earlier, I landed up learning quite a bit in the process - which includes things not restricted to my query. In fact, I extended your code and made 2 more sinkclasses with variations in the code for the MaxValueReached procedure. In the second sink class I tweaked the max_value while in the third sink class I used the counter to add rows to a listobject. What I also discovered in this process, which was both illuminating in one way and confusing in another, was this. There is no event code in the source class for the event - rather, it hands back the control to the calling class to run the required set of actions peculiar to each class that listens to the event. And it was great to see the way this functionality can be used (as was embedded in your example which i extended, to specify a common counter where each class is responsible to monitor how far the counter runs before the plug is pulled). That also means that the raise_event procedure is actually mimicking an interface that has no code but is merely acting as an interface to pass an argument. Which leads me to 2 questions:

1. In cases where there is no code under the raise_event procedure, is it fair to say that it closely mirrors an interface element? Consequently, would implementing an interface achieve the same results? Or, to put the question more appropriately, is there always a case made to carefully evaluate whether to go down the interface object route or the raise_event route where the design of the event is like in the example above?

2. In Chip Pearson's example (http://www.cpearson.com/excel/Events.aspx), he has outlined the framework of using custom events, but the source class has some code within the public sub in which the event is raised. The part that becomes totally confusing is that the public sub is not referenced at all in the rest of the example, which is pretty much a dead end for how the event listener actually behaves. That, as well as the fact that the event parameters are specified in the same sub in the source class, which begs the question as to how the event is integrated with the rest of the project. There is no download file, which leads me to believe that the entire code should be displayed in the example - which also means that I am missing to pick something up. I am aware that the way I have framed this question may not be very telegraphic Im just hoping that when you go through the example, a few things that I might not have been to articulate should become clear.

Thanks a lot

 

[Jaafar Tribak]

Hi,

No. In fact the code CAN and SHOULD be,in my opinion, under the RaiseEvent procedure so it can respond and act according to the event sink handler .. this inter-communication is normaly achieved by passing the Arguments ByRef. The reason I didn't place the code in the event source module was just to focus on the RaiseEvent Vs Call.

In contrast, an interface sould not contain any code. Having said that, I've never tried including a RaiseEvent statement in an interface Method/Property.

Chip's example is a good example but had a couple of things missing .. Here is working demo of Chip's code :

1- Class Module : CExcelEvents

Public Event EventName(ByVal IDNumber As Long, ByRef Cancel As Boolean)

Public Sub AAA()
    Dim Cancel As Boolean
    Dim IDNumber As Long
    IDNumber = 1234
    Cancel = False
    RaiseEvent EventName(IDNumber, Cancel)

[B][COLOR=#006400]    'The following should also work if you declare the event handler in the userorm as *Public*[/COLOR][/B]
[B][COLOR=#006400]    '=========================================================================================[/COLOR][/B]
[B][COLOR=#ff0000]    ' Call UserForm1.XLEvents_EventName(IDNumber, Cancel)[/COLOR][/B]
    
    MsgBox "You set the Cancel Argument to : " & Cancel

End Sub

2- UserForm1 Module:

Option Explicit

Private WithEvents XLEvents As CExcelEvents

Private Sub UserForm_Initialize()
    Set XLEvents = New CExcelEvents
End Sub

Private Sub CommandButton1_Click()
    [COLOR=#008000]'Raise the event.[/COLOR]
    XLEvents.AAA
End Sub

Private Sub XLEvents_EventName(ByVal IDNumber As Long, ByRef Cancel As Boolean)
    Cancel = True [COLOR=#008000]'or False -- your choice[/COLOR]
End Sub

Now, here is a variation of my previous example where part of the code is now under the RaiseEvent procedure:

1- Clss_EventSource Code :

Option Explicit

Public Event MaxValueReached(ByVal NewValue As Integer, ByRef Cancel As Boolean)

Public Property Let GetSomeValue(ByVal vNewValue As Integer)
   Dim Cancel As Boolean

   RaiseEvent MaxValueReached(vNewValue, Cancel)
   If Cancel Then
        MsgBox "Max Value Reached !" & vbCrLf & "Values Cannot exceed : " & vNewValue, vbCritical
   End If
End Property

2- Clss_EventSink Code :

Option Explicit

Private WithEvents oSinkClass As Clss_EventSource

Private NewValue As Integer
Private bool As Boolean

Private Sub Class_Initialize()
    Set oSinkClass = New Clss_EventSource
End Sub

Public Property Let GetSomeValue(ByVal vNewValue As Integer)
    If bool Then Exit Property
    oSinkClass.GetSomeValue = vNewValue
    NewValue = vNewValue
End Property

Public Property Get GetSomeValue() As Integer
    GetSomeValue = NewValue
End Property

Private Sub oSinkClass_MaxValueReached(ByVal MaxValue As Integer, ByRef Cancel As Boolean)
    If MaxValue = 10 Then
        Cancel = True
        bool = Cancel
    End If
End Sub

3- Test in a Standard Module:

Option Explicit

Sub Test()
    Dim oClass As Clss_EventSink
    Dim i As Integer

    Set oClass = New Clss_EventSink

    For i = 1 To 100
        oClass.GetSomeValue = i
        Debug.Print oClass.GetSomeValue
    Next i
End Sub

 

[mangeshmm]

Jaafar - thank you again. I am writing back before actually studying your answer. Just wanted to put in a thank you again for all your effort - before the follow on questions emerge!!

Thanks awfully for all your help!

Mangesh