Error Handling and Cleanup
 

In writing a macro, I've read that it's good programming practice to clean
up after the macro is run, by that I mean resetting
application.screenupdating, calculation modes to their original states, that
sort of thing. Now in the case of there being some code for error handling,
that code is typically placed at the end of the macro:

Sub xxx()

main code
If error then goto Error_handler
clean up code
Exit sub

Error_handler:
code
Exit sub

End sub

There's a chance that after handling the error, the macro may be exited
without there being any cleanup. I suppose one can repeat the cleanup code
in the error handler. However, if there are more than a few errorhandlers to
deal with different types of errors, it would be clumsy to repeat the same
cleanup code again and again. How should this problem be dealt with?

TIA.

Error Handling and Cleanup
 

Resume [label] is what you're after:

Sub test()
Dim i As Long

Application.StatusBar = "Attempting Divide by Zero..."

On Error GoTo e
i = 16 / 0

c: Application.StatusBar = False
Exit Sub

e: Debug.Print Err.Description
Resume c
End Sub


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Shatin" wrote in message
...
In writing a macro, I've read that it's good programming practice to clean
up after the macro is run, by that I mean resetting
application.screenupdating, calculation modes to their original states,
that
sort of thing. Now in the case of there being some code for error
handling,
that code is typically placed at the end of the macro:

Sub xxx()

main code
If error then goto Error_handler
clean up code
Exit sub

Error_handler:
code
Exit sub

End sub

There's a chance that after handling the error, the macro may be exited
without there being any cleanup. I suppose one can repeat the cleanup code
in the error handler. However, if there are more than a few errorhandlers
to
deal with different types of errors, it would be clumsy to repeat the same
cleanup code again and again. How should this problem be dealt with?

TIA.

Error Handling and Cleanup
 

Class is your friend here

In a class module ( call it clsSettings)
Use the initialise method to save excel's current status
and set them to to the default values for when the code
runs. Set the class's Terminate method to restore Excel's
settings to the values stored on initialising

Now you can do somthing like this

DIM MySettings as clsSettings
set MySettings = New clsSettings
Call XXX ' XXX the name of your procedure
' "call" not req'd but makes it "readable"
Set Mysettings = Nothing




Patrick Molloy
Microsoft Excel MVP




-----Original Message-----
In writing a macro, I've read that it's good programming
practice to clean
up after the macro is run, by that I mean resetting
application.screenupdating, calculation modes to their
original states, that
sort of thing. Now in the case of there being some code
for error handling,
that code is typically placed at the end of the macro:

Sub xxx()

main code
If error then goto Error_handler
clean up code
Exit sub

Error_handler:
code
Exit sub

End sub

There's a chance that after handling the error, the
macro may be exited
without there being any cleanup. I suppose one can
repeat the cleanup code
in the error handler. However, if there are more than a
few errorhandlers to
deal with different types of errors, it would be clumsy
to repeat the same
cleanup code again and again. How should this problem be
dealt with?

TIA.


.

Error Handling and Cleanup
 

Rob,

I tried to do what you advised but got the following error message:

Runtime error "20":
Resume with error

I don't understand what this is about. My code is as follows:

Cleanup:

Application.DisplayAlerts = True
Application.StatusBar = False
Application.ScreenUpdating = True

Exit Sub

Error_handler:
MsgBox ("You didn't enter anything. Please run macro again.")
Resume Cleanup

End Sub

"Rob van Gelder" wrote in message
...
Resume [label] is what you're after:

Sub test()
Dim i As Long

Application.StatusBar = "Attempting Divide by Zero..."

On Error GoTo e
i = 16 / 0

c: Application.StatusBar = False
Exit Sub

e: Debug.Print Err.Description
Resume c
End Sub


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Shatin" wrote in message
...
In writing a macro, I've read that it's good programming practice to
clean
up after the macro is run, by that I mean resetting
application.screenupdating, calculation modes to their original states,
that
sort of thing. Now in the case of there being some code for error
handling,
that code is typically placed at the end of the macro:

Sub xxx()

main code
If error then goto Error_handler
clean up code
Exit sub

Error_handler:
code
Exit sub

End sub

There's a chance that after handling the error, the macro may be exited
without there being any cleanup. I suppose one can repeat the cleanup
code
in the error handler. However, if there are more than a few
errorhandlers
to
deal with different types of errors, it would be clumsy to repeat the
same
cleanup code again and again. How should this problem be dealt with?

TIA.

Error Handling and Cleanup
 

Rob,

I think I understand what the problem is. I have always thought an error is
something which either I or VBA regard as not right. Thus, in the case
below, I deliberately didn't enter any info into an input box. The macro
will still run all the same, but to me that's an error. However, apparently,
to VBA, this is not an error. The "Resume" command can only be used when
what VBA regards errors occur.

