Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Further Clarification with Event Handlers & Class Modules
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Further Clarification with Event Handlers & Class Modules
In Line:
"Kevin H. Stecyk" wrote in message ... 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. 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 --------------- I don't claim to be an expert on class modules - but since no one else jumped in I made an attempt. Perhaps this will stimulate further answers if there is a better way to explain it. -- Regards, Tom Ogilvy |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Further Clarification with Event Handlers & Class Modules
Tom,
A huge and sincere thank you for taking the time and effort to respond to my lengthy questions. Best regards, Kevin |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Grouping Event handlers | Excel Programming | |||
Class Modules | Excel Programming | |||
Problems with event handlers | Excel Programming | |||
Class Modules vs Modules | Excel Programming | |||
Event - RaiseEvents Within Class Modules | Excel Programming |