On error run subroutine

[Mumba]

Hi All

Is it, in any way possible to call a subroutine on any error during a macro?
I created a macro that does some calculations, afterwards data sheets are hidden as veryhidden rendering them invisible for the user.
However on errors the macro breaks and leaves the sheets visible to the user. I am trying to avoid this.

I am thinking something along the lines of this:

On error call SubHideSheets
end

 

[dmt32]

Hi,
Possible to manage errors in your routine but would be helpful if you publish the macro that you are having issues with.

Dave

 

[Mumba]

Hi Dave

No problem.
I have several subcodes so i figured it would be more confusing.
What i am looking for i as way to do a total code fail handling. From my understanding On error is i line specific error handling that needs to be above every calculation in the macro, i would very much like to avoid that.
What i would like is this: If at any point the macro breaks down, it should jump to the last subroutine (Call Sheetshide) and execute this before ending the macro (of course be using the "End" functionality)

Sub Main()

Application.ScreenUpdating = False

Call Definitions

'Set total to 0
wsmaster.Cells(7, "E") = "Please chose type in column ""H"""


Call SheetsUnhide
Call Headlines
Call FindRiisItem
Call ReadyDetails
Call ReadyRefreshCost
Call OptimalChoice

wsmaster.Activate
Call formats
Call Sheetshide

Application.ScreenUpdating = True
End Sub

 

[Norie]

What error(s) are you trying to handle?

 

[dmt32]

You could try following addition to your code & see if does what you want

Sub Main()


    On Error GoTo myerror
    Application.ScreenUpdating = False
    
    Call Definitions
    
    'Set total to 0
    wsmaster.Cells(7, "E") = "Please chose type in column ""H"""
    
    
    Call SheetsUnhide
    Call Headlines
    Call FindRiisItem
    Call ReadyDetails
    Call ReadyRefreshCost
    Call OptimalChoice
    
    wsmaster.Activate
    Call formats
    
    
myerror:
    Call Sheetshide
    Application.ScreenUpdating = True
    If Err <> 0 Then MsgBox (Error(Err)), 48, "Error"
End Sub

Dave

 

[Mumba]

Any errors that may occour. This a tool for external use. If the user somehow manages to insert a input that will cause an error, i want the macro to hide the datasheets and show a msgbox that asks them to contact a inserted support contact.
I am aware this is a VERY broad approach and that is why i am unsure if it is even possible.

 

[Mumba]

Hi Dave

I seems that it does.
I am actually a bit surprised, when reading about the "on error" functionality it seems that it should not be possible to do it like this.. But it works

Thank you

 

[dmt32]

Hi Dave

I seems that it does.
I am actually a bit surprised, when reading about the "on error" functionality it seems that it should not be possible to do it like this.. But it works

Thank you

Perfectly possible - save me writing it all out & explaining it, visit Chip Pearson site::http://www.cpearson.com/excel/errorhandling.htm
for full explantion of error Handling

Extract from Chips site relating to your situation below:

Error Handling With Multiple Procedures

Every procedure need not have a error code. When an error occurs, VBA uses the last On Error statement to direct code execution. If the code causing the error is in a procedure with an On Error statement, error handling is as described in the above section.

However, if the procedure in which the error occurs does not have an error handler, VBA looks backwards through the procedure calls which lead to the erroneous code.

For example if procedure A calls B and B calls C, and A is the only procedure with an error handler, if an error occurs in procedure C, code execution is immediately transferred to the error handler in procedure A, skipping the remaining code in B.

Dave