Using Events with the Application Object & XL 2003

[Kevin H. Stecyk]

Hi,

I have never understood Class Modules very well. So please bear with as I
ask my questions.

My objective is to do the following:

1) Create a Private Sub App_WorkbookOpen(ByVal Wb As Workbook)
2) In App_WorkbookOpen routine, I want to set a vba global variable.

I am not well versed with using event handlers.

So XL 2003 Help says,
~~~~
Before you can use events with the Application object, you must create a new
class module and declare an object of type Application with events. For
example, assume that a new class module is created and called
EventClassModule. The new class module contains the following code.

Public WithEvents App As Application
~~~~

I understand that.

Now it says,

~~~~
After the new object has been declared with events, it appears in the Object
drop-down list box in the class module, and you can write event procedures
for the new object. (When you select the new object in the Object box, the
valid events for that object are listed in the Procedure drop-down list
box.)

Before the procedures will run, however, you must connect the declared
object in the class module with the Application object. You can do this with
the following code from any module.

Dim X As New EventClassModule

Sub InitializeApp()
Set X.App = Application
End Sub

After you run the InitializeApp procedure, the App object in the class
module points to the Microsoft Excel Application object, and the event
procedures in the class module will run when the events occur.
~~~~

My understanding here is much weaker.

Once I have created the Private Sub App_WorkbookOpen(ByVal Wb As Workbook),
how do I get it to run? Does it not run automatically when the workbook is
opened? I am confused by the "Dim X as a New EvenClassModule" and the Sub
InitializeApp(). If you understand where my confusion lies, please clarify.

All I want to do at this point is set a global variable within the Private
Sub App_WorkbookOpen(ByVal Wb As Workbook) that can be changed by a "normal"
routine from within a normal (not class) vba module. What do I need to do?

Thank you.

Kevin

 

[Chip Pearson]

Kevin,

Once I have created the Private Sub App_WorkbookOpen(ByVal Wb
As Workbook), how do I get it to run? Does it not run
automatically when the workbook is opened?

Yes, it will run automatically when any workbook is open or
created.

I am confused by the "Dim X as a New EvenClassModule" and the
Sub InitializeApp().

A class module is a template for an object. It doesn't by itself
allocate any memory or create an object. Only when you create an
instance of it (called "instantiating") will memory be allocated
an the object come into existence.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Kevin H. Stecyk]

Hi Chip,

Thank you very much for responding to my question. As I mentioned, I don't
understand class modules very well.

From your response, I get the impression that I need to ""Dim X as a New
EvenClassModule" and the Sub InitializeApp()". I am testing that statement
to see whether you agree or not. If I don't do that, then what happens?
For example, let's say I don't "instantiate" the class module, will "Create
a Private Sub App_WorkbookOpen(ByVal Wb As Workbook)" still function? Can I
still set my global variable?

This goes in the normal (non-class) module, correct? What calls "Sub
InitializeApp()"?

I am not sure if my questions are making sense or not.

Thank you for patience and your help.

Best regards,
Kevin






Chip Pearson wrote...

 

[Bob Phillips]

From your response, I get the impression that I need to ""Dim X as a New
EvenClassModule" and the Sub InitializeApp()". I am testing that statement
to see whether you agree or not. If I don't do that, then what happens?

Without this, nothing happens, because as Chip says, all you have is an
object template, you will not have created an instance of that oject.

For example, let's say I don't "instantiate" the class module, will "Create
a Private Sub App_WorkbookOpen(ByVal Wb As Workbook)" still function? Can I
still set my global variable?

If you don't Dim X, this statement will error.

This goes in the normal (non-class) module, correct? What calls "Sub
InitializeApp()"?

I tend to do it in a normal workbook_Open event for that workbook. This is
my example post on App Events.


Firstly, all of this code goes in the designated workbook.



'========================================Insert a class module, rename it to
'clsAppEvents', with this codeOption Explicit


Public WithEvents App As Application


Private Sub App_WorkbookOpen(ByVal Wb As Workbook)

msgbox Wb.Name

End Sub


'========================================In ThisWorkbook code module, add
this event codeDim AppClass As New clsAppEvents


Private Sub Workbook_Open()


Set AppClass.App = Application


End Sub



Either save the workbook, close it and re-open it to initaite application
events, or just run the Workbook_Open code manually. From then on, each
workbook opened will display the name.


--

 

[Kevin H. Stecyk]

Hi Bob,

Sorry to be such a bother. I tried following your instructions explicitly.
However, I have done something wrong. I will copy and paste directly from
my code.

Class Module: clsAppEvents (copied and pasted)

Option Explicit

Public WithEvents App As Application


Private Sub App_WorkbookOpen(ByVal Wb As Workbook)

MsgBox Wb.Name

End Sub

--------------

Now the Module1 is simply:

