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

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" wrote in message
...
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

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?

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

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...
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" wrote...
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

"Kevin H. Stecyk" wrote in message
...

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?

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

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.


--

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

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" wrote:

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