Raise Event

[tiredofit]

The following code is taken from here:

http://www.cpearson.com/Excel/Events.aspx

This is in CExcelEvents:

Option Explicit

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

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

        If Cancel = False Then
            ' Appropriate Non-Cancel Action
        Else
            ' Appropriate Cancel Action
        End If
    End Sub

The article goes on as follows:

Once you have created your class with events, you need to write the code that will receive the event triggers. Note that only object modules (class modules, a user form code module, a Sheet module, or the ThisWorkbook module -- standard code modules cannot receive events) can receive event messages. In a suitable object module, declare the event class using WithEvents:

    Dim WithEvents XLEvents As CExcelEvents
    ' More code

At some point in your code, you will need to set the XLEvents variable to an instance of the CExcelEvents class, with the code:

    Set XLEvents = New CExcelEvents

Exactly when and where you put the object initialization code depends on what sort of module contains the event declaration. While it is technically possible to put the initialization of the variable in another procedure, this is generally a bad programming practice: it makes the code more difficult to debug and maintain. As a general rule, the code that initializes the events variable should be in the same class as the events variable. Of course, the actual event code must reside in the same object module as the events variable declaration. In a class module, the initialization would normally be in the Class_Initialize event. For a user form, the code would go in the UserForm_Initialize event.

    Private Sub XLEvents_EventName(IDNumber Long, Cancel As Boolean)
        Cancel = True ' or False -- your choice
    End Sub

Where should these lines go?

Dim WithEvents XLEvents As CExcelEvents

Set XLEvents = New CExcelEvents

Private Sub XLEvents_EventName(IDNumber Long, Cancel As Boolean)
        Cancel = True ' or False -- your choice
    End Sub

I tried putting them in a worksheet module, such as:

Option Explicit

Dim WithEvents XLEvents As CExcelEvents

Private Sub Worksheet_Change(ByVal Target As Range)
Set XLEvents = New CExcelEvents
End Sub

but it didn't even compile, instead displaying this error message:

Compile error variable not defined

Cancel = False

Thanks

 

[jkpieterse]

In your first code snippet, Cancel has not been declared. You need to add

Dim Cancel as Boolean

just below the line Sub AAA()

 

[tiredofit]

In your first code snippet, Cancel has not been declared. You need to add

Dim Cancel as Boolean

just below the line Sub AAA()

Thanks

Where should these lines go?

Dim WithEvents XLEvents As CExcelEvents

Set XLEvents = New CExcelEvents

Also, what exactly is the code supposed to do?

 

[jkpieterse]

What are you trying to achieve?

 

[tiredofit]

What are you trying to achieve?

I'm afraid I don't know!

I'm reading the article and trying to understand how to use raise events.

http://www.cpearson.com/Excel/Events.aspx

 

[jkpieterse]

Right. Hard to answer your question without having access to your current code.

 

[tiredofit]

Does the article not help?

I've read it but not understood 100% but I'm sure you can make more sense of it.

 

[jkpieterse]

I can certainly make sense of the article, I've written custom events myself in the past. I hardly ever have a need for them though.

 

[tiredofit]

I can certainly make sense of the article, I've written custom events myself in the past. I hardly ever have a need for them though.

I feel better already!

 

[jkpieterse]

Perhaps this article gives you a better idea of a useful implementation of a class module: Excel: Preparations