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

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

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

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

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

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

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

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

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

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

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