Vista -vs- XP

[Bill Martin]

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

 

[Peter T]

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]

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

 

[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

 

[NOPIK]

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

 

[Bill Martin]

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
----------------------

 

[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

 

[Peter T]

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

Regards,
Peter T

 

[NOPIK]

Without endless loop (it's pointless), everything works.
Yes, Excel is busy so it can't update screen - but it is absolutely
normal and it stops after end of loop. No error messages from Vista.

 

[Bill Martin]

On my system it depends on what else Vista is running.

For example, I have code that runs flawlessly under both XP and Vista
when nothing else is running on the machine. If I also run the
taskmanager (showing CPU usage) at the same time however, then the
program fails under Vista.

It seems to depend on whether Vista is switching among tasks in some
what that I don't understand, but I can see the effects of it.

Bill

 

[Bill Martin]

Are you sure your application is dead and not just silent and deaf?

In my case the only clue it's actually alive is to see that the
taskmanager is still showing heavy CPU usage by Excel while it's
"dead". That was my first clue that the program was still active, but
just not communicating with Vista enough.

Like you, I must also quit Excel and restart it to work with it but I
know it's still working silently (and uselessly).

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

 

[Peter T]

Are you sure your application is dead and not just silent and deaf?

Good distinction, but running the demo I posted - after I press Esc
(deliberately forcing goto error handler due to EnableCancelKey =
xlErrorHandler) it most definitely becomes deader than a stuffed Norwegian
parrot

Regards,
Peter S