Exit Sub not exiting

[JHop]

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

 

[Leith Ross]

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

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

 

[JHop]

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?

 

[Leith Ross]

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?

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 (e-mail address removed)

Sincerely,
Leith Ross

 

[Tim Zych]

What happens if ExitSub is the only cmdLoadData_Click event code, removing
all other code as a test? Does it still run repeatedly?

 

[Nigel]

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
(e-mail address removed)

 

[(PeteCresswell)]

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.

 

[JHop]

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.

 

[JHop]

Thanks, Nigel. See my response to Tim Zych above.

 

[JHop]

Thanks very much for the offer. Everything seems to be working OK now--see
my reply to Tim Zych below.

 

[Chip Pearson]

Three things come to mind. First, is it possible that in the code you
<snip>ed 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)

 

[Tim Zych]

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.

 

[(PeteCresswell)]

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.

 

[Tim Zych]

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.