calculationstate

there is a code:

..
..
..
application.calculate
..
..
..
..


application.calculate initiates calculation end execution of vba code is
continued.
How to hold code execution until calculation is finished?

to the best of my knowledge, Calculate doesn't operate asynchronously, so it
shouldn't be an issue.

If it is for some reason, then break your code into two pieces (calculate at
the end of the first). Then use Application.OnTime to start the second
section of code with an appropriate delay.

--
Regards,
Tom Ogilvy


"Kris" <witek7205@spam.gazeta.pl.invalid> wrote in message
news:do9n79$idj$1@inews.gazeta.pl...
> there is a code:
>
> .
> .
> .
> application.calculate
> .
> .
> .
> .
>
>
> application.calculate initiates calculation end execution of vba code is
> continued.
> How to hold code execution until calculation is finished?
>
>
>
>
>

Tom Ogilvy wrote:
> to the best of my knowledge, Calculate doesn't operate asynchronously, so it
> shouldn't be an issue.

When I put break after calculate and I look at call stack I have


vbaproject.sheet1.worksheet.change
<non basic code>
vbaproject.udf.myfun


udf is my module

it means that myfun which is a udf function was called by excel from one
of cells, this function was interrupted by excel and my code in
worksheet_change event is continued.
When worksheet_change is finished excel goes back and finishes calculation.

But I can't continue worksheet_change without calculated values in cells.







>
> If it is for some reason, then break your code into two pieces (calculate at
> the end of the first). Then use Application.OnTime to start the second
> section of code with an appropriate delay.

Calculation time is now 5 seconds, I can't add any second more.

Maybe try

Application.EnableEvents = False
Application.Calculate
Application.EnableEvents = True


"Kris" wrote:

> Tom Ogilvy wrote:
> > to the best of my knowledge, Calculate doesn't operate asynchronously, so it
> > shouldn't be an issue.
>
> When I put break after calculate and I look at call stack I have
>
>
> vbaproject.sheet1.worksheet.change
> <non basic code>
> vbaproject.udf.myfun
>
>
> udf is my module
>
> it means that myfun which is a udf function was called by excel from one
> of cells, this function was interrupted by excel and my code in
> worksheet_change event is continued.
> When worksheet_change is finished excel goes back and finishes calculation.
>
> But I can't continue worksheet_change without calculated values in cells.
>
>
>
>
>
>
>
> >
> > If it is for some reason, then break your code into two pieces (calculate at
> > the end of the first). Then use Application.OnTime to start the second
> > section of code with an appropriate delay.
>
> Calculation time is now 5 seconds, I can't add any second more.
>
>

Charlie wrote:
> Maybe try
>
> Application.EnableEvents = False
> Application.Calculate
> Application.EnableEvents = True
>

No.
I doesn't work. Still the same.

Are saying you want all sheet calculations to complete first, then the
Worksheet_Change event to fire last? (But the calculations are firing it
prematurely?) Yea? Nay?

Maybe you can re-fire the Worksheet_Change event after the
Application.EnableEvents = True statement. I don't know how to do that.
Tom? Jim? Chip?

Or you may need to rewrite your logic to move or duplicate what it is you
are doing in the Worksheet_Change sub to your main code.

That's about all I can think of.

"Kris" wrote:

> Charlie wrote:
> > Maybe try
> >
> > Application.EnableEvents = False
> > Application.Calculate
> > Application.EnableEvents = True
> >
>
> No.
> I doesn't work. Still the same.
>
>
>

Tom Ogilvy wrote:
> to the best of my knowledge, Calculate doesn't operate asynchronously, so it
> shouldn't be an issue.


I checked it on simple example.
Calculate waits if it is a simple workbook.
But my is much much more complicated and I have much much more
dependecies than 65535 which causes full calculation all time.
Excel is lost in that case and calucalte method works different, but who
knows how.

Tom Ogilvy wrote:
> to the best of my knowledge, Calculate doesn't operate asynchronously, so it
> shouldn't be an issue.


I checked it on simple example.
Calculate waits if it is a simple workbook.
But my is much much more complicated and I have much much more
dependecies than 65535 which causes full calculation all time.
Excel is lost in that case and calucalte method works different, but who
knows how.

Tom Ogilvy wrote:
> to the best of my knowledge, Calculate doesn't operate asynchronously, so it
> shouldn't be an issue.
>
> If it is for some reason, then break your code into two pieces (calculate at
> the end of the first). Then use Application.OnTime to start the second
> section of code with an appropriate delay.
>

As a final answer:

Worksheet_change event doesn't work correctly if cell which changes its
value has data validation and you use drop down box to change that value.

another approach would be

> .
> .
> .
> application.calculate
DoEvents
> .
> .
> .
> .

But again, I don't think this is necessary.

--
Regards,
Tom Ogilvy


af
"Kris" <witek7205@spam.gazeta.pl.invalid> wrote in message
news:do9n79$idj$1@inews.gazeta.pl...
> there is a code:
>
> .
> .
> .
> application.calculate
> .
> .
> .
> .
>
>
> application.calculate initiates calculation end execution of vba code is
> continued.
> How to hold code execution until calculation is finished?
>
>
>
>
>

Tom Ogilvy wrote:
> another approach would be
>
>
>>.
>>.
>>.
>>application.calculate
>
> DoEvents
>


No. It doesn't help.

There are no events waiting.

So I see

--
Regards,
Tom Ogilvy


"Kris" <witek7205@spam.gazeta.pl.invalid> wrote in message
news:do9r1c$94s$2@inews.gazeta.pl...
> Tom Ogilvy wrote:
> > another approach would be
> >
> >
> >>.
> >>.
> >>.
> >>application.calculate
> >
> > DoEvents
> >
>
>
> No. It doesn't help.
>
> There are no events waiting.
>