CODEWITHSUNDEEP
vbaexcel
user2140173
user2140173

Reputation:

Custom Event not firing

I am in Excel 2010 and I seem to be getting a strange rather unexpected behaviour working with custom events.

I am like 99% sure this approach has worked for me a few years ago (maybe it was in Excel 03/07 - can't remember) or maybe I've just messed something up...

Here's a repro:

Add a new class module and name it Factory

Public Event AfterInitialize()

Private Sub Class_Initialize()
    RaiseEvent AfterInitialize
End Sub

Add another class module and name is FactoryTest

Private WithEvents cFactory As Factory

Private Sub Class_Initialize()
    Set cFactory = New Factory
End Sub

Private Sub cFactory_AfterInitialize()
    Debug.Print "after inialized..."
End Sub

and a standard Module1 and run the below

Sub Main()

    Dim fTest As FactoryTest
    Set fTest = New FactoryTest

End Sub

At this point I expected to see after initialized.. in the Immediate Window but I don't...

Stepping-through the code it seems that the Private Sub cFactory_AfterInitialize() is never reached...

Note:

I can add a public sub: RaiseAfterInitialize() to the Factory class and then call that explicitly in the Initialize() event in the FactoryTest like cFactory.RaiseAfterInitialize() so that could be a possible work around but what I am really trying to understand is why it doesn't work the original way showed above?

There isn't much on VBA events on MSDN

Can anyone point out what I am doing wrong?

Upvotes: 12

Views: 1794

Answers (1)

Blackhawk
Blackhawk

Reputation: 6120

Based on the VBA Language Specification section 5.3.1.10 Lifecycle Handler Declarations, I would guess this is the reason (emphasis mine):

If a class defines a Class_Initialize lifecycle handler, that subroutine will be invoked as an method each time an instance of that class is created by the New operator, by referencing a variable that was declared with an <as-auto-object> and whose current value is Nothing, or by call the CreateObject function (section 6.1.2.8.1.4) of the VBA Standard Library. The target object of the invocation is the newly created object. The invocation occurs before a reference to the newly created object is returned from the operations that creates it.

So in your case, in the line

Set cFactory = New Factory

The Class_Initialize method of Factory is run before the assignment is made, which means while the event IS raised, the FactoryTest class instance doesn't know about it.

UPDATE

I gave it a quick test by adding a method to Factory which calls the Class_Initialize function:

Public Sub test()
    Class_Initialize
End Sub

And then added a call to it as part of the FactoryTest.Class_Initialize method:

Private Sub Class_Initialize()
    Set cFactory = New Factory
    cFactory.test
End Sub

Since the call to the method test takes place after the New Factory has been assigned to cFactory, the "after initialized..." message displays as expected.

Upvotes: 16

Related Questions