'On Exit' routine?


Board Regular
Oct 30, 2003
Is there a way to specify code to run if macro is exited, for instance, during a debug? I know it's kind of backwards, since the point of stopping execution is to stop execution, but wondering if there is a way to specify some cleanup code even if the rest of the code isn't working (something along the lines of 'on exit'.

In a certain case i use the status bar to display messages. If the code displays an error, or i'm stepping though it with F8 and stop execution, the status bar is left with it's current message. Also, of course, are other things like applications that don't get killed, etc.

I know an 'on error' routine would work for actually handling errors, and maybe I'm just completely avoiding reality by not using that exclusively, but i was looking more towards a quick 'always run thins code before exiting no matter what' kind of solution.

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
I think you're avoiding reality. But you could try this:

Structure your code so that your 'tidy-up' code is at the end of your 'Main' code. This means it will always run if the code runs successfuly.

If you put your error routines BEFORE this code, then it will also run after an error has been trapped (even if you just ignore the error, in which case this code IS your response).

The important point is that your 'tidy-up' code anticipates the results of any errors and doesn't spit the dummy if something unexpected happens (like a loop).

I do this kind of thing to remove any temporary worksheets I've created during the macro running. My 'tidy-up' code removes all worksheets except those I specify.

Upvote 0
Right. In the short time since I posted this question (perhaps while I was posting it) i did decide the error handling routine was probably the way to go. The one holdout I have (not really, since I'm implementing it anyway) is how to tell where the error occurred. I understand that I can get to this information with the code i'm using (by using the resume next it takes me to the 'next' line that would have executed), but the benefit of letting the code error on it's own is that debug takes you directly to the offending line. With error handling code, you kind of lose that info.

Below is the example code I'm planning on using as a template for subs (pardon the statusbar stuff, but it's kind of handy). Pretty much in line with your suggestions, as it turns out I would love to hear of any tweaks or suggestions. Especially anything that could return the line number, or something similar, where the error occurred.

Sub template_w_error_handling()

On Error GoTo err_handle
oldStatusBar = Application.DisplayStatusBar
Application.DisplayStatusBar = True
Application.StatusBar = "In Progress..."


    'Contents Go Here.....


GoTo endsub
Application.StatusBar = "Error..."
x = MsgBox("Error Occurred in procedure: " & Chr(10) & _
    "   Error Number       : " & Err.Number & Chr(10) & _
    "   Error Description  : " & Err.Description & Chr(10) & _
    "   Error Source        : " & Err.Source & Chr(10) & _
    " " & Chr(10) & " Debug...?", vbYesNo, "Error Handling")
If x = vbYes Then
    Application.StatusBar = "Debug..."
    Resume Next
End If

    'Clean Up Code Goes Here.....
Application.StatusBar = False
Application.DisplayStatusBar = oldStatusBar

End Sub

Thanks for the reply,
Upvote 0

Yes, I can see what you're after. Beyond me 'though. I can only see the very painful method of adding lines that act as a counter, although you could perhaps make this counter 'chunky' so it only records the subroutine name and perhaps every 5 or 10 lines within the routine. There must be a better way.

Upvote 0

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back