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
|