I've long been running Excel 2003 on my XP machine, but recently I've
had occasion to run the same workbooks under Vista. Which raises a
question.

Is there some way I can keep the calculation turned off, and yet keep
Vista from declaring Excel to have become unresponsive?

My workbook is essentially just a lot of VBA that optimizes a problem.
So the program can run for days if I choose to let it keep refining
the solution. To make it run faster I've turned off screen updating
and automatic calculation. At occasional points in the code I turn
the functions back on briefly to allow the screen to update its
progress for me, but basically these functions are off. And I update
a status message on the status bar for my convenience perhaps once a
second.

Everything works fine as expected under XP. With Vista however, after
running a few seconds, Vista will declare Excel to be "not
responding". The main effect of this is that the status bar no longer
updates (so I can't see what's going on), and the 'Esc' key which can
normally interrupt the VBA execution does not work. The program is
still executing, but it is silent and cannot be interrupted.

After fooling around with this I find that if I toggle the automatic
calculation mode on/off every few seconds, then Vista is happy and
lets Excel run as it does under XP.

Is there some way I can keep the automatic calculation turned off, and
yet keep touching Vista in some way that won't make Vista declare
Excel to have become unresponsive?

Thanks.

Bill

I replicate what you describe in Vista.

I don't have a direct solution apart from the workaround you have discovered
for yourself.

A different approach, perhaps, might be to do all your work out of cells. Eg
manipulate arrays in memory, then dump your results to cells when done.
Depending on what you are doing, use of worksheet formulas etc, you might
find this approach works significantly faster - and solves the main problem.

Regards,
Peter T


"Bill Martin" wrote in message
...
I've long been running Excel 2003 on my XP machine, but recently I've
had occasion to run the same workbooks under Vista. Which raises a
question.

Is there some way I can keep the calculation turned off, and yet keep
Vista from declaring Excel to have become unresponsive?

My workbook is essentially just a lot of VBA that optimizes a problem.
So the program can run for days if I choose to let it keep refining
the solution. To make it run faster I've turned off screen updating
and automatic calculation. At occasional points in the code I turn
the functions back on briefly to allow the screen to update its
progress for me, but basically these functions are off. And I update
a status message on the status bar for my convenience perhaps once a
second.

Everything works fine as expected under XP. With Vista however, after
running a few seconds, Vista will declare Excel to be "not
responding". The main effect of this is that the status bar no longer
updates (so I can't see what's going on), and the 'Esc' key which can
normally interrupt the VBA execution does not work. The program is
still executing, but it is silent and cannot be interrupted.

After fooling around with this I find that if I toggle the automatic
calculation mode on/off every few seconds, then Vista is happy and
lets Excel run as it does under XP.

Is there some way I can keep the automatic calculation turned off, and
yet keep touching Vista in some way that won't make Vista declare
Excel to have become unresponsive?

Thanks.

Bill

Thanks for letting me know that it's not just my code that's strange.

I'll have to poke at it some more. There must be some simple system
call I can do to tell Vista the program is still responsive. Turning
autocalc on and off is just such a waste since there's nothing that
needs that sort of calculation anyhow.

I'll let you know if I stumble into something.

Bill
---------------------------

On Tue, 30 Sep 2008 23:30:29 +0100, "Peter T" <peter_t@discussions
wrote:

I replicate what you describe in Vista.

I don't have a direct solution apart from the workaround you have discovered
for yourself.

A different approach, perhaps, might be to do all your work out of cells. Eg
manipulate arrays in memory, then dump your results to cells when done.
Depending on what you are doing, use of worksheet formulas etc, you might
find this approach works significantly faster - and solves the main problem.

Regards,
Peter T

I found simply doing "Application.Calculate" roughly every 5 seconds is
enough to prevent the crash, slotted into the loop appropriately.

I'll let you know if I stumble into something.

I'll also look into it.

Regards,
Peter T



"Bill Martin" wrote in message
...
Thanks for letting me know that it's not just my code that's strange.

I'll have to poke at it some more. There must be some simple system
call I can do to tell Vista the program is still responsive. Turning
autocalc on and off is just such a waste since there's nothing that
needs that sort of calculation anyhow.

I'll let you know if I stumble into something.

Bill
---------------------------

On Tue, 30 Sep 2008 23:30:29 +0100, "Peter T" <peter_t@discussions
wrote:

I replicate what you describe in Vista.

I don't have a direct solution apart from the workaround you have
discovered
for yourself.

A different approach, perhaps, might be to do all your work out of cells.
Eg
manipulate arrays in memory, then dump your results to cells when done.
Depending on what you are doing, use of worksheet formulas etc, you might
find this approach works significantly faster - and solves the main
problem.

Regards,
Peter T

What I need to do to replicate it on Vista?
I never get anything like this and want to avoid it.

Try the routine I just posted as follow-up to Bill.

Regards,
Peter T

"NOPIK" wrote in message
...
What I need to do to replicate it on Vista?
I never get anything like this and want to avoid it.

Interesting. That's a whole lot faster than toggling the mode on/off.

Curiously, I don't find any of the other Application statements that
seem to keep Vista alive other than the various Calculation ones.

Thanks.

Bill
----------------------
On Wed, 1 Oct 2008 00:13:04 +0100, "Peter T" <peter_t@discussions
wrote:

I found simply doing "Application.Calculate" roughly every 5 seconds is
enough to prevent the crash, slotted into the loop appropriately.

I'll let you know if I stumble into something.

I'll also look into it.

Regards,
Peter T

Reading your OP again the problem is, if anything, even worse for me. You
said "the program is still executing" even though the app is marked "not
responding". That's not the case for me, I need to quit Excel from the Task
manager (ctrl-alt-del).

I've had another look and, strangely, even a DoEvents every 3-5 seconds in
the loop also seems enough to fix. At least it does for me in the
following -

Sub test()
Dim b As Boolean, i As Long
' press Esc to abort

' in Vista prepare to crash Excel and close with ctrl-Alt-Del

Range("A1").Formula = "=1 * B1"
Range("B1").Value = 1

Application.Calculation = xlCalculationManual
On Error GoTo errH
Application.EnableCancelKey = xlErrorHandler
b = True
While b = True
Range("C1") = Range("C1") + 1

' Application.Calculate '' uncomment to work around in Vista
'' or try a DoEvents
' DoEvents

For i = 1 To 12345 ' also try i = 1 To 123456
Range("B1") = i
Next
Wend

done:
Application.EnableCancelKey = xlInterrupt
Application.Calculation = xlCalculationAutomatic
Exit Sub
errH:
Resume done
End Sub


The above should run a continuous loop until aborted with Esc. Before trying
in Vista ensure all work is saved (incl non-Excel app's)

Whilst it might seem tempting to use DoEvents in preference to
app.calculate, allowing user to do something mid loop might cause even more
problems, eg manually entering a value in a cell.

Neither you (Bill) nor I are using Excel 2007 which hopefully does not
manifest the same problem in Vista.

Regards,
Peter T


"Bill Martin" wrote in message
...
Interesting. That's a whole lot faster than toggling the mode on/off.

Curiously, I don't find any of the other Application statements that
seem to keep Vista alive other than the various Calculation ones.

Thanks.

Bill
----------------------
On Wed, 1 Oct 2008 00:13:04 +0100, "Peter T" <peter_t@discussions
wrote:

I found simply doing "Application.Calculate" roughly every 5 seconds is
enough to prevent the crash, slotted into the loop appropriately.

I'll let you know if I stumble into something.

I'll also look into it.

Regards,
Peter T