Excel 2002, WinXP

I have seen dire warnings in these newsgroups about using the "End"
statement to stop all code execution and I understand the reasons for the
warnings and have never used it. But I have a unique instance where it
would be very handy to use it and I think I am safe in doing so, but I want
to pass it by some of you to see if I am overlooking something.

The program consists of looping through some 150 sheets and
performing some 30 different procedures on each sheet and everything is
working well. Five of these procedures call the "SetDest" procedure. The
SetDest procedure searches for some text in the sheet. The problem comes
about if that text cannot be found in that sheet. The OP I am helping wants
the code to stop if that occurs and revert back to the file as it was before
anything was done to it by this program.

What I propose to do is put the following in the SetDest macro: (Ignore
syntax)

If ..cannot be found Then

MsgBox "Explain what happened & to which sheet and what will
happen now."

ActiveWorkbook.Close (display alerts = False)

TheFile.Open (same file)

End

End If



Do any of you see me stepping in something I don't like?

Can I add some code to make it safer?

Should I close and reopen Excel and then open TheFile?

Thanks for your help. Otto

instead of End, I think you are looking for Exit

Matt Chen
Blue Ridge Telecom

"Otto Moehrbach" wrote:

Excel 2002, WinXP

I have seen dire warnings in these newsgroups about using the "End"
statement to stop all code execution and I understand the reasons for the
warnings and have never used it. But I have a unique instance where it
would be very handy to use it and I think I am safe in doing so, but I want
to pass it by some of you to see if I am overlooking something.

The program consists of looping through some 150 sheets and
performing some 30 different procedures on each sheet and everything is
working well. Five of these procedures call the "SetDest" procedure. The
SetDest procedure searches for some text in the sheet. The problem comes
about if that text cannot be found in that sheet. The OP I am helping wants
the code to stop if that occurs and revert back to the file as it was before
anything was done to it by this program.

What I propose to do is put the following in the SetDest macro: (Ignore
syntax)

If ..cannot be found Then

MsgBox "Explain what happened & to which sheet and what will
happen now."

ActiveWorkbook.Close (display alerts = False)

TheFile.Open (same file)

End

End If



Do any of you see me stepping in something I don't like?

Can I add some code to make it safer?

Should I close and reopen Excel and then open TheFile?

Thanks for your help. Otto

Matt
No, I am looking for End. Exit Sub stops all code in that one macro.
End stops all code, period. I am looking for a way to back out of several
layers of code and do it in any of about five different places in the code.
End stops all further code execution wherever it is placed in the code.
Thanks anyway. Otto
"Matt - Data Manager - Blue Ridge Telecom"
soft.com wrote in message
...
instead of End, I think you are looking for Exit

Matt Chen
Blue Ridge Telecom

"Otto Moehrbach" wrote:

Excel 2002, WinXP

I have seen dire warnings in these newsgroups about using the "End"
statement to stop all code execution and I understand the reasons for
the
warnings and have never used it. But I have a unique instance where it
would be very handy to use it and I think I am safe in doing so, but I
want
to pass it by some of you to see if I am overlooking something.

The program consists of looping through some 150 sheets and
performing some 30 different procedures on each sheet and everything is
working well. Five of these procedures call the "SetDest" procedure.
The
SetDest procedure searches for some text in the sheet. The problem
comes
about if that text cannot be found in that sheet. The OP I am helping
wants
the code to stop if that occurs and revert back to the file as it was
before
anything was done to it by this program.

What I propose to do is put the following in the SetDest macro: (Ignore
syntax)

If ..cannot be found Then

MsgBox "Explain what happened & to which sheet and what will
happen now."

ActiveWorkbook.Close (display alerts = False)

TheFile.Open (same file)

End

End If



Do any of you see me stepping in something I don't like?

Can I add some code to make it safer?

Should I close and reopen Excel and then open TheFile?

Thanks for your help. Otto

Otto,
It seems to me that what you need is to re-design your programming flow.
If there's a condition that has to meet before the changes are applied then
why not check for that condition or conditions before you perform any actions.


"Otto Moehrbach" wrote:

Matt
No, I am looking for End. Exit Sub stops all code in that one macro.
End stops all code, period. I am looking for a way to back out of several
layers of code and do it in any of about five different places in the code.
End stops all further code execution wherever it is placed in the code.
Thanks anyway. Otto
"Matt - Data Manager - Blue Ridge Telecom"
soft.com wrote in message
...
instead of End, I think you are looking for Exit

Matt Chen
Blue Ridge Telecom

"Otto Moehrbach" wrote:

Excel 2002, WinXP

I have seen dire warnings in these newsgroups about using the "End"
statement to stop all code execution and I understand the reasons for
the
warnings and have never used it. But I have a unique instance where it
would be very handy to use it and I think I am safe in doing so, but I
want
to pass it by some of you to see if I am overlooking something.

The program consists of looping through some 150 sheets and
performing some 30 different procedures on each sheet and everything is
working well. Five of these procedures call the "SetDest" procedure.
The
SetDest procedure searches for some text in the sheet. The problem
comes
about if that text cannot be found in that sheet. The OP I am helping
wants
the code to stop if that occurs and revert back to the file as it was
before
anything was done to it by this program.

What I propose to do is put the following in the SetDest macro: (Ignore
syntax)

If ..cannot be found Then

MsgBox "Explain what happened & to which sheet and what will
happen now."

ActiveWorkbook.Close (display alerts = False)

TheFile.Open (same file)

End

End If



Do any of you see me stepping in something I don't like?

Can I add some code to make it safer?

Should I close and reopen Excel and then open TheFile?

Thanks for your help. Otto