SheetChange event starts before .calculate finished
 

Hi Nils,

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

Try setting Application.Interactive = False before the .Calculate and
back to True afterwards.

Regards

Stephen Bullen
Microsoft MVP - Excel
www.BMSLtd.co.uk

SheetChange event starts before .calculate finished
 

Sorry Stephen, no effect: v=Target.value is still "0"

regards

Nils

Stephen Bullen wrote:
*Hi Nils,

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

Try setting Application.Interactive = False before the .Calculate
and
back to True afterwards.

Regards

Stephen Bullen
Microsoft MVP - Excel
www.BMSLtd.co.uk *

SheetChange event starts before .calculate finished
 

Hi Nils,

Sorry Stephen, no effect: v=Target.value is still "0"

OK, after a bit of investigation, I've managed to reproduce what you're seeing!

Am I correct in thinking that you have a complex workbook that takes some time to recalc and it's actually formulae that you're typing in that
you need to ensure are calculated correctly before writing their results to a database - so it's not the data entry that you're storing, but some
formulae results? And what you're finding is that continuing to type is automatically interrupting Excel's calculation?

There are two possible solutions to this issue (I think):

Solution 1 (tested):

Get Excel 2002 (in Office XP) and Application.CalculationInterruptKey = xlEscKey before Target.Calculate and Application.CalculationInterruptKey
= xlAnyKey afterwards. This is a new property added to Excel 2002 specifically to control when/if calculation can be interrupted.

Solution 2 (untested):

A. Use API Calls to see if there's anything in the Windows keyboard buffer (see checkkey.zip on my web site), store them and remove them.
B. Set Application.Interaction = False, do the Calc and set it back to true
C. Use SendKeys to put the keystrokes stored in A. back in the buffer

Regards

Stephen Bullen
Microsoft MVP - Excel
www.BMSLtd.co.uk

SheetChange event starts before .calculate finished
 

Hi Nils,

Regarding solution 2 you may provide me with some additional
information. I see in your example (checkkey) how to look if a key is
pressed (although I don't know how the API works). How do I remove the
key from the buffer before the calculation is interupted? I haven't
writen such an interrup handler.

Don't bother - it didn't work!

How about using:

v = Application.Evaluate(Target.Formula)

instead of forcing the Target.Calculate that gets interrupted?

Regards

Stephen Bullen
Microsoft MVP - Excel
www.BMSLtd.co.uk

SheetChange event starts before .calculate finished
 

Hi Stephen,

Congratulations! Thats excactly the thing I wanted to do when I talked
about getting the result of the .Formula without taking .value. And it
seems to work. You only have to check if .formula is filled before you
can do it. Otherwise you get an error when cell content is deleted.

My code now looks like the following and after the first test it seems
to be ok.

Thanks a lot,

regards

Nils.

For Each i In target

If i.Formula = "" Then
v = i.Value
Else
v = Application.Evaluate(i.Formula)
End If
...
Next i

Stephen Bullen wrote:
*Hi Nils,

Regarding solution 2 you may provide me with some additional
information. I see in your example (checkkey) how to look if a key
is
pressed (although I don't know how the API works). How do I remove
the
key from the buffer before the calculation is interupted? I
haven't
writen such an interrup handler.

Don't bother - it didn't work!

How about using:

v = Application.Evaluate(Target.Formula)

instead of forcing the Target.Calculate that gets interrupted?

Regards

Stephen Bullen
Microsoft MVP - Excel
www.BMSLtd.co.uk *



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

SheetChange event starts before .calculate finished
 

Maybe:
if i.hasformula then

would be safer.

(pretty interesting thread, too.)



Nils M wrote:

In addition to my last response where I rashly said that it works there
seems to be a bug in Excel:

The code:

v = Application.Evaluate(i.Formula)

produce a run time error for i.Formula="1"! All other values/formulas
are accepted even "=2-1". Strange but this is Excel ;-)

My code now looks like

If Left(i.Formula, 1) = "=" Then
v = Application.Evaluate(i.Formula)
Else
v = i.Value
End If

regards,

Nils

------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

--

Dave Peterson