Error Handling in Excel VBA Outside of Sub

Question

I know my title is a bit confusing, but this is the problem I have. I basically have one sheet that functions as a wrapper of sorts. It has the ability to run a multitude of macros sequentially, which is done through a Userform. Basically, you check the box, if it's checked, it runs the macro. What I'm trying to do is, if there is an error, I want it to return either what sub it was in.

My first idea was in my if statement for the checkboxes that run the subs to put an On Error statement, but that didn't work, since the error handling goes to the sub that is called and ignores what is before it.

What should I do? Is this possible?

If the called sub handles the error, there is nothing you can do. If the called sub lets you see the error, you can handle it as if it was raised by your code. I can't see what is the problem here. — GSerg
– GSerg, Commented Sep 1, 2016 at 21:03

Coolshaikh · Accepted Answer · 2016-09-01 21:12:06Z

6

You could do something like this:

Sub ErrorHandler()
  On Error GoTo ErrHandler
    Call Proc1
    Call Proc2
    Call Proc3
  Exit Sub
ErrHandler:
    MsgBox Err.Source & vbCrLf & Err.Description
End Sub


Sub Proc1()
    On Error GoTo ErrHandler
    ' Your code block start

    ' Your code block end
  Exit Sub
ErrHandler:
    Err.Raise 513, "Proc1", "Customer Error Message 1|" & Err.Description
End Sub


Sub Proc2()
    On Error GoTo ErrHandler
    ' Your code block start

    ' Your code block end
  Exit Sub
ErrHandler:
    Err.Raise 513, "Proc2", "Customer Error Message 2|" & Err.Description
End Sub


Sub Proc3()
    On Error GoTo ErrHandler
    ' Your code block start

    ' Your code block end
  Exit Sub
ErrHandler:
    Err.Raise 513, "Proc3", "Customer Error Message 3|" & Err.Description
End Sub

edited Sep 1, 2016 at 21:12

answered Sep 1, 2016 at 21:04

Coolshaikh

1464 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Error Handling in Excel VBA Outside of Sub

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related