'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.
 

Some videos you may like

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

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
 

Watch MrExcel Video

Forum statistics

Threads
1,119,127
Messages
5,576,244
Members
412,709
Latest member
Rishu
Top