Rob,

I tried to do what you advised but got the following error message:

Runtime error "20":
Resume with error

I don't understand what this is about. My code is as follows:

Cleanup:

Application.DisplayAlerts = True
Application.StatusBar = False
Application.ScreenUpdating = True

Exit Sub

Error_handler:
MsgBox ("You didn't enter anything. Please run macro again.")
Resume Cleanup

End Sub

"Rob van Gelder" wrote in message
...
Resume [label] is what you're after:

Sub test()
Dim i As Long

Application.StatusBar = "Attempting Divide by Zero..."

On Error GoTo e
i = 16 / 0

c: Application.StatusBar = False
Exit Sub

e: Debug.Print Err.Description
Resume c
End Sub


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Shatin" wrote in message
...
In writing a macro, I've read that it's good programming practice to
clean
up after the macro is run, by that I mean resetting
application.screenupdating, calculation modes to their original
states,
that
sort of thing. Now in the case of there being some code for error
handling,
that code is typically placed at the end of the macro:

Sub xxx()

main code
If error then goto Error_handler
clean up code
Exit sub

Error_handler:
code
Exit sub

End sub

There's a chance that after handling the error, the macro may be
exited
without there being any cleanup. I suppose one can repeat the cleanup
code
in the error handler. However, if there are more than a few
errorhandlers
to
deal with different types of errors, it would be clumsy to repeat the
same
cleanup code again and again. How should this problem be dealt with?

TIA.

Error Handling and Cleanup
 

Put the clean up code in a separate sub procedure and call it from
both the main code and the error handler:

Sub CleanUp()
' <clean up code here
End Sub

Sub xxx()
If error then goto Error_handler
' <main code here
CleanUp
Exit sub
Error_handler:
CleanUp
End sub

--

"Shatin" wrote in message ...
In writing a macro, I've read that it's good programming practice to clean
up after the macro is run, by that I mean resetting
application.screenupdating, calculation modes to their original states, that
sort of thing. Now in the case of there being some code for error handling,
that code is typically placed at the end of the macro:

Sub xxx()

main code
If error then goto Error_handler
clean up code
Exit sub

Error_handler:
code
Exit sub

End sub

There's a chance that after handling the error, the macro may be exited
without there being any cleanup. I suppose one can repeat the cleanup code
in the error handler. However, if there are more than a few errorhandlers to
deal with different types of errors, it would be clumsy to repeat the same
cleanup code again and again. How should this problem be dealt with?

TIA.

Error Handling and Cleanup
 

You could use the Err.Raise method to generate an error.

Sub test()
Dim i As Long

Application.StatusBar = "Attempting Input Error..."

On Error GoTo e
Err.Raise Number:=513, Description:="Input Error"

c: Application.StatusBar = False
Exit Sub

e: Debug.Print Err.Description
Resume c
End Sub


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Shatin" wrote in message
...
Rob,

I think I understand what the problem is. I have always thought an error
is
something which either I or VBA regard as not right. Thus, in the case
below, I deliberately didn't enter any info into an input box. The macro
will still run all the same, but to me that's an error. However,
apparently,
to VBA, this is not an error. The "Resume" command can only be used when
what VBA regards errors occur.

Rob,

I tried to do what you advised but got the following error message:

Runtime error "20":
Resume with error

I don't understand what this is about. My code is as follows:

Cleanup:

Application.DisplayAlerts = True
Application.StatusBar = False
Application.ScreenUpdating = True

Exit Sub

Error_handler:
MsgBox ("You didn't enter anything. Please run macro again.")
Resume Cleanup

End Sub

"Rob van Gelder" wrote in
message
...
Resume [label] is what you're after:

Sub test()
Dim i As Long

Application.StatusBar = "Attempting Divide by Zero..."

On Error GoTo e
i = 16 / 0

c: Application.StatusBar = False
Exit Sub

e: Debug.Print Err.Description
Resume c
End Sub


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Shatin" wrote in message
...
In writing a macro, I've read that it's good programming practice to
clean
up after the macro is run, by that I mean resetting
application.screenupdating, calculation modes to their original
states,
that
sort of thing. Now in the case of there being some code for error
handling,
that code is typically placed at the end of the macro:

Sub xxx()

main code
If error then goto Error_handler
clean up code
Exit sub

Error_handler:
code
Exit sub

End sub

There's a chance that after handling the error, the macro may be
exited
without there being any cleanup. I suppose one can repeat the
cleanup
code
in the error handler. However, if there are more than a few
errorhandlers
to
deal with different types of errors, it would be clumsy to repeat
the
same
cleanup code again and again. How should this problem be dealt with?

TIA.