Option Explicit

Dim AppClass As New clsAppEvents



Private Sub Workbook_Open()


Set AppClass.App = Application


End Sub


When I close and reopen, I get nothing. When I single step through Private
Sub Workbook_Open(), I get nothing. No errors or anything.

Can you see where I went wrong?

Best regards,
Kevin

 

[Dave Peterson]

Try putting:

Option Explicit
Dim AppClass As New clsAppEvents
Private Sub Workbook_Open()
Set AppClass.App = Application
End Sub

Not in a general module (module1), but in the ThisWorkbook Module.

If you want to read more about application events, visit Chip Pearson's site:
http://www.cpearson.com/excel/AppEvent.htm

Chip also has a sample workbook there that you can download and test out.

 

[Kevin H. Stecyk]

Dave, Bob, and Chip,

Thank you Dave for spotting my error! Thank you everyone for your patient
assistance.

I am going to have a look at Chip's information.

Best regards,
Kevin





Dave Peterson...

 

[Kevin H. Stecyk]

Dave Peterson wrote

Try putting:

Option Explicit
Dim AppClass As New clsAppEvents
Private Sub Workbook_Open()
Set AppClass.App = Application
End Sub

Not in a general module (module1), but in the ThisWorkbook Module.

If you want to read more about application events, visit Chip Pearson's
site:
http://www.cpearson.com/excel/AppEvent.htm

Chip also has a sample workbook there that you can download and test out.

Dave,

Just a follow up question to assist with my understanding.

In the ThisWorkbook Module, we placed a Work_Open() event handler which
instantiated the Class Module called clsAppEvents. In the clsAppEvents, we
have another workbookopen event handler called App_WorkbookOpen(ByVal Wb As
Workbook). My question is, why not just use the Work_Open() event handler
in the ThisWorkbook Module? Why use two event handlers that act upon the
workbook being opened?

I did read Chip's site that you referenced. That was helpful. I am going
to soak on it over night and then follow up with some questions tomorrow.

Again, thank you for your patience and assistance.

Best regards,
Kevin

 

[keepITcool]

What Chip doesn't mention and what I find a lot easier...

you dont need a separate class module..
Thisworkbook IS a class module so you can keep all your code
(and the withevents application variable) there.

'thisworkbook code module...
Option Explicit

Private WithEvents XlsEvents As Application

Private Sub Workbook_Open()
Set XlsEvents = Application
End Sub

Private Sub XlsEvents_WorkbookOpen(ByVal Wb As Workbook)
MsgBox "You just opened " & Wb.Name
End Sub




--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Dave Peterson wrote :

 

[Tom Ogilvy]

If you want to set the variable for only a single workbook, then you don't
need application level events - just use the workbook_Open event as you
stated.

 

[Kevin H. Stecyk]

Hi Tom,

Thank you for answering my question. That's interesting. So you only need
to use the class modules for event handlers when you are working with
external workbooks. That helps to reinforce Chip's notes.

Again, thank you.

Best regards,
Kevin


Tom Ogilvy wrote...

 

[Tom Ogilvy]

ThisWorkbook is a class module, but it is already set up for you. You only
need explicitly instantiate application level events if you want to have
events that are independent of the activities associated with an individual
workbook or workhseet. In other words if I wanted to do something for any
workbook that is opened and I don't want to put the code in each workbook
then I would use application level events.

The common way show to instantiate application level events is with a class
module, but KeepItCool pointed out that this could be done within the
thisworkbook module of workbook. Even if using a separate class module, it
would still have to reside in a single workbook.

so your statement:

So you only need
to use the class modules for event handlers when you are working with
external workbooks.

Is accurate.

--
Regards,
Tom Ogilvy

Hi Tom,

Thank you for answering my question. That's interesting. So you only need
to use the class modules for event handlers when you are working with
external workbooks. That helps to reinforce Chip's notes.

Again, thank you.

Best regards,
Kevin


Tom Ogilvy wrote...

 

[Kevin H. Stecyk]

Hi Tom,

Let me put this in my own words to see if I have completely understood.

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.

Best regards,
Kevin

=================

Written by Tom Ogilvy....


ThisWorkbook is a class module, but it is already set up for you. You only
need explicitly instantiate application level events if you want to have
events that are independent of the activities associated with an individual
workbook or workhseet. In other words if I wanted to do something for any
workbook that is opened and I don't want to put the code in each workbook
then I would use application level events.

The common way show to instantiate application level events is with a class
module, but KeepItCool pointed out that this could be done within the
thisworkbook module of workbook. Even if using a separate class module, it
would still have to reside in a single workbook.

so your statement:

So you only need
to use the class modules for event handlers when you are working with
external workbooks.

Is accurate.

 

[Tom Ogilvy]

See my answer to your later posting as new thread.