VBA: displaying a standard runtime error handler

Question

I have a question about the correct way to handle errors in VBA in Excel. If a specific error occurs, for example xxxxxxx, then MsgBox should be displayed. If another error occurs, a standard error handler appears at runtime. How can I do that? Here is a sample code:

On Error Resume Next 'Line of code that causes an error here. If Err.Number = xxxxxxx Then MsgBox "Specific error message." ElseIf Err.Number = 0 Then Do nothing Else 'Some error other than xxxxxxx. 'This is the problem. Here I would like to display standard run-time error 'handler without causing the error again. End If On Error GoTo 0 

Answer 1

You can get a message box that looks very much like a standard error message by placing it in the "Else" block:

 MsgBox "Run-time error '" & Err.Number & "':" & _ vbNewLine & vbNewLine & _ Error(Err.Number), vbExclamation + vbOKOnly, _ "YourProjectNameHere" 

But this is just a facsimile. This is not the actual error message dialog box that VB6 hosts; it is simply formatted to look like this. Error handling is still disabled by the "Enable error after completion" statement at this point.

But if you really, really want to call the standard error handling code, you can put it in the "Else" block:

 Dim SaveError As Long SaveError = Err.Number On Error Goto 0 Error (SaveError) 

This code stores the error number, enables error handling again, and then re-generates the error. This way you invoke the VB real-time error processing machines. But be careful: if this error is not caught with the active error handler somewhere higher in the call chain, it will terminate your program after the user clicks the OK button.

Note that you will also lose the ability to get the actual line number where the error occurs using "Erl" in this error handler, because you are re-generating the runtime error using the "Error (SaveError)" instruction. But that probably doesn't matter, because most VB code doesn't actually use line numbers, so Erl just returns 0 anyway.

Answer 2

Replace when the error is repeated Next

 On Error Goto SomePlaceInCodeToHandleErrors SomePlaceInCodeToHandleErrors: If Err.Number = XXXX Then MSGBOX "Message" End If 

Check out this thread for more information and sample code.

Answer 3

In the VBA options, select "Break Unprocessed Errors."
To enable processing, use on error goto SomeLabel or on error resume next .
To stop error handling, use on error goto 0 .

In this context, your question is controversial. If you enable error handling, you disable standard error handling.

As Damartyr suggests, you can still use something like msgbox err.description .

Answer 4

So, to keep an eye on JeffK’s new offer, the code shown below works great with VBA, and what else I don’t see in it is dangerous. It is important for Excel to stop working, as it can lose a lot of work, but since the code always checks if there is an error, how could this happen?

Thanks to JeffK for this intriguing idea.

 Dim savedNumber As Long 
 On Error Resume Next 
 'Line of code that causes an error. 
 If Err.Number = XXXXXXX Then 
 'Specific error message. 
 ElseIf Err.Number <> 0 Then 
 savedNumber = Err.Number 
 On Error GoTo 0 
 Error savedNumber 
 End If 
 Err.Clear 
 On Error GoTo 0