Programming Excel with VBA and .NET

Raise Events

I mentioned earlier that, unlike modules, classes can include events . Classes define events using an Event statement and then raise those events using RaiseEvent. For example, the following additions (in bold) create an event that occurs whenever a message is shown or sent using the Message class:

    ' Message class
    Public Event OnShow(arg As MessageType)

    ' Show method: displays the message.
    Public Sub Show(Optional icon As IconType = -1)
        If (icon = -1) Then icon = Me.icon
        MsgBox value, icon, Title
        RaiseEvent OnShow(MessageType.MessageBox)
    End Sub

    ' Send method: sends the message via email.
    Public Sub Send(Optional ToAddress As String)
        Dim msgToSend As String, result As Double
        If ToAddress = "" Then ToAddress = m_Recipients
        msgToSend = "mailto:" & ToAddress
        msgToSend = msgToSend & "?SUBJECT=" & Title
        msgToSend = msgToSend & " &BODY=" & value
        ThisWorkbook.FollowHyperlink msgToSend, , True
        RaiseEvent OnShow(Email)
    End Sub

Responding to the OnShow event from within code that uses the Message class requires a few steps:

1. Write your code in a class—you can’t intercept events from a module. For example, write your code in a Sheet object within Visual Basic.

2. Declare the object at the class level using WithEvents.

3. Initialize that object by creating an instance of the class.

4. Create an event procedure to respond to the event.

The following sample illustrates the steps to using the OnShow event from a Sheet object:

1. Create code in Sheet object.

2. Declare Message object using WithEvents: ...