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?
>
>
>
>
>

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:
> 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.

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


No. It doesn't help.

There are no events waiting.

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.
>
>
>

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.
>

How can a calculate fire a Change event. Are there DDE links?

More likely a change event fires a calculate.

--
Regards,
Tom Ogilvy

"Charlie" <Charlie@discussions.microsoft.com> wrote in message
news:6E85DE22-603E-46FA-BB29-D706754CFB67@microsoft.com...
> 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.
> >
> >
> >

Charlie wrote:


> 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?

Worksheet_change is fired by a user who changes something on a sheet.
Because calculation is set to manual I must recalculate sheet.

Calcualate can fire worksheet_change once again, but I exit it if target
is out of specific region. It can happen when calcuate change something
of that sheet. But it doesn't and I quit worksheet_change if this is out
of region I am interested in.


application.calculate doesn't wait.
so the rest of code doesn't make sense, because data is not updateed on
the rest of sheets.



>
> 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.

The only thing which I can do is to remove the rest of code from
worksheet_change, put it as a separate procedure and add button to
worksheet. Now user can finish code manualy, but I don't want to do that.

Tom Ogilvy wrote:
> How can a calculate fire a Change event.

You are right calculate doesn't fire worksheet_change, but it doesn't
change my situation


>
> More likely a change event fires a calculate.

in automatic calculation yes

in manual you must do this manualy by calling application.calculate or
activesheet.calculate or similar.
but the problem is if code in worksheet_change needs updated data.
There is no way to wait 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.


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.

It would be easier to help if I could see your change sub section but this is a example of one. You have to be careful or you can make an endless loop where the user makes a change and then the application makes a change which fires the change event again....

Please Login or Register  to view this content.

Originally Posted by Kris

Tom Ogilvy wrote:
> How can a calculate fire a Change event.

You are right calculate doesn't fire worksheet_change, but it doesn't
change my situation


>
> More likely a change event fires a calculate.

in automatic calculation yes

in manual you must do this manualy by calling application.calculate or
activesheet.calculate or similar.
but the problem is if code in worksheet_change needs updated data.
There is no way to wait until calculation is finished.

CodeJunky wrote:
> It would be easier to help if I could see your change sub section but
> this is a example of one. You have to be careful or you can make an
> endless loop where the user makes a change and then the application
> makes a change which fires the change event again....


Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("RangeName")) Is Nothing Then
ActiveSheet.Calculate
CollectData
End If


CollectData simply copy data from other sheets and put into this one.
Three nested loops.
CollectData calls CollectSegmentData with different paramters

SheetsName is a list of sheets with data
SegmentName is a name of range with data to copied
DestinationName is a range on active sheet where data should go

number of columns in DestinationName and SegmentName are the same

number of rows of destinationName is defined by NumberOfSegmentsVisible
and equals size of SheetsName(s).




Sub CollectSegmentData(SheetsName As String, SegmentName As String,
DestinationName As String, NumberOfSegmentsVisible As Integer)

Dim iRows As Integer
Dim iCols As Integer
Dim iRow As Integer
Dim iCol As Integer
Dim Sh As Worksheet

iCols = Range(DestinationName).Columns.Count
Range(DestinationName).ClearContents
For iRow = 1 To NumberOfSegmentsVisible
If Not IsError(Range(SheetsName)(iRow).Value) Then
Dim wksName As String
wksName = Range(SheetsName)(iRow).Value
Dim wks As Worksheet
Set wks = ActiveWorkbook.Sheets(wksName)
For iCol = 1 To iCols
Dim SourceValue As Double
SourceValue = wks.Range(SegmentName)(1, iCol).Value
Dim DestCell As Range
Set DestCell = Range(DestinationName)(iRow, iCol)
DestCell.Value = SourceValue
Next iCol
End If
Next iRow
Exit Sub





The problem is that CallectData is fired before calculation is finished.
It doesn't happen on simple examples. it happens on this one.
usually
DestCell.Value = SourceValue
fails with an error.
I don't know why by destination sheet can't be modified.

It's like a circle: can't calculate without data, can't get data without
calculating. Sounds like it's time to re-examine the logic flow and design a
new method of solving the problem.

"Kris" wrote:

> Charlie wrote:
>
>
> > 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?
>
> Worksheet_change is fired by a user who changes something on a sheet.
> Because calculation is set to manual I must recalculate sheet.
>
> Calcualate can fire worksheet_change once again, but I exit it if target
> is out of specific region. It can happen when calcuate change something
> of that sheet. But it doesn't and I quit worksheet_change if this is out
> of region I am interested in.
>
>
> application.calculate doesn't wait.
> so the rest of code doesn't make sense, because data is not updateed on
> the rest of sheets.
>
>
>
> >
> > 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.
>
> The only thing which I can do is to remove the rest of code from
> worksheet_change, put it as a separate procedure and add button to
> worksheet. Now user can finish code manualy, but I don't want to do that.
>
>

Charlie wrote:
> It's like a circle: can't calculate without data, can't get data without
> calculating. Sounds like it's time to re-examine the logic flow and design a
> new method of solving the problem.
>
It's not a circle. There is no loop there.
Summary sheet collect data from other sheets and other sheet doesn't
need any data from summary sheet to be calculated.

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.