Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application Event Procedure <Falling Over
Hi
I am using the software from (the peerless) Chip Pearson's site that helps with Application Event programming. The event I have is as follows: Private Sub App_WorkbookOpen(ByVal Wb As Excel.Workbook) Etc etc End Sub Some time ago someone on the ng helped me by supplying this code to deal with the situation when the VB Project has <fallen over e.g. if a Run-Reset has happened etc or a runtime error has been finished by the user pressing "End" on the dialog box. Sub Reset_EnableEvents() Set AppClass.App = Application End Sub Is there a property in VB that can identify if that reset has occurred i.e. can one determine programmatically if it is necessary to run the procedure Reset_EnableEvents? Thanks Tim |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application Event Procedure <Falling Over
Make a public variable, in a normal module and call it for example:
bWBOpened, so: Public bWBOpened as Boolean In the Workbook Open event, so in: Private Sub Workbook_Open() put this: bWBOpened = True Now test for this variable, so: If bWBOpened = False Then Reset_EnableEvents End If RBS "Tim Childs" wrote in message ... Hi I am using the software from (the peerless) Chip Pearson's site that helps with Application Event programming. The event I have is as follows: Private Sub App_WorkbookOpen(ByVal Wb As Excel.Workbook) Etc etc End Sub Some time ago someone on the ng helped me by supplying this code to deal with the situation when the VB Project has <fallen over e.g. if a Run-Reset has happened etc or a runtime error has been finished by the user pressing "End" on the dialog box. Sub Reset_EnableEvents() Set AppClass.App = Application End Sub Is there a property in VB that can identify if that reset has occurred i.e. can one determine programmatically if it is necessary to run the procedure Reset_EnableEvents? Thanks Tim |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application Event Procedure <Falling Over
Hi Tim,
If I follow you are trapping application level events and want to cater for the possibility the class holding the app ref has been destroyed, eg by user pressing the reset button. You could set a boolean flag, which if false would indicate your variables have been destroyed. However you might just as well amend your routine - Sub Reset_EnableEvents() If AppClass is nothing then set AppClass = new ClassName Set AppClass.App = Application end if End Sub I can't think of anything that will automatically tell you that your AppClass no longer exists, so you would need to check periodically. In theory you could trap the VBE Reset button-click event but there are other ways your ref could be destroyed. Perhaps look into the OnTime method with Schedule to run the amended Reset_EnableEvents routine (don't forget to clear it when done). Regards, Peter T "Tim Childs" wrote in message ... Hi I am using the software from (the peerless) Chip Pearson's site that helps with Application Event programming. The event I have is as follows: Private Sub App_WorkbookOpen(ByVal Wb As Excel.Workbook) Etc etc End Sub Some time ago someone on the ng helped me by supplying this code to deal with the situation when the VB Project has <fallen over e.g. if a Run-Reset has happened etc or a runtime error has been finished by the user pressing "End" on the dialog box. Sub Reset_EnableEvents() Set AppClass.App = Application End Sub Is there a property in VB that can identify if that reset has occurred i.e. can one determine programmatically if it is necessary to run the procedure Reset_EnableEvents? Thanks Tim |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application Event Procedure <Falling Over
Hi
thanks, I will try that Tim "RB Smissaert" wrote in message ... Make a public variable, in a normal module and call it for example: bWBOpened, so: Public bWBOpened as Boolean In the Workbook Open event, so in: Private Sub Workbook_Open() put this: bWBOpened = True Now test for this variable, so: If bWBOpened = False Then Reset_EnableEvents End If RBS "Tim Childs" wrote in message ... Hi I am using the software from (the peerless) Chip Pearson's site that helps with Application Event programming. The event I have is as follows: Private Sub App_WorkbookOpen(ByVal Wb As Excel.Workbook) Etc etc End Sub Some time ago someone on the ng helped me by supplying this code to deal with the situation when the VB Project has <fallen over e.g. if a Run-Reset has happened etc or a runtime error has been finished by the user pressing "End" on the dialog box. Sub Reset_EnableEvents() Set AppClass.App = Application End Sub Is there a property in VB that can identify if that reset has occurred i.e. can one determine programmatically if it is necessary to run the procedure Reset_EnableEvents? Thanks Tim |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application Event Procedure <Falling Over
Hi Peter
Thanks for response. Can someone explain what the suggested change to the procedure Sub Reset_EnableEvents() If AppClass is nothing then set AppClass = new ClassName Set AppClass.App = Application end if End Sub is doing because I dont yet understand. Thanks Tim "Peter T" <peter_t@discussions wrote in message ... Hi Tim, If I follow you are trapping application level events and want to cater for the possibility the class holding the app ref has been destroyed, eg by user pressing the reset button. You could set a boolean flag, which if false would indicate your variables have been destroyed. However you might just as well amend your routine - Sub Reset_EnableEvents() If AppClass is nothing then set AppClass = new ClassName Set AppClass.App = Application end if End Sub I can't think of anything that will automatically tell you that your AppClass no longer exists, so you would need to check periodically. In theory you could trap the VBE Reset button-click event but there are other ways your ref could be destroyed. Perhaps look into the OnTime method with Schedule to run the amended Reset_EnableEvents routine (don't forget to clear it when done). Regards, Peter T |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application Event Procedure <Falling Over
If AppClass is Nothing then ...
If your previously created class, that holds the ref to Excel (AppClass.App = Application) and handles application 'withevents', has unexpectedly become nothing, ie it's been destroyed, you can go on to recreate or 'reset' it. You could of course use RBS's suggestion to check 'If Not bWBOpened then...' anywhere in your code and go on to reset your objects, and perhaps any other global variables. If you are going to check your settings periodically with the OnTime method you will need to call some routine, which might just as well be your existing Sub Reset_EnableEvents(). But in the routine only reset if necessary. Regards, Peter T "Tim Childs" wrote in message ... Hi Peter Thanks for response. Can someone explain what the suggested change to the procedure Sub Reset_EnableEvents() If AppClass is nothing then set AppClass = new ClassName Set AppClass.App = Application end if End Sub is doing because I dont yet understand. Thanks Tim "Peter T" <peter_t@discussions wrote in message ... Hi Tim, If I follow you are trapping application level events and want to cater for the possibility the class holding the app ref has been destroyed, eg by user pressing the reset button. You could set a boolean flag, which if false would indicate your variables have been destroyed. However you might just as well amend your routine - Sub Reset_EnableEvents() If AppClass is nothing then set AppClass = new ClassName Set AppClass.App = Application end if End Sub I can't think of anything that will automatically tell you that your AppClass no longer exists, so you would need to check periodically. In theory you could trap the VBE Reset button-click event but there are other ways your ref could be destroyed. Perhaps look into the OnTime method with Schedule to run the amended Reset_EnableEvents routine (don't forget to clear it when done). Regards, Peter T |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application Event Procedure <Falling Over
Hi Peter
Many thanks for response I will try to assimilate in the programming. The underlying problem is that sometimes the program simply does not start up properly when the file containing it is opened. The problem is intermittent so I was very interested in being able to test as directly as possible, rather than by proxy e.g. the proxy would be testing the value of a new boolean flag variable, although I will incorporate that as well. Best wishes Tim "Peter T" <peter_t@discussions wrote in message ... If AppClass is Nothing then ... If your previously created class, that holds the ref to Excel (AppClass.App = Application) and handles application 'withevents', has unexpectedly become nothing, ie it's been destroyed, you can go on to recreate or 'reset' it. You could of course use RBS's suggestion to check 'If Not bWBOpened then...' anywhere in your code and go on to reset your objects, and perhaps any other global variables. If you are going to check your settings periodically with the OnTime method you will need to call some routine, which might just as well be your existing Sub Reset_EnableEvents(). But in the routine only reset if necessary. Regards, Peter T "Tim Childs" wrote in message ... Hi Peter Thanks for response. Can someone explain what the suggested change to the procedure Sub Reset_EnableEvents() If AppClass is nothing then set AppClass = new ClassName Set AppClass.App = Application end if End Sub is doing because I dont yet understand. Thanks Tim "Peter T" <peter_t@discussions wrote in message ... Hi Tim, If I follow you are trapping application level events and want to cater for the possibility the class holding the app ref has been destroyed, eg by user pressing the reset button. You could set a boolean flag, which if false would indicate your variables have been destroyed. However you might just as well amend your routine - Sub Reset_EnableEvents() If AppClass is nothing then set AppClass = new ClassName Set AppClass.App = Application end if End Sub I can't think of anything that will automatically tell you that your AppClass no longer exists, so you would need to check periodically. In theory you could trap the VBE Reset button-click event but there are other ways your ref could be destroyed. Perhaps look into the OnTime method with Schedule to run the amended Reset_EnableEvents routine (don't forget to clear it when done). Regards, Peter T |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application Event Procedure <Falling Over
Hi
I could not get the code below to compile: Sub Reset_EnableEvents_NEW() If AppClass Is Nothing Then Set AppClass = New ClassName Set AppClass.App = Application End If End Sub "New ClassName" above produced the compile error: "User-defined type not defined" I had Option Explicit turned on in the module Any help welcome. THANKS Tim "Tim Childs" wrote in message ... Hi Peter Many thanks for response I will try to assimilate in the programming. The underlying problem is that sometimes the program simply does not start up properly when the file containing it is opened. The problem is intermittent so I was very interested in being able to test as directly as possible, rather than by proxy e.g. the proxy would be testing the value of a new boolean flag variable, although I will incorporate that as well. Best wishes Tim "Peter T" <peter_t@discussions wrote in message ... If AppClass is Nothing then ... If your previously created class, that holds the ref to Excel (AppClass.App = Application) and handles application 'withevents', has unexpectedly become nothing, ie it's been destroyed, you can go on to recreate or 'reset' it. You could of course use RBS's suggestion to check 'If Not bWBOpened then...' anywhere in your code and go on to reset your objects, and perhaps any other global variables. If you are going to check your settings periodically with the OnTime method you will need to call some routine, which might just as well be your existing Sub Reset_EnableEvents(). But in the routine only reset if necessary. Regards, Peter T "Tim Childs" wrote in message ... Hi Peter Thanks for response. Can someone explain what the suggested change to the procedure Sub Reset_EnableEvents() If AppClass is nothing then set AppClass = new ClassName Set AppClass.App = Application end if End Sub is doing because I dont yet understand. Thanks Tim |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application Event Procedure <Falling Over
Hi Tim,
I understood from the onset that you are trapping application level events, so you would have a class module named [say] ClassName, in the class module something like this Public withevents App as Excel.application also, at module level in a normal module you would maintain a reference to your class named [say] ClassName Dim AppClass as ClassName In my sample code. not knowing what you named your class I wrote at random 'ClassName'. You will need to change this to whatever you named your class, perhaps it's still named 'Class1'. Regards, Peter T "Tim Childs" wrote in message ... Hi I could not get the code below to compile: Sub Reset_EnableEvents_NEW() If AppClass Is Nothing Then Set AppClass = New ClassName Set AppClass.App = Application End If End Sub "New ClassName" above produced the compile error: "User-defined type not defined" I had Option Explicit turned on in the module Any help welcome. THANKS Tim "Tim Childs" wrote in message ... Hi Peter Many thanks for response I will try to assimilate in the programming. The underlying problem is that sometimes the program simply does not start up properly when the file containing it is opened. The problem is intermittent so I was very interested in being able to test as directly as possible, rather than by proxy e.g. the proxy would be testing the value of a new boolean flag variable, although I will incorporate that as well. Best wishes Tim "Peter T" <peter_t@discussions wrote in message ... If AppClass is Nothing then ... If your previously created class, that holds the ref to Excel (AppClass.App = Application) and handles application 'withevents', has unexpectedly become nothing, ie it's been destroyed, you can go on to recreate or 'reset' it. You could of course use RBS's suggestion to check 'If Not bWBOpened then...' anywhere in your code and go on to reset your objects, and perhaps any other global variables. If you are going to check your settings periodically with the OnTime method you will need to call some routine, which might just as well be your existing Sub Reset_EnableEvents(). But in the routine only reset if necessary. Regards, Peter T "Tim Childs" wrote in message ... Hi Peter Thanks for response. Can someone explain what the suggested change to the procedure Sub Reset_EnableEvents() If AppClass is nothing then set AppClass = new ClassName Set AppClass.App = Application end if End Sub is doing because I dont yet understand. Thanks Tim |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application Event Procedure <Falling Over
Hi Peter
I seem to get it to work when I change the code as follows: Sub Reset_EnableEvents_NEW() 'If AppClass Is Nothing Then ' 'Set AppClass = New ClassName ' Set AppClass = New EventClass ' Set AppClass.App = Application 'End If If AppClass.App Is Nothing Then 'MsgBox "found it" Set AppClass.App = Application End If End Sub When I just used: If AppClass Is Nothing Then ... End If I did not trap the problem, I seemed to have to use: If AppClass.App Is Nothing to trap the <falling over Any explanation of this would be welcome. The help is much appreciated Tim "Peter T" <peter_t@discussions wrote in message ... Hi Tim, I understood from the onset that you are trapping application level events, so you would have a class module named [say] ClassName, in the class module something like this Public withevents App as Excel.application also, at module level in a normal module you would maintain a reference to your class named [say] ClassName Dim AppClass as ClassName In my sample code. not knowing what you named your class I wrote at random 'ClassName'. You will need to change this to whatever you named your class, perhaps it's still named 'Class1'. Regards, Peter T "Tim Childs" wrote in message ... Hi I could not get the code below to compile: Sub Reset_EnableEvents_NEW() If AppClass Is Nothing Then Set AppClass = New ClassName Set AppClass.App = Application End If End Sub "New ClassName" above produced the compile error: "User-defined type not defined" I had Option Explicit turned on in the module Any help welcome. THANKS Tim "Tim Childs" wrote in message ... Hi Peter Many thanks for response I will try to assimilate in the programming. The underlying problem is that sometimes the program simply does not start up properly when the file containing it is opened. The problem is intermittent so I was very interested in being able to test as directly as possible, rather than by proxy e.g. the proxy would be testing the value of a new boolean flag variable, although I will incorporate that as well. Best wishes Tim "Peter T" <peter_t@discussions wrote in message ... If AppClass is Nothing then ... If your previously created class, that holds the ref to Excel (AppClass.App = Application) and handles application 'withevents', has unexpectedly become nothing, ie it's been destroyed, you can go on to recreate or 'reset' it. You could of course use RBS's suggestion to check 'If Not bWBOpened then...' anywhere in your code and go on to reset your objects, and perhaps any other global variables. If you are going to check your settings periodically with the OnTime method you will need to call some routine, which might just as well be your existing Sub Reset_EnableEvents(). But in the routine only reset if necessary. Regards, Peter T |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application Event Procedure <Falling Over
Hi Tim,
When I just used: If AppClass Is Nothing Then .. End If I did not trap the problem, I seemed to have to use: If AppClass.App Is Nothing Any explanation of this would be welcome. I'm guessing you declared AppClass like this - Dim AppClass as New ClassName If that's how you declared it, the first time AppClass is 'called' a new instance of the class is automatically created. So when testing it will inevitably always be 'Not Nothing'. So continue with your test for AppClass.App If you are frequently referring to AppClass it's more efficient (in practice might not notice) to do this '' top of module Dim AppClass as ClassName ' or Public ... ' in the routine Set AppClass = New ClassName ' etc Generally, though not always, I prefer not to declare 'As New' irrespective of any marginal efficiency gain. Regards, Peter T "Tim Childs" wrote in message ... Hi Peter I seem to get it to work when I change the code as follows: Sub Reset_EnableEvents_NEW() 'If AppClass Is Nothing Then ' 'Set AppClass = New ClassName ' Set AppClass = New EventClass ' Set AppClass.App = Application 'End If If AppClass.App Is Nothing Then 'MsgBox "found it" Set AppClass.App = Application End If End Sub When I just used: If AppClass Is Nothing Then .. End If I did not trap the problem, I seemed to have to use: If AppClass.App Is Nothing to trap the <falling over Any explanation of this would be welcome. The help is much appreciated Tim "Peter T" <peter_t@discussions wrote in message ... Hi Tim, I understood from the onset that you are trapping application level events, so you would have a class module named [say] ClassName, in the class module something like this Public withevents App as Excel.application also, at module level in a normal module you would maintain a reference to your class named [say] ClassName Dim AppClass as ClassName In my sample code. not knowing what you named your class I wrote at random 'ClassName'. You will need to change this to whatever you named your class, perhaps it's still named 'Class1'. Regards, Peter T "Tim Childs" wrote in message ... Hi I could not get the code below to compile: Sub Reset_EnableEvents_NEW() If AppClass Is Nothing Then Set AppClass = New ClassName Set AppClass.App = Application End If End Sub "New ClassName" above produced the compile error: "User-defined type not defined" I had Option Explicit turned on in the module Any help welcome. THANKS Tim "Tim Childs" wrote in message ... Hi Peter Many thanks for response I will try to assimilate in the programming. The underlying problem is that sometimes the program simply does not start up properly when the file containing it is opened. The problem is intermittent so I was very interested in being able to test as directly as possible, rather than by proxy e.g. the proxy would be testing the value of a new boolean flag variable, although I will incorporate that as well. Best wishes Tim "Peter T" <peter_t@discussions wrote in message ... If AppClass is Nothing then ... If your previously created class, that holds the ref to Excel (AppClass.App = Application) and handles application 'withevents', has unexpectedly become nothing, ie it's been destroyed, you can go on to recreate or 'reset' it. You could of course use RBS's suggestion to check 'If Not bWBOpened then...' anywhere in your code and go on to reset your objects, and perhaps any other global variables. If you are going to check your settings periodically with the OnTime method you will need to call some routine, which might just as well be your existing Sub Reset_EnableEvents(). But in the routine only reset if necessary. Regards, Peter T |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Event Procedure doesn't run | Excel Programming | |||
Call procedure using Application.Run | Excel Programming | |||
Event Procedure | Excel Programming | |||
Event Procedure again | Excel Programming | |||
OnTime event not firing in Workbook_Open event procedure | Excel Programming |