MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Macros randomly stopping? Surely not.

  Asked By: Rose    Date: Dec 23    Category: MS Office    Views: 1378

I finished my excel app, and tested it quickly before showing it.
(I said "quickly." <g>)

I set up a display monitor, started Excel, brought up the sheet. It was
working then, caused I tried it.

After I explained why I wrote the program, I proceeded to show it. The
macros would not run. For some reason, after I left it alone for a few
minutes, the ability to run them was turned off. (No one touched it.)
Rebooting solved the problem, but my question is can I force the macros
not to become disabled as long as the sheet workbook is open?



6 Answers Found

Answer #1    Answered By: Bien Nguyen     Answered On: Dec 23

there must be something else going on here.
I don't think there's any way to disable macros  based on "time".
Mainly because when excel  is idle, there isn't any macros RUNNING.
so therefore, it can't be monitoring the time.

Do you happen to use any of the Application.EnableEvents = True/False ??
Perhaps you somehow left  the state as "false"...
Or your macro is left in "debug".. in which case, a new macro won't run.

there has to be something like this going on...
Time to do some investigating!

Answer #2    Answered By: Freddie Evans     Answered On: Dec 23

Yes, investigating is needed. I one theory. If (and a somewhat big IF,
but I could have done it, I guess.) Suppose I save the sheet  while
debug is open  and running. Is it _/still/_ running in debug mode? Has to
be a way to turn that off, if that's the case.

And, another question, though I may have this one resolved tonight. IF
the macros  affect Cell O10 (oh, ten), and I have a conditional format
for the same cell of O10, which one "overrides" the other? When I was in
college, my professor told us whichever was _*last edited*_ took
priority over the other, but now I question  that. I am starting to think
the conditional format overrides any formats set  by macros. (In this
case, I am just removing the conditional formats.)

Answer #3    Answered By: Jonah Brown     Answered On: Dec 23

Forgive me for interrupting your conversation, but I was remembering
something, or infact more than one thing, similar to your
One is . . . if certain run-time errors fire, they stop a
subroutine from continuing, especially one which had been running
from a "call <such-an-such <subname>"
So, the other day, I tried to run  a sub, and hit a runtime error
on the sub which was called to run from the main sub which I had
been working on at the time, and since it was a "application level"
error, it skipped all the rest of the lines I had written;
thus, it appeared to "randomly skip" some major steps in the code.

The other time, for some reason, which you experts can perhaps
point out, those "ThisWorkbook" subroutines, which referenced code
containing a runtime error, just 'skipped' to the next line in the
code, effectively discontinuing the subroutine which might be
necessary for the process indicated to work. . . . some of those
were when I had put in error traps and other times, if there was an
error trap or a "on error resume next", I was unable to identify it.

Each of those might be part of the problem, I would think . . .
if I'm missing something, please teach me, since I'm certainly
learning as I go . . .

Answer #4    Answered By: Boyce Fischer     Answered On: Dec 23

Sorry, after reading what I had written, I wasn't clear . . . when
those things happen, events might have been shut off entirely for
processing reasons, and the code to reactivate them might have been
skipped because of the error, and stops could have been written in,
so the code would just stop working . . .and it would appear
as "random".

Answer #5    Answered By: Stacy Cunningham     Answered On: Dec 23

There are two different modes - debug and design - that would have similar
effects. If you save (and close) while either is active, they will be gone
when you start the sheet  next time, and it will be back to normal. (Excel
2003 and earlier, anyway.)


There is no "overrides" between conditional formatting and macros. The fact
that a macro impacts a cell is not known to Excel until the macro is
actually running and actually changes that cell. Conditional formatting
_is_ associated with a cell and is always going to be active. I.e. if a
macro changes a cell (and assuming display  updating is not turned off), then
any conditional formatting on that cell will influence its display.

Of course, a macro can remove or replace the conditional formatting
instructions for a cell - just as it can do anything else. In that case,
the old conditional formatting instructions would be gone.


What your professor said sounds incorrect. But ...

> I am starting to think the conditional format overrides any formats set  by
> macros.

... is also incorrect.

A cell can be thought of as having two distinct attributes: its value and
its format specification. The value decides what is displayed, and the
format specification decides what it looks like. The format specification
always includes "normal" formatting and sometimes includes conditional
formatting. (This is very simplistic - there are lots of attributes
really - but this is the principle.)

To display a cell, you give it a format specification and a value, and
_Excel_ displays it. Your macro or your typing doesn't display the cell,
Excel does - based on the current value and format specification.

BUT. A macro can put a value into a cell, and it can put a format
specification into a cell. These overwrite whatever was there before. And
Excel will display the cell again, according to its new attributes.

So ... there is no concept of overrides or precedence. A cell is displayed
according to its current attributes only. Conditional formatting on a cell
will be active until the conditional formatting specification itself is
removed or replaced (either by a macro or by you).

Answer #6    Answered By: Jimmie Ramirez     Answered On: Dec 23

I think here that you need to consider the definition of "last edited".
A macro does not run  continually. If it did, a user would never be able
to interrupt long  enough to enter anything into a cell!
Macros are an "on-demand" kind of thing. Even the "Event" macros
are on-demand, it's just you've pre-defined or automated the "demand".

Conditional formatting, however acts ALMOST like a "Change" event.
(almost, but not quite, and the analogy fall apart very quickly, but still...)
So, if you have a macro that changes the color of a cell, the conditional
formatting says: "Look, the cell changed, what should I do?" and changes
it back as defined by the conditional format. Basically, the conditional format
acts as if it is applied almost continuously. screen refreshes, cell changes,

so.. if your definition of "last edited" means the last action that takes
place.. well yeah!
If your definition of "overrides" means something like "takes precedence"..
that's probably true too... just for different reasons.

Kind-of like having two macros. One changes the cell to red, the other to blue.
the one changing it to red is called by just about every other macro, including
screen refreshes and selectionchange events. When you run the macro changing
the cell to blue, it stays blue until ANYTHING happens, then it changes back to
So, in this case, the one changing it to red "overrides" any other change... and
it is the last (or most recent) action.

Another simple fact is that the "format set  my macros" is still taking place.
If you removed the conditional formatting, the cell will display  as the cell
format dictates.
Kindof like cutting a hole in the wall and haning a picture over it.
No matter how big the hole is, or what you put in the hole, all you see is the
Once you remove the picture, then you can see what's been going on with the

Hope this rambling narrative actually make sense...
Comes from caffeine difficiency... soon to be rememdied.

Didn't find what you were looking for? Find more on Macros randomly stopping? Surely not. Or get search suggestion and latest updates.