VBA code crashes Excel 97 - Help please!

[GB]

Hi, I have some simple code (below) that works most of the time.
Unfortunately, if there is an error in the data in the spreadsheet, this can
cause some of the values in the spreadsheet to go N/A, 'Not a value', Div/0
or something similar. The result is not just that the macro stops working,
but it crashes Excel completely. Is this normal expected behaviour from
Excel 97? What is the best way to trap the errors, so it just keeps working
and either leaves a blank in my results page or copies the N/A figures, then
moves onto the next case?

Can I put in something like:
On Error Resume Next

(You can see that I was brought up on GW-Basic.)


------------------------------------------------------------------------------------------------
Here is the VBA code:

Public Sub DoAllCalcs()


For JJ = 1 To 740 ' Use 1 for first case, up to no. of cases

Sheets("Basic Info").Range("C1").Value = JJ 'Increments case no. to get data
from database

Calculate

Sheets("ResultsList").Range("A" & JJ + 3).Value = JJ

Sheets("ResultsList").Range("B" & JJ + 3).Value =
Sheets("Summary").Range("C4")
Sheets("ResultsList").Range("C" & JJ + 3).Value =
Sheets("Summary").Range("C25")
Sheets("ResultsList").Range("D" & JJ + 3).Value =
Sheets("Summary").Range("D25")
Sheets("ResultsList").Range("E" & JJ + 3).Value =
Sheets("Summary").Range("E25")
Sheets("ResultsList").Range("F" & JJ + 3).Value =
Sheets("Summary").Range("F25")
Sheets("ResultsList").Range("H" & JJ + 3).Value =
Sheets("Summary").Range("G31")
Sheets("ResultsList").Range("I" & JJ + 3).Value =
Sheets("Summary").Range("C35")
Sheets("ResultsList").Range("J" & JJ + 3).Value =
Sheets("PastCalcs").Range("I5")
Sheets("ResultsList").Range("K" & JJ + 3).Value =
Sheets("PastCalcs").Range("Q5")

Debug.Print JJ

Next


End Sub

 

[Peter T]

I couldn't reproduce what I understood as being your problem in Excel 97
with this test

Range("A1").Formula = "=1/0" ' #DIV/0!
Range("B1").Value = Range("A1").Value
v = Range("B1").Value
If IsError(v) Then v = Range("B1").Text ' #DIV/0!
MsgBox v

Could you isolate the particular value that causes the problem and
demonstrate with simplified code, indicate the line that causes the crash.

Regards,
Peter T

 

[GB]

Hmm, I think it's something about that particular spreadsheet, which is very
big.

For example, if I set the vba code running, then press ctrl-break, Excel
brings up the msgbox with End and Debug as options, and if I choose either
one of those, it crashes Excel.

I put the same code into a blank spreadsheet, and follow the same
procedure - it works fine.

This may be some bug that only appears where the spreadsheet takes a long
time to recalculate -my main one takes a few seconds.

Any further thoughts? Or do I just shrug and move on?

 

[Peter T]

Any further thoughts? Or do I just shrug and move on?

What I suggested earlier, isolate the line that triggers the crash, post the
line and anything else relavant. Or if it's OK to keep crashing your Excel
then by all means shrug and move on!

Regards,
Peter T

 

[GB]

Like I said, it's not the VBA code that causes the problem. I have tried
the same code in a blank spreadsheet, and it does not cause excel to crash.
So I assume it is associated with the size of this spreadsheet, which is not
something I can do anything about. The spreadsheet works perfectly under
manual control, and I do not have the choice of altering the spreadsheet.
Thanks for your interest, though.

This may be some bug that only appears where the spreadsheet takes a long
time to recalculate -my main one takes a few seconds.

If there's anyone around who has come across this sort of problem and/or has
a solution, I would be interested to hear. Thanks again.