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

 

[mangeshmm]

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.

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

Jaafar - I stepped through the modified code which you have posted. I have also noted the design/coding protocol which you laid out before the code. A few reactions:

1. The event source class has a property level Cancel as boolean variable. I am assuming that every reference to Cancel thereafter in the property references the ByRef Cancel passed through the RaiseEvent, correct? Which pretty much makes the property level Cancel variable irrelevant? Wanted to clear that up since it helps me distill the code better

2. When you say the RaiseEvent can and should have the code, I note that the "outcome" of the event, which is the message box response has been moved under the Event procedure. However, the max value continues to remain in the listener class - which is how I guess the construct should be (and not an outcome of trying to highlight the RaiseEvent vs call dilemma). Intuitively, I guess that this is exactly how the construct ought to be since it allows different sink classes to specify their own max value and, in turn, set the Cancel value. But your confirmation on this would be appreciated

3. I understand that the message box with the outcome of the event is merely a placeholder for code that the source class needs to perform. Given that the parameters to keep running (or stop) are in the sink class, in a real world scenario, what kind of code would one therefore expect to see to substitute the message box response? You dont have to put the code in, merely a descriptive example/pointer would do nicely. I ask because, from where I see the world, the fact that the sink class specifies the max_value that the real operation is carried out in the sink_class and merely reported back to the user through the message box from the source class.

4. Stepping through the code posted also revealed that there is a module level boolean operator (bool) in the sink class, which is now hooked with the RaiseEvent boolean operator which permits/prohibits the sink class property to be Let (set). Is this an accepted design for custom event framework. I went over this question again and I know it sounds quite fringy - because I am aware there are a million different ways of doing the same thing. What I am really interested in is knowing if there is a "right way" in terms of design for this element, and if so, whether your example adopts the commonly adopted method

Thank you Jaafar for all your help (and patience!)

Mangesh

 

[Jaafar Tribak]

I have been playing around to see if the RaiseEvent would work from within an Interface class or it would generate a compile error .

The following experimentation worked fine although I am not sure there is any real benefits of doing this when the same could be achieved in simpler ways.. Just curiosity.

1- Add a new Class for the interface called IMyInterface and place the following code its module:

Option Explicit

Public Event SomeEvent()

Public Sub ShowMe()
 '
End Sub

Public Sub RaiseSomeEvent()
    RaiseEvent SomeEvent
End Sub

2- Add a Useform with a commandbutton and put this code in its module :

Option Explicit

Implements IMyInterface
Private WithEvents oInterfaceImplement As IMyInterface

Private Sub UserForm_Initialize()
    Set oInterfaceImplement = New IMyInterface
    CommandButton1.Caption = "Click to Raise Event"
    CommandButton1.AutoSize = True
End Sub

Private Sub IMyInterface_ShowMe()
    Me.Show
End Sub

Private Sub IMyInterface_RaiseSomeEvent()
 '
End Sub

Private Sub CommandButton1_Click()
    oInterfaceImplement.RaiseSomeEvent
End Sub

Private Sub oInterfaceImplement_SomeEvent()
    MsgBox "Event Raised from the Interface !"
End Sub

3- Test in a Standard Module:

Option Explicit

Private oRaiseEventExample As IMyInterface

Sub Test()
    Set oRaiseEventExample = New UserForm1
    oRaiseEventExample.ShowMe
End Sub

Click the commandbutton to raise the event when the userform is shown.

 

[Jaafar Tribak]

Jaafar - I stepped through the modified code which you have posted. I have also noted the design/coding protocol which you laid out before the code. A few reactions:

1. The event source class has a property level Cancel as boolean variable. I am assuming that every reference to Cancel thereafter in the property references the ByRef Cancel passed through the RaiseEvent, correct? Which pretty much makes the property level Cancel variable irrelevant? Wanted to clear that up since it helps me distill the code better

