I have posted several questions on worksheet change event behavior, but I
have narrowed the problem down to the recalculation that coincides with the
change event. Is there a way to delay the execution of code inside a
Worksheet_Change subroutine until after any recalculation caused by the
change is complete?

you don't have any control on when the event fires. You do have control on
what the event does. You might look at application.Ontime to have the event
trigger off the actions you want to take with a delay.

http://www.cpearson.com/excel/ontime.htm

for information on using ontime.

that said, in my testing, calculate completes before the change event fires.
I believe that isn't true for a DDE connection - and I have worked around
that by having the event issue a calculate command. So you may have
something special going on. If so, it might be helpful to say what isn't
getting completed before your change event.

--
Regards,
Tom Ogilvy


"AVR" wrote:

I have posted several questions on worksheet change event behavior, but I
have narrowed the problem down to the recalculation that coincides with the
change event. Is there a way to delay the execution of code inside a
Worksheet_Change subroutine until after any recalculation caused by the
change is complete?

Seems to me that the calculate event happens 1st. The problem is that
changes in the cells impact several hundred calculations which are iterative
and take time. The recalc takes about 90 seconds. If I had a way to know
that the calc was complete, I could wait for it before doing the sheet
manipulations. If I issue a calculate command from code, is there a way to
make sure it completes before moving on in the code?


"Tom Ogilvy" wrote:

you don't have any control on when the event fires. You do have control on
what the event does. You might look at application.Ontime to have the event
trigger off the actions you want to take with a delay.

http://www.cpearson.com/excel/ontime.htm

for information on using ontime.

that said, in my testing, calculate completes before the change event fires.
I believe that isn't true for a DDE connection - and I have worked around
that by having the event issue a calculate command. So you may have
something special going on. If so, it might be helpful to say what isn't
getting completed before your change event.

--
Regards,
Tom Ogilvy


"AVR" wrote:

I have posted several questions on worksheet change event behavior, but I
have narrowed the problem down to the recalculation that coincides with the
change event. Is there a way to delay the execution of code inside a
Worksheet_Change subroutine until after any recalculation caused by the
change is complete?

The calculate event does happen 1st (before the change event) - in my
experience after the sheet has been recalculated. Not sure how that affects
the discussion.

Excel should wait for the calculate to complete before continuing but in
later versions of Excel, they added a calculationstate property to the
application

If Application.CalculationState = xlDone Then

So if they added the property, apparently they must be accounting for the
chance that calculations are not completed.

--
Regards,
Tom Ogilvy




"AVR" wrote:

Seems to me that the calculate event happens 1st. The problem is that
changes in the cells impact several hundred calculations which are iterative
and take time. The recalc takes about 90 seconds. If I had a way to know
that the calc was complete, I could wait for it before doing the sheet
manipulations. If I issue a calculate command from code, is there a way to
make sure it completes before moving on in the code?


"Tom Ogilvy" wrote:

you don't have any control on when the event fires. You do have control on
what the event does. You might look at application.Ontime to have the event
trigger off the actions you want to take with a delay.

http://www.cpearson.com/excel/ontime.htm

for information on using ontime.

that said, in my testing, calculate completes before the change event fires.
I believe that isn't true for a DDE connection - and I have worked around
that by having the event issue a calculate command. So you may have
something special going on. If so, it might be helpful to say what isn't
getting completed before your change event.

--
Regards,
Tom Ogilvy


"AVR" wrote:

I have posted several questions on worksheet change event behavior, but I
have narrowed the problem down to the recalculation that coincides with the
change event. Is there a way to delay the execution of code inside a
Worksheet_Change subroutine until after any recalculation caused by the
change is complete?

I have not done much testing with this but I suspect that Excel will wait
for the calculation to complete but will service events that can interrupt
calculation (like DDE) (I think it then stacks the events). In this case I
think testing CalculationState could be useful.

Note there are 3 states for CalculationState ; calculating, pending and
done. You get Pending rather than Done if you have circular references with
iteration enabled, or if you have hit one of the dependency limits (Pending
seems to correspond to Calculate in the status bar).

Charles
______________________
Decision Models
FastExcel 2.3 now available
Name Manager 4.0 now available
www.DecisionModels.com

"Tom Ogilvy" wrote in message
...
The calculate event does happen 1st (before the change event) - in my
experience after the sheet has been recalculated. Not sure how that
affects
the discussion.

Excel should wait for the calculate to complete before continuing but in
later versions of Excel, they added a calculationstate property to the
application

If Application.CalculationState = xlDone Then

So if they added the property, apparently they must be accounting for the
chance that calculations are not completed.

--
Regards,
Tom Ogilvy




"AVR" wrote:

Seems to me that the calculate event happens 1st. The problem is that
changes in the cells impact several hundred calculations which are
iterative
and take time. The recalc takes about 90 seconds. If I had a way to
know
that the calc was complete, I could wait for it before doing the sheet
manipulations. If I issue a calculate command from code, is there a way
to
make sure it completes before moving on in the code?


"Tom Ogilvy" wrote:

you don't have any control on when the event fires. You do have
control on
what the event does. You might look at application.Ontime to have the
event
trigger off the actions you want to take with a delay.

http://www.cpearson.com/excel/ontime.htm

for information on using ontime.

that said, in my testing, calculate completes before the change event
fires.
I believe that isn't true for a DDE connection - and I have worked
around
that by having the event issue a calculate command. So you may have
something special going on. If so, it might be helpful to say what
isn't
getting completed before your change event.

--
Regards,
Tom Ogilvy


"AVR" wrote:

I have posted several questions on worksheet change event behavior,
but I
have narrowed the problem down to the recalculation that coincides
with the
change event. Is there a way to delay the execution of code inside a
Worksheet_Change subroutine until after any recalculation caused by
the
change is complete?