|
Exit Sub not exiting
I have a very strange problem. When the following code executes and the
condition is not met, it goes to LoadDataExit as expected, but then instead of stopping when it gets to the end, it goes back to cmdLoadData and executes repeatedly. This happened once before in another workbook, and at the time (being more of an Access programmer) I decided it was some kind of corruption and went back to an earlier version and applied all the code changes, which got it working again. Since it's happened again in a totally different workbook, and since that kind of fix is very tedious and not necessarily guaranteed to work, I'm wondering what could be happening and how best to fix it now and prevent it in the future. I'd appreciate any help. Private Sub cmdLoadData_Click() <snip If oSheet.Range(conCellOrderToVerify) < "" And _ InStr(oSheet.Range(conCellOrderToVerify), "Order - Verify") = 0 Then MsgBox "Input file in wrong format. Cell " & conCellOrderToVerify & _ " on Sheet1 (Summary Sheet) " & _ " should contain the text 'Order - Verify'", _ vbCritical oInputBook.Close GoTo LoadDataExit End If <snip LoadDataExit: Sheets("Start").Activate Sheets("Data").Visible = False Sheets("Lookups").Visible = False ActiveSheet.Range("A11").Select Application.ScreenUpdating = True Exit Sub <snip End Sub -- Judy Hopkins |
Exit Sub not exiting
On Nov 23, 6:12 pm, JHop wrote:
I have a very strange problem. When the following code executes and the condition is not met, it goes to LoadDataExit as expected, but then instead of stopping when it gets to the end, it goes back to cmdLoadData and executes repeatedly. This happened once before in another workbook, and at the time (being more of an Access programmer) I decided it was some kind of corruption and went back to an earlier version and applied all the code changes, which got it working again. Since it's happened again in a totally different workbook, and since that kind of fix is very tedious and not necessarily guaranteed to work, I'm wondering what could be happening and how best to fix it now and prevent it in the future. I'd appreciate any help. Private Sub cmdLoadData_Click() <snip If oSheet.Range(conCellOrderToVerify) < "" And _ InStr(oSheet.Range(conCellOrderToVerify), "Order - Verify") = 0 Then MsgBox "Input file in wrong format. Cell " & conCellOrderToVerify & _ " on Sheet1 (Summary Sheet) " & _ " should contain the text 'Order - Verify'", _ vbCritical oInputBook.Close GoTo LoadDataExit End If <snip LoadDataExit: Sheets("Start").Activate Sheets("Data").Visible = False Sheets("Lookups").Visible = False ActiveSheet.Range("A11").Select Application.ScreenUpdating = True Exit Sub <snip End Sub -- Judy Hopkins Hello Judy, I would check any worksheet event code you might have. A command button can be "clicked' in code by setting its value property. You could also add a break points to each line in LoadDataExit to step through and verify your operations. To add/remove a break point, place the cursor on the line and press F9. To step to the next statement, press F5. Sincerely, Leith Ross |
Exit Sub not exiting
Thanks, Leith, but I have stepped through it numerous times and there are no
other events that trigger it. When I step through it, I see it go from Exit Sub at the end to cmdDataLoad_Click and go through the whole thing again. Very weird. Is there such a thing as corruption in Excel VBA? -- Judy Hopkins "Leith Ross" wrote: On Nov 23, 6:12 pm, JHop wrote: I have a very strange problem. When the following code executes and the condition is not met, it goes to LoadDataExit as expected, but then instead of stopping when it gets to the end, it goes back to cmdLoadData and executes repeatedly. This happened once before in another workbook, and at the time (being more of an Access programmer) I decided it was some kind of corruption and went back to an earlier version and applied all the code changes, which got it working again. Since it's happened again in a totally different workbook, and since that kind of fix is very tedious and not necessarily guaranteed to work, I'm wondering what could be happening and how best to fix it now and prevent it in the future. I'd appreciate any help. Private Sub cmdLoadData_Click() <snip If oSheet.Range(conCellOrderToVerify) < "" And _ InStr(oSheet.Range(conCellOrderToVerify), "Order - Verify") = 0 Then MsgBox "Input file in wrong format. Cell " & conCellOrderToVerify & _ " on Sheet1 (Summary Sheet) " & _ " should contain the text 'Order - Verify'", _ vbCritical oInputBook.Close GoTo LoadDataExit End If <snip LoadDataExit: Sheets("Start").Activate Sheets("Data").Visible = False Sheets("Lookups").Visible = False ActiveSheet.Range("A11").Select Application.ScreenUpdating = True Exit Sub <snip End Sub -- Judy Hopkins Hello Judy, I would check any worksheet event code you might have. A command button can be "clicked' in code by setting its value property. You could also add a break points to each line in LoadDataExit to step through and verify your operations. To add/remove a break point, place the cursor on the line and press F9. To step to the next statement, press F5. Sincerely, Leith Ross |
Exit Sub not exiting
On Nov 23, 8:05 pm, JHop wrote:
Thanks, Leith, but I have stepped through it numerous times and there are no other events that trigger it. When I step through it, I see it go from Exit Sub at the end to cmdDataLoad_Click and go through the whole thing again. Very weird. Is there such a thing as corruption in Excel VBA? -- Judy Hopkins "Leith Ross" wrote: On Nov 23, 6:12 pm, JHop wrote: I have a very strange problem. When the following code executes and the condition is not met, it goes to LoadDataExit as expected, but then instead of stopping when it gets to the end, it goes back to cmdLoadData and executes repeatedly. This happened once before in another workbook, and at the time (being more of an Access programmer) I decided it was some kind of corruption and went back to an earlier version and applied all the code changes, which got it working again. Since it's happened again in a totally different workbook, and since that kind of fix is very tedious and not necessarily guaranteed to work, I'm wondering what could be happening and how best to fix it now and prevent it in the future. I'd appreciate any help. Private Sub cmdLoadData_Click() <snip If oSheet.Range(conCellOrderToVerify) < "" And _ InStr(oSheet.Range(conCellOrderToVerify), "Order - Verify") = 0 Then MsgBox "Input file in wrong format. Cell " & conCellOrderToVerify & _ " on Sheet1 (Summary Sheet) " & _ " should contain the text 'Order - Verify'", _ vbCritical oInputBook.Close GoTo LoadDataExit End If <snip LoadDataExit: Sheets("Start").Activate Sheets("Data").Visible = False Sheets("Lookups").Visible = False ActiveSheet.Range("A11").Select Application.ScreenUpdating = True Exit Sub <snip End Sub -- Judy Hopkins Hello Judy, I would check any worksheet event code you might have. A command button can be "clicked' in code by setting its value property. You could also add a break points to each line in LoadDataExit to step through and verify your operations. To add/remove a break point, place the cursor on the line and press F9. To step to the next statement, press F5. Sincerely, Leith Ross Hello Judy, VBA can become corrupted. In my experience, when this happens the code generally errors or it crashes Excel. If you like, I could look the workbook over for errors for you. If you have any sensitive information in the workbook, you would need to sanitize it first. My email is Sincerely, Leith Ross |
Exit Sub not exiting
What happens if ExitSub is the only cmdLoadData_Click event code, removing
all other code as a test? Does it still run repeatedly? -- Tim Zych SF, CA "JHop" wrote in message ... Thanks, Leith, but I have stepped through it numerous times and there are no other events that trigger it. When I step through it, I see it go from Exit Sub at the end to cmdDataLoad_Click and go through the whole thing again. Very weird. Is there such a thing as corruption in Excel VBA? -- Judy Hopkins "Leith Ross" wrote: On Nov 23, 6:12 pm, JHop wrote: I have a very strange problem. When the following code executes and the condition is not met, it goes to LoadDataExit as expected, but then instead of stopping when it gets to the end, it goes back to cmdLoadData and executes repeatedly. This happened once before in another workbook, and at the time (being more of an Access programmer) I decided it was some kind of corruption and went back to an earlier version and applied all the code changes, which got it working again. Since it's happened again in a totally different workbook, and since that kind of fix is very tedious and not necessarily guaranteed to work, I'm wondering what could be happening and how best to fix it now and prevent it in the future. I'd appreciate any help. Private Sub cmdLoadData_Click() <snip If oSheet.Range(conCellOrderToVerify) < "" And _ InStr(oSheet.Range(conCellOrderToVerify), "Order - Verify") = 0 Then MsgBox "Input file in wrong format. Cell " & conCellOrderToVerify & _ " on Sheet1 (Summary Sheet) " & _ " should contain the text 'Order - Verify'", _ vbCritical oInputBook.Close GoTo LoadDataExit End If <snip LoadDataExit: Sheets("Start").Activate Sheets("Data").Visible = False Sheets("Lookups").Visible = False ActiveSheet.Range("A11").Select Application.ScreenUpdating = True Exit Sub <snip End Sub -- Judy Hopkins Hello Judy, I would check any worksheet event code you might have. A command button can be "clicked' in code by setting its value property. You could also add a break points to each line in LoadDataExit to step through and verify your operations. To add/remove a break point, place the cursor on the line and press F9. To step to the next statement, press F5. Sincerely, Leith Ross |
Exit Sub not exiting
Per JHop:
Since it's happened again in a totally different workbook, and since that kind of fix is very tedious and not necessarily guaranteed to work, I'm wondering what could be happening and how best to fix it now and prevent it in the future. I'd appreciate any help. FWIW, I've seen similar situations in my own MS Access VBA. Not resulting in a loop.... but definitely branching to someplace not specified in the code.... maybe 2-3 occurrences... and they've all been at the Exit point. -- PeteCresswell |
Exit Sub not exiting
Thanks! I should have tried this (commenting out all code) right off the bat
but didn't think of it. After trying this, then deleting the subroutine, exporting the code to WordPad and copying it back into a new subroutine, everything worked fine. Then I went back to the backup copy of the workbook I had made just before making these changes, and it worked fine too! I don't understand, but at least it's working properly now. -- Judy Hopkins "Tim Zych" wrote: What happens if ExitSub is the only cmdLoadData_Click event code, removing all other code as a test? Does it still run repeatedly? -- Tim Zych SF, CA "JHop" wrote in message ... Thanks, Leith, but I have stepped through it numerous times and there are no other events that trigger it. When I step through it, I see it go from Exit Sub at the end to cmdDataLoad_Click and go through the whole thing again. Very weird. Is there such a thing as corruption in Excel VBA? -- Judy Hopkins "Leith Ross" wrote: On Nov 23, 6:12 pm, JHop wrote: I have a very strange problem. When the following code executes and the condition is not met, it goes to LoadDataExit as expected, but then instead of stopping when it gets to the end, it goes back to cmdLoadData and executes repeatedly. This happened once before in another workbook, and at the time (being more of an Access programmer) I decided it was some kind of corruption and went back to an earlier version and applied all the code changes, which got it working again. Since it's happened again in a totally different workbook, and since that kind of fix is very tedious and not necessarily guaranteed to work, I'm wondering what could be happening and how best to fix it now and prevent it in the future. I'd appreciate any help. Private Sub cmdLoadData_Click() <snip If oSheet.Range(conCellOrderToVerify) < "" And _ InStr(oSheet.Range(conCellOrderToVerify), "Order - Verify") = 0 Then MsgBox "Input file in wrong format. Cell " & conCellOrderToVerify & _ " on Sheet1 (Summary Sheet) " & _ " should contain the text 'Order - Verify'", _ vbCritical oInputBook.Close GoTo LoadDataExit End If <snip LoadDataExit: Sheets("Start").Activate Sheets("Data").Visible = False Sheets("Lookups").Visible = False ActiveSheet.Range("A11").Select Application.ScreenUpdating = True Exit Sub <snip End Sub -- Judy Hopkins Hello Judy, I would check any worksheet event code you might have. A command button can be "clicked' in code by setting its value property. You could also add a break points to each line in LoadDataExit to step through and verify your operations. To add/remove a break point, place the cursor on the line and press F9. To step to the next statement, press F5. Sincerely, Leith Ross |
Exit Sub not exiting
Thanks, Nigel. See my response to Tim Zych above.
-- Judy Hopkins "Nigel" wrote: There is no obvious problem with the code as you posted it that I can see. But the event must be being re-triggered. Have you tried running the code manually, by removing the link to a control event? Have you tried disabling events? -- Regards, Nigel "JHop" wrote in message ... Thanks, Leith, but I have stepped through it numerous times and there are no other events that trigger it. When I step through it, I see it go from Exit Sub at the end to cmdDataLoad_Click and go through the whole thing again. Very weird. Is there such a thing as corruption in Excel VBA? -- Judy Hopkins "Leith Ross" wrote: On Nov 23, 6:12 pm, JHop wrote: I have a very strange problem. When the following code executes and the condition is not met, it goes to LoadDataExit as expected, but then instead of stopping when it gets to the end, it goes back to cmdLoadData and executes repeatedly. This happened once before in another workbook, and at the time (being more of an Access programmer) I decided it was some kind of corruption and went back to an earlier version and applied all the code changes, which got it working again. Since it's happened again in a totally different workbook, and since that kind of fix is very tedious and not necessarily guaranteed to work, I'm wondering what could be happening and how best to fix it now and prevent it in the future. I'd appreciate any help. Private Sub cmdLoadData_Click() <snip If oSheet.Range(conCellOrderToVerify) < "" And _ InStr(oSheet.Range(conCellOrderToVerify), "Order - Verify") = 0 Then MsgBox "Input file in wrong format. Cell " & conCellOrderToVerify & _ " on Sheet1 (Summary Sheet) " & _ " should contain the text 'Order - Verify'", _ vbCritical oInputBook.Close GoTo LoadDataExit End If <snip LoadDataExit: Sheets("Start").Activate Sheets("Data").Visible = False Sheets("Lookups").Visible = False ActiveSheet.Range("A11").Select Application.ScreenUpdating = True Exit Sub <snip End Sub -- Judy Hopkins Hello Judy, I would check any worksheet event code you might have. A command button can be "clicked' in code by setting its value property. You could also add a break points to each line in LoadDataExit to step through and verify your operations. To add/remove a break point, place the cursor on the line and press F9. To step to the next statement, press F5. Sincerely, Leith Ross |
Exit Sub not exiting
Thanks very much for the offer. Everything seems to be working OK now--see
my reply to Tim Zych below. -- Judy Hopkins "Leith Ross" wrote: On Nov 23, 8:05 pm, JHop wrote: Thanks, Leith, but I have stepped through it numerous times and there are no other events that trigger it. When I step through it, I see it go from Exit Sub at the end to cmdDataLoad_Click and go through the whole thing again. Very weird. Is there such a thing as corruption in Excel VBA? -- Judy Hopkins "Leith Ross" wrote: On Nov 23, 6:12 pm, JHop wrote: I have a very strange problem. When the following code executes and the condition is not met, it goes to LoadDataExit as expected, but then instead of stopping when it gets to the end, it goes back to cmdLoadData and executes repeatedly. This happened once before in another workbook, and at the time (being more of an Access programmer) I decided it was some kind of corruption and went back to an earlier version and applied all the code changes, which got it working again. Since it's happened again in a totally different workbook, and since that kind of fix is very tedious and not necessarily guaranteed to work, I'm wondering what could be happening and how best to fix it now and prevent it in the future. I'd appreciate any help. Private Sub cmdLoadData_Click() <snip If oSheet.Range(conCellOrderToVerify) < "" And _ InStr(oSheet.Range(conCellOrderToVerify), "Order - Verify") = 0 Then MsgBox "Input file in wrong format. Cell " & conCellOrderToVerify & _ " on Sheet1 (Summary Sheet) " & _ " should contain the text 'Order - Verify'", _ vbCritical oInputBook.Close GoTo LoadDataExit End If <snip LoadDataExit: Sheets("Start").Activate Sheets("Data").Visible = False Sheets("Lookups").Visible = False ActiveSheet.Range("A11").Select Application.ScreenUpdating = True Exit Sub <snip End Sub -- Judy Hopkins Hello Judy, I would check any worksheet event code you might have. A command button can be "clicked' in code by setting its value property. You could also add a break points to each line in LoadDataExit to step through and verify your operations. To add/remove a break point, place the cursor on the line and press F9. To step to the next statement, press F5. Sincerely, Leith Ross Hello Judy, VBA can become corrupted. In my experience, when this happens the code generally errors or it crashes Excel. If you like, I could look the workbook over for errors for you. If you have any sensitive information in the workbook, you would need to sanitize it first. My email is Sincerely, Leith Ross |
Exit Sub not exiting
Three things come to mind. First, is it possible that in the code you
<sniped you have some error handling code that is causing the execution to jump to another location within the sub (or to a parent sub which calls the problematic code)? Second, are you ever setting the Value property of the cmdLoadData button? Doing so will cause the Click event to run. Finally, it could be that VBA's own internal code storage has become corrupted or otherwise damaged. The solution to this problem is to export all code out of VBA to plain text files, remove the code modules from the project, which causes VBA to purge its storage areas, and then import the text files into VBA, starting VBA with a clean slate. Rob Bovey (www.appspro.com) has created an Add-In called Code Cleaner 5.0 that automates the entire export/remove/import process down to a few mouse clicks. It also makes a backup copy of the workbook prior to removing the code so that in the very rare chance that something goes wrong during the export/import process. Cleaning out VBA's code storage can cure a wide range of very strange problems. See Rob's page at http://www.appspro.com/Utilities/CodeCleaner.htm for a free copy of the Code Cleaner. This add-in is a must-have for serious VBA developers. On a final note, when debugging your code, you should set error trapping to "Break In Class Module" on the General tab of the Options dialog in the VBA Editor. If an error occurs, this will break at the actual line of code that caused the problem. If the problem lies in an object module (userform, class, etc), and error trapping is set to "Break On Unhandled Errors", the code will break on the line of code that refers to the object module rather than on the actual break. As an example, suppose you have in a user form the line Debug.Print 1/0 ' obvious error And in a standard module you have UserForm1.Show. If error handling is set to "Break On Unhandled Errors", to code will break on UserForm1.Show, even though nothing is wrong with the Show method itself, rather than on the real error within the form Debug.Print 1/0. -- Cordially, Chip Pearson Microsoft MVP - Excel, 10 Years Pearson Software Consulting www.cpearson.com (email on the web site) "JHop" wrote in message ... I have a very strange problem. When the following code executes and the condition is not met, it goes to LoadDataExit as expected, but then instead of stopping when it gets to the end, it goes back to cmdLoadData and executes repeatedly. This happened once before in another workbook, and at the time (being more of an Access programmer) I decided it was some kind of corruption and went back to an earlier version and applied all the code changes, which got it working again. Since it's happened again in a totally different workbook, and since that kind of fix is very tedious and not necessarily guaranteed to work, I'm wondering what could be happening and how best to fix it now and prevent it in the future. I'd appreciate any help. Private Sub cmdLoadData_Click() <snip If oSheet.Range(conCellOrderToVerify) < "" And _ InStr(oSheet.Range(conCellOrderToVerify), "Order - Verify") = 0 Then MsgBox "Input file in wrong format. Cell " & conCellOrderToVerify & _ " on Sheet1 (Summary Sheet) " & _ " should contain the text 'Order - Verify'", _ vbCritical oInputBook.Close GoTo LoadDataExit End If <snip LoadDataExit: Sheets("Start").Activate Sheets("Data").Visible = False Sheets("Lookups").Visible = False ActiveSheet.Range("A11").Select Application.ScreenUpdating = True Exit Sub <snip End Sub -- Judy Hopkins |
Exit Sub not exiting
Hmm not sure how reimporting the code would fix it. My hope was that the
error might stop with only an Exit Sub, and that what's causing the repeating might be easier to figure out. By the way, Option Explicit should be at the top of all of the modules and everything should compile without error. Debug- Compile. -- Tim Zych SF, CA "JHop" wrote in message ... Thanks! I should have tried this (commenting out all code) right off the bat but didn't think of it. After trying this, then deleting the subroutine, exporting the code to WordPad and copying it back into a new subroutine, everything worked fine. Then I went back to the backup copy of the workbook I had made just before making these changes, and it worked fine too! I don't understand, but at least it's working properly now. -- Judy Hopkins "Tim Zych" wrote: What happens if ExitSub is the only cmdLoadData_Click event code, removing all other code as a test? Does it still run repeatedly? -- Tim Zych SF, CA "JHop" wrote in message ... Thanks, Leith, but I have stepped through it numerous times and there are no other events that trigger it. When I step through it, I see it go from Exit Sub at the end to cmdDataLoad_Click and go through the whole thing again. Very weird. Is there such a thing as corruption in Excel VBA? -- Judy Hopkins "Leith Ross" wrote: On Nov 23, 6:12 pm, JHop wrote: I have a very strange problem. When the following code executes and the condition is not met, it goes to LoadDataExit as expected, but then instead of stopping when it gets to the end, it goes back to cmdLoadData and executes repeatedly. This happened once before in another workbook, and at the time (being more of an Access programmer) I decided it was some kind of corruption and went back to an earlier version and applied all the code changes, which got it working again. Since it's happened again in a totally different workbook, and since that kind of fix is very tedious and not necessarily guaranteed to work, I'm wondering what could be happening and how best to fix it now and prevent it in the future. I'd appreciate any help. Private Sub cmdLoadData_Click() <snip If oSheet.Range(conCellOrderToVerify) < "" And _ InStr(oSheet.Range(conCellOrderToVerify), "Order - Verify") = 0 Then MsgBox "Input file in wrong format. Cell " & conCellOrderToVerify & _ " on Sheet1 (Summary Sheet) " & _ " should contain the text 'Order - Verify'", _ vbCritical oInputBook.Close GoTo LoadDataExit End If <snip LoadDataExit: Sheets("Start").Activate Sheets("Data").Visible = False Sheets("Lookups").Visible = False ActiveSheet.Range("A11").Select Application.ScreenUpdating = True Exit Sub <snip End Sub -- Judy Hopkins Hello Judy, I would check any worksheet event code you might have. A command button can be "clicked' in code by setting its value property. You could also add a break points to each line in LoadDataExit to step through and verify your operations. To add/remove a break point, place the cursor on the line and press F9. To step to the next statement, press F5. Sincerely, Leith Ross |
Exit Sub not exiting
Per "Tim Zych" <tzych@NOSp@mE@RTHLINKDOTNET:
Hmm not sure how reimporting the code would fix it. My hope was that the error might stop with only an Exit Sub, and that what's causing the repeating might be easier to figure out. Unencumbered by any real knowledge, I'd hope that re-importing the code would ensure that everything about that module was 100% recompiled. -- PeteCresswell |
Exit Sub not exiting
I agree. That is what I wonder, that a compilation at import-time is fixing
the problem, and the bug may occur during runtime only, which might be caught using Option Explicit and compilation ahead of time. -- Tim Zych SF, CA "(PeteCresswell)" wrote in message ... |
| All times are GMT +1. The time now is 07:05 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com