| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA code crashes Excel 97 - Help please!
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA code crashes Excel 97 - Help please!
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" wrote in message ... 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA code crashes Excel 97 - Help please!
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" <peter_t@discussions wrote in message ... 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" wrote in message ... 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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA code crashes Excel 97 - Help please!
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" wrote in message ... 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" <peter_t@discussions wrote in message ... 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" wrote in message ... 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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA code crashes Excel 97 - Help please!
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. "Peter T" <peter_t@discussions wrote in message ... 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" wrote in message ... 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" <peter_t@discussions wrote in message ... 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" wrote in message ... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| AddFromString Code - Excel crashes ... Why ? | Excel Programming | |||
| VBA WinInet Code Crashes Excel | Excel Programming | |||
| Excel crashes when adding code using vbproject object? | Excel Programming | |||
| Simple code crashes Excel | Excel Programming | |||
| Code in ThisWorkbook crashes Excel | Excel Programming | |||
![GB[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