2. When you say the RaiseEvent can and should have the code, I note that the "outcome" of the event, which is the message box response has been moved under the Event procedure. However, the max value continues to remain in the listener class - which is how I guess the construct should be (and not an outcome of trying to highlight the RaiseEvent vs call dilemma). Intuitively, I guess that this is exactly how the construct ought to be since it allows different sink classes to specify their own max value and, in turn, set the Cancel value. But your confirmation on this would be appreciated

3. I understand that the message box with the outcome of the event is merely a placeholder for code that the source class needs to perform. Given that the parameters to keep running (or stop) are in the sink class, in a real world scenario, what kind of code would one therefore expect to see to substitute the message box response? You dont have to put the code in, merely a descriptive example/pointer would do nicely. I ask because, from where I see the world, the fact that the sink class specifies the max_value that the real operation is carried out in the sink_class and merely reported back to the user through the message box from the source class.

4. Stepping through the code posted also revealed that there is a module level boolean operator (bool) in the sink class, which is now hooked with the RaiseEvent boolean operator which permits/prohibits the sink class property to be Let (set). Is this an accepted design for custom event framework. I went over this question again and I know it sounds quite fringy - because I am aware there are a million different ways of doing the same thing. What I am really interested in is knowing if there is a "right way" in terms of design for this element, and if so, whether your example adopts the commonly adopted method

Thank you Jaafar for all your help (and patience!)

Mangesh

1- I am not sure I understand .. The Cancel argument in the source class must be there as its value changes in every time the event handler is executed.

2- Yes, that's how the construct should be since it allows different sink classes to specify their own max value.

3- That's a good point . Intuitively, once the MaxValue is reached, the code shouldn't keep running which, unfortunately is not the case in my example.

I can't really think of a real world alternative to the messagebox right now .. I would have to rethink the design as I just improvised this MaxValue example as I went along.

4- I had to include that bool variable in the sink class .. This is not an accepted design . It looks tacky to me but I couldn't come up with a better solution to stop Letting the GetSomeValue Property.

I am by no means an expert in OOP, I just learn myself as I go along like in this thread.

Thank you for stimulating my mind with your good questions.

 

[Kyle123]

Essentially events require no knowledge of the subscribing object, they send out messages when something happens. This means that it's possible for multiple objects to subscribe to the events of a single object:

CState

Public Event ModelChanged(ByVal v As Long)

Public Property Let someProperty(v As Long)
    RaiseEvent ModelChanged(v)
End Property

Subscriber

Public WithEvents state As CState
Private pId As String

Private Sub state_ModelChanged(ByVal v As Long)
    MsgBox "Model has updated value is now " & v & " - Subscription from " & pId
End Sub

Public Property Let ID(v As String)
    pId = v
End Property

Module:

Sub test()

    Dim stateModel As CState
    Dim sub1 As Subscriber
    Dim sub2 As Subscriber
    
    
    Set stateModel = New CState
    
    Set sub1 = New Subscriber
    sub1.ID = "Sub1"
    Set sub1.state = stateModel
    
    
    Set sub2 = New Subscriber
    sub2.ID = "Sub2"
    Set sub2.state = stateModel
    
        
    stateModel.someProperty = 1
    
End Sub

 

[mangeshmm]

Jaafar - in the standard module, did you mean to actually declare oRaiseEventExample as a userform1 (rather than as iMyInterface)? Thanks a lot for posting this - I find myself slowly plodding through as your examples add on richer and more diverse features! Thanks a million!

 

[Jaafar Tribak]

Jaafar - in the standard module, did you mean to actually declare oRaiseEventExample as a userform1 (rather than as iMyInterface)? Thanks a lot for posting this - I find myself slowly plodding through as your examples add on richer and more diverse features! Thanks a million!

I meant as IMyInterface which is one of the reasons of using an interface class in the first place.

The actual Object stored in the variable oRaiseEventExample is UserForm1 but now the userform also implements the interface IMyInterface.

By declaring the variable holding the userform as IMyInterface, the Userform will not expose its default Properties and Methods which we don't need .. It will expose only the Properties and Methods of the Interface Class ie: Only ShowMe and RaiseSomeEvent .. You can verify this via the vbe intellsense in the standard module .