Further Clarification with Event Handlers & Class Modules

[Kevin H. Stecyk]

Hi,

This post is a continuation from a prior post on the same topic.

http://tinyurl.com/6a8wd

My prior post was as follows:

1) You need class modules for event handlers.

2) ThisWorkbook is a class module. It is "already set up for you" in the
sense that you don't need to instantiate it. That's already set up. The
Workbook_Open event, for example, is already instantiated. (Question, are
any and all event handlers placed in ThisWorkbook class module
pre-instantiated? Could you place ALL your event handler for the current
and external workbooks in ThisWorkbook class module?)

3) ThisWorkbook class module is used for current workbook events.
(Question, can you place other event handlers in ThisWorkbook class module
that are used for external workbooks? I am guessing NO. But I want to be
clear in my understanding.)

4) You can create your own class modules. When dealing with external
workbooks, event handlers are usually created in a separate class modules
outside of ThisWorkbook class module. You can rename your class modules to
suit your purpose. But these class modules remain void until they are
instantiated by another event handler within ThisWorkbook class module.
Once they are instantiated, they become active.

Thank you Tom for walking me through this material.

Question 5)

My last question concerns Chip's notes on his site:

http://www.cpearson.com/excel/AppEvent.htm

Quoting:

~~~~~~~~~~~
Enter the following code in the Workbook_Open event procedure for your
workbook:

Set AppClass.App = Application

If desired, you can move the code to initialize the App object to the
Initialize event of the class itself. This event is automatically executed
when a new object based on the class module is created. There is also a
corresponding Terminate event.

Private Sub Class_Initialize()
Set App = Application
End Sub


If you use this technique, you can remove the following line of code from
your Workbook_Open event procedure.

Set AppClass.App = Application

Now, your workbook is ready to process application level events.
~~~~~~~~~~~~~

I understand how the Workbook_Open instantiates the class module. But I am
less clear on the "initialize" event. Can someone please elaborate on how
the two differ and why you would use one form over another.

This should be my last set of questions on this topic for a while. I should
have sufficient information to go and experiment and discover on my own for
a while.

I know I have asked a lot of questions regarding event handlers and class
modules. I never understood this topic very well, so I wanted to take this
opportunity to try to better understand this topic. I am appreciative of
everyone's assistance.

Thank you.

Best regards,
Kevin

 

[Tom Ogilvy]

In Line:

Hi,

This post is a continuation from a prior post on the same topic.

http://tinyurl.com/6a8wd

My prior post was as follows:

1) You need class modules for event handlers.

A: Yes (but not necessarily a separate class module as pointed out by
KeepItCool)

2) ThisWorkbook is a class module. It is "already set up for you" in the
sense that you don't need to instantiate it. That's already set up. The
Workbook_Open event, for example, is already instantiated. (Question, are
any and all event handlers placed in ThisWorkbook class module
pre-instantiated? Could you place ALL your event handler for the current
and external workbooks in ThisWorkbook class module?)

A: I believe yes as far as KeepItCool said you could set up the
thisworkbook module to handle application level events. An instance of the
class (thisworkbook) is automatically created (instantiated) by Excel when
the workbook is opened.

3) ThisWorkbook class module is used for current workbook events.
(Question, can you place other event handlers in ThisWorkbook class module
that are used for external workbooks? I am guessing NO. But I want to be
clear in my understanding.)

A: See the answer above - so the answer would be Yes

4) You can create your own class modules. When dealing with external
workbooks, event handlers are usually created in a separate class modules
outside of ThisWorkbook class module. You can rename your class modules to
suit your purpose. But these class modules remain void until they are
instantiated by another event handler within ThisWorkbook class module.
Once they are instantiated, they become active.

A: insantiation is creating an instance of the class. That is done with
the command

Dim AppClass As New EventClass

where EventClass is the name of the class module. AppClass is the instance
of the EventClass.

In the EventClass module you had the statement

Public WithEvents App As Application

This ties your class module to the predefined application level event
definitions (defined in Excel VBA).

So now all the structures are set up and the instance is created
(instantiated). Now you just need to tie this instance to the Excel
application object. Think of the application object as having event stubs,
but they don't point anywhere. All code has to be in a workbook so we now
need to point the stubs to the workbook with the code for the events. We do
this with

Set app = Application

This makes the events active as you say. In fact, you can have other
workbooks with different code for the application events (with their own
class modules and so forth). So opening a workbook (for example) could
trigger multiple xxx_WorkbookOpen events - each defined in a separate
workbook's class module.

We want to insure this final step, set app = application, is executed. One
choice is the workbook_open event of the workbook containing the application
level event code. And as Chip points out, whenever the class instance is
created/instantiated, there is an inherent Initialize event that is fired.
So this is another place it could be done.

KeepItCool's suggestion of using the ThisWorkbook module eliminates the need
to instantiate. So it eliminates the need for

Dim AppClass As New EventClass

Thank you Tom for walking me through this material.

Question 5)

My last question concerns Chip's notes on his site:

http://www.cpearson.com/excel/AppEvent.htm

Quoting:

~~~~~~~~~~~
Enter the following code in the Workbook_Open event procedure for your
workbook:

Set AppClass.App = Application

If desired, you can move the code to initialize the App object to the
Initialize event of the class itself. This event is automatically executed
when a new object based on the class module is created. There is also a
corresponding Terminate event.

Private Sub Class_Initialize()
Set App = Application
End Sub


If you use this technique, you can remove the following line of code from
your Workbook_Open event procedure.

Set AppClass.App = Application

Now, your workbook is ready to process application level events.
~~~~~~~~~~~~~

I understand how the Workbook_Open instantiates the class module. But I am
less clear on the "initialize" event. Can someone please elaborate on how
the two differ and why you would use one form over another.

I would see no advantage either way. I discussed this in answering the
previous question.

 

[Kevin H. Stecyk]

Tom,

A huge and sincere thank you for taking the time and effort to respond to my
lengthy questions.

Best regards,
Kevin