worksheet change infinite loop/calculate for user functions
 

Hi, I inherited some worksheet and user function code. There are three
functions that seem to be executed only when the worksheet change event
occurs. They're all simple and basically the same - they turn a cell a color
based on other cell values. The first is in all the cells of a matrix very
much like a calendar with months on the x-axis and days on the y axis. The
"day" cells have function 1, the final column cells have function 2, and the
final row cells have function 3. When the user changes a value in a "day"
cell, the function changes the color, triggers a worksheet change event as
well as the aggregate col and row functions, which of course trigger more
worksheet change events. I have two problems (besides inheriting the code):

1. It looks like the only way he was able to trigger the secondary col and
row functions was by putting code in the worksheet change event. This seems
to cause a chain of infinitely looping events that Excel eventually kicks
out of. Sometimes it does it before all the functions execute, sometimes
not. Is there a better implementation? Is there some sort of equivalent to
calculate in vba? Is this what the volatile function is for?

2. When I paste (programmatically or manually) multiple "day" cells, only
the function of the first cell runs. I surmise this is the same problem as
above, but stepping through the code I get the <non Excel code entry in the
stack trace so it's hard to find out what happened.

I don't have much time or license to rewrite very much. Is there an easy and
elegant solution to my problem(s)?

Many thanks,
Mike

worksheet change infinite loop/calculate for user functions
 

To stop infinite triggers put
Application.EnableEvents = False
near the top and
Application.EnableEvents = True
near the bottom
--
Gary's Student


"Mike" wrote:

Hi, I inherited some worksheet and user function code. There are three
functions that seem to be executed only when the worksheet change event
occurs. They're all simple and basically the same - they turn a cell a color
based on other cell values. The first is in all the cells of a matrix very
much like a calendar with months on the x-axis and days on the y axis. The
"day" cells have function 1, the final column cells have function 2, and the
final row cells have function 3. When the user changes a value in a "day"
cell, the function changes the color, triggers a worksheet change event as
well as the aggregate col and row functions, which of course trigger more
worksheet change events. I have two problems (besides inheriting the code):

1. It looks like the only way he was able to trigger the secondary col and
row functions was by putting code in the worksheet change event. This seems
to cause a chain of infinitely looping events that Excel eventually kicks
out of. Sometimes it does it before all the functions execute, sometimes
not. Is there a better implementation? Is there some sort of equivalent to
calculate in vba? Is this what the volatile function is for?

2. When I paste (programmatically or manually) multiple "day" cells, only
the function of the first cell runs. I surmise this is the same problem as
above, but stepping through the code I get the <non Excel code entry in the
stack trace so it's hard to find out what happened.

I don't have much time or license to rewrite very much. Is there an easy and
elegant solution to my problem(s)?

Many thanks,
Mike

worksheet change infinite loop/calculate for user functions
 

Thank you. That's fixes part of my problem. Now, how do I force a Calculate
for the user defined functions?

"Gary''s Student" wrote in message
...
To stop infinite triggers put
Application.EnableEvents = False
near the top and
Application.EnableEvents = True
near the bottom
--
Gary's Student


"Mike" wrote:

Hi, I inherited some worksheet and user function code. There are three
functions that seem to be executed only when the worksheet change event
occurs. They're all simple and basically the same - they turn a cell a
color
based on other cell values. The first is in all the cells of a matrix
very
much like a calendar with months on the x-axis and days on the y axis.
The
"day" cells have function 1, the final column cells have function 2, and
the
final row cells have function 3. When the user changes a value in a "day"
cell, the function changes the color, triggers a worksheet change event
as
well as the aggregate col and row functions, which of course trigger more
worksheet change events. I have two problems (besides inheriting the
code):

1. It looks like the only way he was able to trigger the secondary col
and
row functions was by putting code in the worksheet change event. This
seems
to cause a chain of infinitely looping events that Excel eventually kicks
out of. Sometimes it does it before all the functions execute, sometimes
not. Is there a better implementation? Is there some sort of equivalent
to
calculate in vba? Is this what the volatile function is for?

2. When I paste (programmatically or manually) multiple "day" cells, only
the function of the first cell runs. I surmise this is the same problem
as
above, but stepping through the code I get the <non Excel code entry in
the
stack trace so it's hard to find out what happened.

I don't have much time or license to rewrite very much. Is there an easy
and
elegant solution to my problem(s)?

Many thanks,
Mike