controlling excel recalculation in VBA?

[mvl_groups_user]

I have a huge excel model that takes 1-2 minutes to recalculate each
time I change data.

I like the concept of automatic recalculation, in that it stops
recalculating while you type and waits until idle to kick off the
recalculation.

However, excel has an annoyance that it forces an uninterruptable
recalculation each time I paste data (in auto-recalc mode).

Is there any way to set up excel so it doesn't do an uninterruptable
recalc upon paste, but still recalcs on idle?

Assuming the answer to the above is no, is there a way to
programatically start and suspend recalculation so that I can write
this manually?

I'm thinking of writing VBA code along the following lines:
1) change calculation to manual
2) set a keystroke event that starts calculation on a delay timer (eg
10 no-key seconds)
3) also (this seems the hardest) set a keystroke event that stops an
ongoing calculation upon any key press

-MVL

 

[Pranav Vaidya]

I don't understand clearly why you want to write a macro for tis.

Simply set the calc mode to manual and hit F9 whenever you to calculate,
which is built-in option.

 

[mvl_groups_user]

I know I can do that, but there never seems to be a good time to hit
F9. I like how it automatic mode recalulates while idle so I don't
have to consciously choose a 1-2 minute downtime period to wait for
recalculation. My problem is working around the issue where automatic
mode forces an uninterruptable recalc after a paste.

-MVL

 

[Guest]

As a quick and easy step, change the CalculationInterruptKey

Application.CalculationInterruptKey = xlAnyKey

That seems to do what you outline in your steps.

 

[Pranav Vaidya]

OK..then try this....
use Application.onTime method to enforce the automatic calculation.

create a new function that calculates the worksheet and call this at a fixed
interval using Application.OnTime.

 

[mvl_groups_user]

Thanks to both for the help. I was able to get the ontimer working,
but did have to workaround 2 Excel quirks:

1) Application.CalculationInterruptKey = xlAnyKey seems to be changed
to xlNoKey (at least temporarily) after a large paste. I had to
include the Application.CalculationInterruptKey = xlAnyKey statement
inside my timer sub. I suspect this is the same "optimization" that
MS did that prevents interruption after paste in Automatic mode. I'm
hoping MS will be nice and create a registry flag to shut this off.

2) I lose the clipboard during a recalc. I had to check for
Application.CutCopyMode = 0 before executing the recalc.

I have a 3rd bug that I haven't been able to fix. The "end" mode
(when the END key is pressed) resets upon a recalc as well. Is there
a vba property that can read the "end" state? Googling "end" returns
way to many irrelevant results.

-MVL