'On Exit' routine?

dnickelson

Board Regular
Joined
Oct 30, 2003
Messages
118
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

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

Robert2100

New Member
Joined
May 26, 2003
Messages
38
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.

Bob
 

dnickelson

Board Regular
Joined
Oct 30, 2003
Messages
118
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.

Code:
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
err_handle:
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..."
    Stop
    Resume Next
End If

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


End Sub

Thanks for the reply,
-Dan
 

Robert2100

New Member
Joined
May 26, 2003
Messages
38
Dan,

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.

Bob
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,624
Messages
5,832,742
Members
430,160
Latest member
a_majda

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
Top