Error Handling and Cleanup

[Shatin]

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.

 

[Rob van Gelder]

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

 

[Patrick Molloy]

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

 

[Shatin]

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

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

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.

 

[Shatin]

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

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

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.

 

[onedaywhen]

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

 

[Rob van Gelder]

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

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

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


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.