SheetChange event starts before .calculate finished
 

I've never seen xl behave this way.

Is there some chance that something else is happening?

Maybe when you store your information???

If you store your info in the same workbook (different worksheet), does your
store_db procedure turn off application.enableevents?



Nils M wrote:

In my application code the change event of the worksheets is used to
store the values of the changed cells in a database.

Unfortunately if the user enters a formula in one cell and goes
directly to another cell e.g with the cell dows key, the calculation of
the formula is not completed before the event handler is fired and the
value of the cell is set to "0" although the formula property is
already updated. This effect can only be reproduced on slow machines
(like my PC).

Any idea how to force Excel to complete the calculation of the changed
range?

My second idea is to read the formula property and calculate it in the
code. But I havenot found a solution for this without writing a
complete parser for all Excel formulas.

Kind regards,

Nils

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Range)

Target.Calculate
'v = Target.Value = "0"
Call store_db(Sh.Index, Target)

End Sub

--

Dave Peterson

SheetChange event starts before .calculate finished
 

We can reproduce this behaviour on 3 different PC(Pentium II) with
XL2000 when we slow down application speed by running the application
twice. We also realized this after 2 monthes development time by
incidence when we entered old data very quickly in the sheets.

The procedure store_db definetly don't touch any event. It can be
empyty. If you place a breakpoint before calling the procedure you
already see that target.value="0"

Any other ideas?

Dave Peterson wrote:
*I've never seen xl behave this way.

Is there some chance that something else is happening?

Maybe when you store your information???

If you store your info in the same workbook (different worksheet),
does your
store_db procedure turn off application.enableevents?


Nils M wrote:

In my application code the change event of the worksheets is used
to
store the values of the changed cells in a database.

Unfortunately if the user enters a formula in one cell and goes
directly to another cell e.g with the cell dows key, the
calculation of
the formula is not completed before the event handler is fired and
the
value of the cell is set to "0" although the formula property is
already updated. This effect can only be reproduced on slow
machines
(like my PC).

Any idea how to force Excel to complete the calculation of the
changed
range?

My second idea is to read the formula property and calculate it in
the
code. But I havenot found a solution for this without writing a
complete parser for all Excel formulas.

Kind regards,

Nils

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target
As
Range)

Target.Calculate
v = Target.Value '= "0"
f = Target.Formula '= "=234+1"

Call store_db(Sh.Index, Target)

End Sub

--

Dave Peterson
*

SheetChange event starts before .calculate finished
 

I think it might be difficult to reproduce the problem by only testing
the peace of code. You have to use it in a complex spreadsheet where
the calculation of the sheets take some time.
Our application has about 60 sheets and 2000 input fields. The normal
recalculation after inserting a value takes about a second on my PC.
But if you continue to enter data in other cells, the recalculation
seems to be postponed until there is enough time to complete it.

Is there a simple way to get the "result" of the .Formula property
except looking to the .value?

Kind regards,

Nils.