Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 * |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel E-2007 starts, but Installer also starts 3 times??? | Setting up and Configuration of Excel | |||
calculate from time started and finished to get total time used | Excel Worksheet Functions | |||
Please Help: Macro Nearly Finished | Excel Discussion (Misc queries) | |||
Loop until finished | Excel Discussion (Misc queries) | |||
Turn off calculate event | Excel Discussion (Misc queries) |