Run Sub before macro Ends like Finally block

Question

Is there some VBA best practice to use something like 'before end' subroutines?

I am changing Excel's default configs when my macro starts, and before my macro reaches its 'end sub' line I am resetting the configs to its standards. But what if some error occurs? Am I supposed to define 'On Error' treatment inside all subs to reset the configs to the standard properties?

Just for example, I am changing configs such as:

ScreenUpdating
DisplayStatusBar
Calculation
DisplayAlerts

I couldn't find a proper duplicate of this question, but you can see [my answer here](http://stackoverflow.com/a/23350327/3198973). By resuming at the `ExitSub:` label, you can ensure whatever necessary clean up code is executed. — RubberDuck, May 28 '14 at 13:55
This one looks like the duplicate you were looking for: [Do we need to create a error handler for each subroutine?](http://stackoverflow.com/questions/6149273/do-we-need-to-create-a-error-handler-for-each-subroutine) — Jean-François Corbett, May 28 '14 at 14:00
I changed the Question tittle to better fit my purpose, and to better differentiate it from the one you suggested. — jSpeciale, May 28 '14 at 14:23
possible duplicate of [Good Patterns For VBA Error Handling](http://stackoverflow.com/questions/1038006/good-patterns-for-vba-error-handling) — RubberDuck, May 28 '14 at 14:49

score 4 · Answer 1 · edited May 23 '17 at 12:19

4

I'm pretty sure there is no such mechanism that is called unconditionally before exiting a function or a subroutine. You may have though error handlers (but these are executed conditionally; see the comment of ckuhn203 for an example).

However, there is such a mechanism for instances of Class Modules (i.e. for objects). When an object is destroyed (this happens when is not referenced anymore by any variable/storage), its Class_Terminate subroutine is called no-matter-what. If you can wrap your task in such an object that you discard immediately after you create it, you could ~~override~~ overwrite this subroutine to do the cleanup.

edited May 23 '17 at 12:19

Community

1
1

answered May 28 '14 at 14:09

1

+1 I like this solution. As for discarding it immediately, you could use the `With` statement, E.g., `With new UtilityClass` `.DoStuff()` `End With` – LimaNightHawk May 28 '14 at 14:37
Knocking after 6 years, can you please add code to this post / share reference to a code that implements this? – Karthick Ganesan Sep 15 '20 at 18:28

LimaNightHawk · Accepted Answer · 2014-05-28T14:02:57.337

If I understand your question correctly, yes, the best way is to define an On Error Goto line, in each method where it's needed, like this:

Public Sub DoSomething()

    On Error GoTo Finally ' Try
    Application.ScreenUpdating = False

    ' Do your stuff here

Finally:
    Application.ScreenUpdating = True


End Sub

This will ensure the things like ScreenUpdating get done even if there is an error. You can also add a catch block, like this:

Public Sub DoSomething()

    On Error GoTo Catch ' Try
    Application.ScreenUpdating = False

    ' Do normal stuff here

    GoTo Finally
Catch:

' Do only error stuff here

Finally:
    Application.ScreenUpdating = True
End Sub

Generally speaking, GoTo is a hated practice, but for error catching, VBA kind of forces your hand.

Im accepting your answer, in combination with @ckuhn203 comment and this link i found [link] So what im gonna do is use ckuhn's error handler, but restoring the default On Error treatment just after that. [link]: http://stackoverflow.com/questions/1038006/good-patterns-for-vba-error-handling — jSpeciale, May 28 '14 at 14:06

Run Sub before macro Ends like Finally block

2 Answers2