Class stops working...

Some time ago on Dick Kusleikas Daily Dose of Excel (dicks-glob.com) a
utility for putting additional stats on the status bar was posted. With that
idea in mind, I created a Class Module to show the sum and count of selected
ranges on the status bar so that you don't have to continuously switch
between them.
To keep the status bar updated, in the class module, I use€¦
App_Stats_SheetActivate
App_Stats_SheetSelectionChange
App_Stats_WorkbookBeforeSave

The problem is:
Every so often, the Class Module stops working and the status bar no longer
updates. I cant pin down whats going on when it stops working because I
only look at the status bar when I need the sum/count information€¦and then
notice that its not giving me the information. I have another Class Module
that also depends on specific events such as SheetActivate that also appears
to stop working at the same time.

Does anyone have any idea why a class module might stop working and yet
macros in Standard Modules continue to work? And, of course, do you know how
to get around this issue? I dont know if Im going down a blind alley but
it appears that macros that I specifically call work fine while macros
dependent on an event stop.

Any help would be greatly appreciated.

Thanks in advance.
Sincerely,
Gary Brown

Gary Brown wrote:


The problem is:
Every so often, the Class Module stops working and the status bar no
longer updates. I can't pin down what's going on when it stops
working because I only look at the status bar when I need the
sum/count information.and then notice that it's not giving me the
information. I have another Class Module that also depends on
specific events such as SheetActivate that also appears to stop
working at the same time.

Do you have another routine that you run occasionally that features an End
statement? This would kill the global variable that is holding the instance
of your class, even if it happens in a completely unconnected project. End
is evil. Don't use it folks.

Rob

Gary

I like dicks-glob.com, but I named it dicks-blog.com. :)

The problem is that the variable that holds the class has gone out of scope.
That usually happens to me when I change something in the code - anywhere in
the project not just in the class. I'm sure if that was the case for you,
you would have made that connection. There are other reasons that a global
variable can go out of scope, like the one Robert suggested.

You might try putting a message box in the classe's Terminate event. If it
is terminating normally, you can note the message box and possible determine
what's happening right before it terminates.

A lot of times when I need to be sure that class is instantiated, I use
something like

If gMyClass Is Nothing Then
Set gMyClass = New MyClass
End If

at the start of a procedure to make sure nothing has happened to it. You
just can't do this when you're using a class solely for events. If you knew
when the events were going to happen, you wouldn't need the class. You can,
however, do this kind of thing periodically in connection with something
else that you may have going on. That's not as good of a solution as
finding what's actually causing the variable to go out of scope though.

--
Dick Kusleika
Excel MVP
Daily Dose of Excel
www.dicks-blog.com

Gary Brown wrote:
Class stops working...

Some time ago on Dick Kusleika's Daily Dose of Excel
(dicks-glob.com) a utility for putting additional stats on the status
bar was posted. With that idea in mind, I created a Class Module to
show the sum and count of selected ranges on the status bar so that
you don't have to continuously switch between them.
To keep the status bar updated, in the class module, I use.
App_Stats_SheetActivate
App_Stats_SheetSelectionChange
App_Stats_WorkbookBeforeSave

The problem is:
Every so often, the Class Module stops working and the status bar no
longer updates. I can't pin down what's going on when it stops
working because I only look at the status bar when I need the
sum/count information.and then notice that it's not giving me the
information. I have another Class Module that also depends on
specific events such as SheetActivate that also appears to stop
working at the same time.

Does anyone have any idea why a class module might stop working and
yet macros in Standard Modules continue to work? And, of course, do
you know how to get around this issue? I don't know if I'm going
down a blind alley but it appears that macros that I specifically
call work fine while macros dependent on an event stop.

Any help would be greatly appreciated.

Thanks in advance.
Sincerely,
Gary Brown

In article ,
"Dick Kusleika" wrote:

I like dicks-glob.com, but I named it dicks-blog.com. :)

Much more distinctive. Consider changing, Dick?

Thanks Dick.
Sorry about the mis-type (sub-conscious? :O).
And thanks a LOT for the advice.
I visit your site ~virtually~ every day.
--
Gary Brown



"Dick Kusleika" wrote:

Gary

I like dicks-glob.com, but I named it dicks-blog.com. :)

The problem is that the variable that holds the class has gone out of scope.
That usually happens to me when I change something in the code - anywhere in
the project not just in the class. I'm sure if that was the case for you,
you would have made that connection. There are other reasons that a global
variable can go out of scope, like the one Robert suggested.

You might try putting a message box in the classe's Terminate event. If it
is terminating normally, you can note the message box and possible determine
what's happening right before it terminates.

A lot of times when I need to be sure that class is instantiated, I use
something like

If gMyClass Is Nothing Then
Set gMyClass = New MyClass
End If

at the start of a procedure to make sure nothing has happened to it. You
just can't do this when you're using a class solely for events. If you knew
when the events were going to happen, you wouldn't need the class. You can,
however, do this kind of thing periodically in connection with something
else that you may have going on. That's not as good of a solution as
finding what's actually causing the variable to go out of scope though.

--
Dick Kusleika
Excel MVP
Daily Dose of Excel
www.dicks-blog.com

Gary Brown wrote:
Class stops working...

Some time ago on Dick Kusleika's Daily Dose of Excel
(dicks-glob.com) a utility for putting additional stats on the status
bar was posted. With that idea in mind, I created a Class Module to
show the sum and count of selected ranges on the status bar so that
you don't have to continuously switch between them.
To keep the status bar updated, in the class module, I use.
App_Stats_SheetActivate
App_Stats_SheetSelectionChange
App_Stats_WorkbookBeforeSave

The problem is:
Every so often, the Class Module stops working and the status bar no
longer updates. I can't pin down what's going on when it stops
working because I only look at the status bar when I need the
sum/count information.and then notice that it's not giving me the
information. I have another Class Module that also depends on
specific events such as SheetActivate that also appears to stop
working at the same time.

Does anyone have any idea why a class module might stop working and
yet macros in Standard Modules continue to work? And, of course, do
you know how to get around this issue? I don't know if I'm going
down a blind alley but it appears that macros that I specifically
call work fine while macros dependent on an event stop.

Any help would be greatly appreciated.

Thanks in advance.
Sincerely,
Gary Brown

Thanks Robert.
I know that I don't use the 'End' statement but now that you've put the bug
in my ear, I'm currently working at a client that uses the Web-based version
of Discoverer for Oracle. It's always putting hidden Excel V4 macros for
formatting in it's downloads. I wonder???
I appreciate your time.
Thanks again.
--
Gary Brown



"Robert Bruce" wrote:

Gary Brown wrote:


The problem is:
Every so often, the Class Module stops working and the status bar no
longer updates. I can't pin down what's going on when it stops
working because I only look at the status bar when I need the
sum/count information.and then notice that it's not giving me the
information. I have another Class Module that also depends on
specific events such as SheetActivate that also appears to stop
working at the same time.

Do you have another routine that you run occasionally that features an End
statement? This would kill the global variable that is holding the instance
of your class, even if it happens in a completely unconnected project. End
is evil. Don't use it folks.

Rob