Avatar of kosenrufu
kosenrufu asked on

Global Error Handling in Excel VBA

Is there a way to detect run time errors and handle them?

Microsoft Excel

Avatar of undefined
Last Comment

8/22/2022 - Mon
Patrick Matthews


To handle runtime errors, use the On Error statement.  For example,

    On Error Resume Next

indicates that the error should be ignored and code execution should go to the next line.  To branch to an in-procedure error handler:

    On Error GoTo NameOfErrorHandler

To turn off error handling:

    On Error GoTo 0

I suggest that you look up On Error in the VBA help, and then come back with specific questions, because this is a very broad topic.


Not every module will produce every error.  It all depends on what your procedure is doing.
Every error has an ERR NUMBER.  If you place an error handler in the procedure, you can determine the ERR NUMBER when an error occurs.  This is pretty basic example, but it will get you started.

On error goto err_hand
'... code
.... code
exit sub
      msgbox err.description & " " & err.number
     if err.number = {any specific error} then
                  '--- do something
                   resume next  '--- return and run next line of code
               resume progout '-- leave the procedure
     end if

Scott C

Here is a very good link for you :)


This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.

If I apply  On Error Resume Next in a main routine would it be applied to all sub-routines?


Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question

If it is used properly, "On Error Resume Next" is a valid method of dealing with error(s), However you have to be judicious about it's use.

It is 'annoying' to see those error messages but it can easily cause more problems later on

For Example

    *Subsequent Errors are suppressed as shown in the example above
    *Getting wrong results

So if you don’t know what the problem is, or where it is, how will you fix it?

Hope this helps..