What is good practice for writing closing statements in the end of procedures and in error handlers?

Question

I find myself writing some commands both at the end of procedures and in error handlers. On top of it all I find myself writing these lines in all of my functions:

    Application.Cursor = xlDefault
    Application.ScreenUpdating = True

ErrHandler:
    MsgBox ("An unforseen problem has occured. Please contact support.")
    Application.Cursor = xlDefault
    Application.ScreenUpdating = True
End Sub

I'm feeling like I'm repeating myself more than I should. Is there any better practice for writing these kinds of "standard" lines at the end of procedures and in error handlers?

possible duplicate of [Good Patterns For VBA Error Handling](http://stackoverflow.com/questions/1038006/good-patterns-for-vba-error-handling) — RubberDuck, Oct 29 '14 at 13:29
possible duplicate of [Error Handler - Exit Sub vs. End Sub](http://stackoverflow.com/questions/1377152/error-handler-exit-sub-vs-end-sub) — Jean-François Corbett, Apr 08 '15 at 07:44

score 4 · Answer 1 · answered Oct 29 '14 at 12:16

4

You can simply have your error handler resume at a 'cleanup' section of code that is also executed when no error occurs:

   Clean_up:
        Application.Cursor = xlDefault
        Application.ScreenUpdating = True
        Exit Sub
    ErrHandler:
        MsgBox "An unforeseen problem has occurred. Please contact support."
        Resume Clean_up
    End Sub

If you always have that code in your routines, you could move it to a separate routine that you call from the Clean_up section.

answered Oct 29 '14 at 12:16

Rory

32,730
5
32
35

+ 1 I also prefer this than to a `Class` as mentioned in the accepted answer because if the code breaks the `Resume Clean_up` can do the relevant cleanups. Very effective when using against events like `Application.EnableEvents`!!! – Siddharth Rout Oct 29 '14 at 14:19

score 1 · Accepted Answer · edited May 23 '17 at 11:59

1

One way to simplify this is to use the RAII pattern: i.e. write a class that does the clean up in its Class_Terminate event procedure. For example, you could create class module "CursorSaver" that contains:

Private m_SavedCursor As XlMousePointer
Private m_SavedScreenUpdating As Boolean

Private Sub Class_Initialize()
    m_SavedCursor = Application.Cursor
    m_SavedScreenUpdating = Application.ScreenUpdating
End Sub

Private Sub Class_Terminate()
    Application.Cursor = m_SavedCursor
    Application.ScreenUpdating = m_SavedScreenUpdating
End Sub

You can then create an instance of this class at the start of your subs and functions, and when the function exits, the class instance will go out of scope and the terminate code will automatically be called and restore the initial state:

Public Sub MySub
    Dim saver As New CursorSaver
    ...
    Exit Sub
End Sub ' Cursor and ScreenUpdating are automatically restored when the Sub exits

This answer to another question contains a similar example.

edited May 23 '17 at 11:59

Community

1
1

answered Oct 29 '14 at 12:47

Joe

122,218
32
205
338

This is fine if you have a dedicated `ErrorHandler` class or something, but I would be upset to find some class was "silently" altering the application state. What if for some reason I `Set saver = Nothing` before the sub exits? – RubberDuck Oct 29 '14 at 13:31
@RubberDuck - not sure I understand your concerns. This pattern would be a class that is explicitly designed to save/restore state when it goes out of scope: so it's not "silent". If you "Set saver = Nothing", you will explicitly restore the saved state prior to the sub exiting. – Joe Oct 29 '14 at 20:59
That's exactly my concern. It's fine so long as it's utterly clear that this isn't a coding pattern, but a dedicated class to handle it. As it is, I don't feel your answer calls that out. – RubberDuck Oct 29 '14 at 21:01
@RubberDuck - RAII is a coding pattern with wider applicability than just saving/restoring state. E.g. encapsulating calls from VBA to the Win32 API in a class makes cleanup such as releasing handles much simpler. Like anything, it can be abused, but I'm not sure that needs to be explicitly called out. – Joe Oct 30 '14 at 11:17
I'm not saying that this pattern doesn't have it's place. It's just my *opinion* that for this use case, it's abusive and a big WTF for any future maintainer. We'll have to agree to disagree is all. Lots of ways to skin a cat. – RubberDuck Oct 30 '14 at 11:36

score 0 · Answer 3 · answered Nov 20 '15 at 18:35

I also find myself repeatedly adding similar error handling code to my procedures and it can get a bit tedious to say the least. There is a VBIDE addin called MZTools that has the ability to add code snippets that you can easily embed in your code, including an error handler. The snippets allow the inclusion of placeholders, such as procedure name, so it can adapt to the target procedure. Earlier editions were free, but v8 does cost money, and I think it is a tad expensive, but it has lots of other functions, it is an option for you.

This is an example error handler, you can see how the placeholders work

Dim mpSettings As ApplicationSettings
Dim mpTopLevel As Boolean

    Const mpProcedure As String = "{PROCEDURE_NAME}"

    On Error GoTo {PROCEDURE_NAME}_Error
    'can be a top-level call or initiated by another
    mpTopLevel = IIf(IsArrayAllocated(mgVecProcStack), False, True)
    PushProcedureStack mpProcedure, mpTopLevel

    {PROCEDURE_NAME}= True

    Call AppSettings(State:="Set", _
                     AppType:=mpSettings, _
                     AppEvents:=True, _
                     AppScreen:=True, _
                     AppAlerts:=False, _
                     AppCalc:=appNotEnabled)

    {PROCEDURE_BODY}

{PROCEDURE_NAME}_Tidy:
    PopProcedureStack      

{PROCEDURE_NAME}_Tear_Down:               

{PROCEDURE_NAME}_Exit:
    Call AppSettings(State:="Reset", _
                     AppType:=mpSettings, _
                     AppEvents:=True, _
                     AppScreen:=True, _
                     AppAlerts:=False, _
                     AppCalc:=appNotEnabled)
    Exit Function

{PROCEDURE_NAME}_Error:
    If Err.Number = mgBypassErrorNum Then Resume {PROCEDURE_NAME}_Tear_Down
    {PROCEDURE_NAME} = False
    If AppErrorHandler(mmModule, mpProcedure, mpTopLevel) Then
        Stop
        Resume
    Else
        Resume {PROCEDURE_NAME}_Exit
    End If

I have no connection with MZTools or the author, it is just a tool that I use and have gained value from.

What is good practice for writing closing statements in the end of procedures and in error handlers?

3 Answers3