Manual Calc in a loop issue

[Peter T]

Hi All,

Come across an issue whereby writing to cells in a long loop with
calculation set to Manual can cause the following:

After running for 5-10 seconds
- cells no longer visually update
- cannot abort the macro with Esc or Ctrl-break
- Excel's caption might say "Not responding"
- and similar in the task manager (Ctrl-alt-del)
- doing End task in the task manager crashes Excel

Even if the above occurs the loop seems to continue normally in the
background until it comes to a natural end.

I've tested this in 4 systems, 2 run fine but 2 exhibit the above symptoms.
It has been suggested that this may be due to the system not updating
quickly enough, I'm not sure about that as one of the systems that works
fine is very old.

If anyone would care to test "TestManCalc() below I'd be interested in
results, something like this

works normally or displays symptoms,
Excel version & OS
if possible brief details of hardware

Before running ensure you have no unsaved data in Excel, particularly if
you're up for trying End task if you get the above, ie cells stop visually
updating.

Thanks,
Peter T
pmbthornton gmail com


Option Explicit
Const nLimit As Long = 50000 ' adjust to change when the loop terminates

' suggest assign the 4 Test routines to Forms buttons on the sheet
' or run via Alt-F8

Sub TestManCalc()
Static bWarned As Boolean
If Not bWarned Then
If MsgBox("If you are not ready for the possibility of" & vbCr & _
"crashing Excel press Cancel", _
vbOKCancel Or vbDefaultButton2, _
"one off warning") <> vbOK Then Exit Sub
bWarned = True
End If
LongLoop sInfo:="ManualCalc with Re-calcs"
End Sub

Sub TestInterCalc() '
LongLoop bReCalc:=True, sInfo:="ManualCalc with Re-calcs"
End Sub

Sub TestInterDoEvnts()
LongLoop bDoEvnts:=True, sInfo:="ManualCalc with DoEvents"
End Sub

Sub TestAutoCalc() '
LongLoop bMacCalc:=False, sInfo:="AutomaticCalc"
End Sub

Sub LongLoop(Optional bMacCalc As Boolean = True, _
Optional bReCalc As Boolean = False, _
Optional bDoEvnts As Boolean = False, _
Optional sInfo As String)
Dim bFlag As Boolean
Dim i As Long, j As Long

Range("B7") = sInfo
Range("B33") = Array("number", "number", "formula")
Range("B4:C4").Value = 0
Range("D4").Formula = "=1 * B4 * C4"

If bMacCalc Then
Application.Calculation = xlCalculationManual
End If

On Error GoTo errH
Application.EnableCancelKey = xlErrorHandler ' allow abort with Esc

bFlag = True
While bFlag = True
j = j + 1
'Range("C4") = j
Range("C4") = Range("C4") + 1

If bReCalc Then
Application.Calculate
ElseIf bDoEvnts Then
DoEvents
End If

Range("B4") = 0
For i = 1 To 2000 '
'Range("B4") = i
Range("B4") = Range("B4") + 1
Next

bFlag = (i - 1) * j < nLimit
Wend

done:
Application.EnableCancelKey = xlInterrupt
Application.Calculation = xlCalculationAutomatic

Exit Sub
errH:
Resume done
End Sub

 

[Per Jessen]

Hi Peter

It ran normally here.
Excel 2000 and 2007
OS: Vista Home Premium
Processor: AMD Athlon 64 X2 Dual Core Processor 5000+ 2.60 GHz
Memory: 3.0 GB
32-bit OS

Hopes this helps

 

[Jim Cone]

Hi Peter,
TestManCalc() ran find for me (only tried once) in Excel 2002 on Windows XP.
Intel(R) Pentium(R) 4 CPU 2.00GHz, 2019MHz
511MB
--
Note: My rule #3: don't run long loops without a DoEvents inside the loop.
--
Regards,
Jim Cone
Portland, Oregon USA



"Peter T"
<peter_t@discussions>
wrote in message
Hi All,

-snip-

 

[Jim Cone]

Peter,
Oops... really, I do know the difference between find and fine. <g>
Jim

 

[Peter T]

Per and Jim,

Thanks both for testing.
I was kind of expecting one or two might replicate, perhaps it's only in my
systems. Actually no, the issue arose from someone else experiencing similar
in this ng several months ago.

Jim,

My rule #3: don't run long loops without a DoEvents inside the loop.

Really? Well an occasional DoEvents indeed prevents the problem (if done in
time), as does intermittent re-calc. As it happens MS have also suggested
DoEvents. What I find curious is if anything I would expect the scenario
more likely to occur with automatic calculation rather than manual.

However I don't think DoEvents is a good idea at all, at least not without
knowing the potential consequenses. Say the routine was called from a
button, user presses the button another 5 times whilst running between
subseqent Doevents (people do that kind of thing), when done the macro will
re-run another 5 times. Or maybe something else will get triggered. Also,
if DoEvents is called in every loop, as some do, it'll significantly slow
things.

Rules #1 & 2, dare I ask ?

Regards,
Peter T

 

[Jim Cone]

Peter,
Since you asked... <g>
Rule #1 - Don't vote, it only encourages them.
Rule #2 - Get the money in advance.

DoEvents allows the user to quit running code with Escape or Ctrl + Break.
Also, long loops should have a progress indicator and that can provide
an interval for the DoEvents function.

I am unsure about multiple button clicks running code multiple times?
However, a lot of my code is run from MenuItems (Microsoft please note) or
a new sheet is activated to display results (no buttons on it).
Regards,
Jim Cone



"Peter T"
<peter_t@discussions>
wrote in message
Per and Jim,
Thanks both for testing.
I was kind of expecting one or two might replicate, perhaps it's only in my
systems. Actually no, the issue arose from someone else experiencing similar
in this ng several months ago.

Jim,

My rule #3: don't run long loops without a DoEvents inside the loop.

Really? Well an occasional DoEvents indeed prevents the problem (if done in
time), as does intermittent re-calc. As it happens MS have also suggested
DoEvents. What I find curious is if anything I would expect the scenario
more likely to occur with automatic calculation rather than manual.

However I don't think DoEvents is a good idea at all, at least not without
knowing the potential consequenses. Say the routine was called from a
button, user presses the button another 5 times whilst running between
subseqent Doevents (people do that kind of thing), when done the macro will
re-run another 5 times. Or maybe something else will get triggered. Also,
if DoEvents is called in every loop, as some do, it'll significantly slow
things.
Rules #1 & 2, dare I ask ?
Regards,
Peter T

 

[Peter T]

Hi Jim,

Since you asked... <g>
Rule #1 - Don't vote, it only encourages them.
Rule #2 - Get the money in advance.

Can't argue with that, so maybe #3 is right too

Regards,
Peter T

 

[Telecorder]

Also, long loops should have a progress indicator and that can provide
an interval for the DoEvents function.

Jim- (Or other knowledgable entities...)
Was looking for such an animal to include in some code since my users will
be running the program on laptops/PCs that may have different processors and
RAM.

I came across
http://spreadsheetpage.com/index.php/file/progress_indicator_demo/ that looks
promising but I'm unclear as to how to code the logic for updating.

My code on user click searches a series of worksheet data tables for cell
entries >0; consolidates the rows/columns associated with a cell entry >0 and
populates a summary worksheet with just those entries found.

I saw where a "Kludge" might be used as if...

Start = Timer
Do while Time <start+0.1
DoEvents
Loop

But I'm really unsure of how to write the Sub Main code to incorporate the
SpreadsheetPageExcelTips approach with the "kludge" (Whatever that might
refer to...)

Any insights, anyone?