VBA: How to display an error message just like the standard error message which has a "Debug" button?

vba scripting excel ms-office
Vantomex picture Vantomex · Oct 12, 2010 · Viewed 196.4k times · Source

As usual, I create an error-handler using On Error Goto statement, there I put a few lines of cleaning codes and display the error message, but now I don't want to lose the comfortableness of the default handler which also point me to the exact line where the error has occured. How can I do that?

Thanks in advance.

Answer

Dr. belisarius picture Dr. belisarius · Oct 12, 2010

First the good news. This code does what you want (please note the "line numbers")

Sub a()
 10:    On Error GoTo ErrorHandler
 20:    DivisionByZero = 1 / 0
 30:    Exit Sub
 ErrorHandler:
 41: If Err.Number <> 0 Then
 42:    Msg = "Error # " & Str(Err.Number) & " was generated by " _
         & Err.Source & Chr(13) & "Error Line: " & Erl & Chr(13) & Err.Description
 43:    MsgBox Msg, , "Error", Err.HelpFile, Err.HelpContext
 44:    End If
 50:    Resume Next
 60: End Sub

When it runs, the expected MsgBox is shown:

alt text

And now the bad news:
Line numbers are a residue of old versions of Basic. The programming environment usually took charge of inserting and updating them. In VBA and other "modern" versions, this functionality is lost.

However, Here there are several alternatives for "automatically" add line numbers, saving you the tedious task of typing them ... but all of them seem more or less cumbersome ... or commercial.

HTH!

Related questions

Test or check if sheet exists

Dim wkbkdestination As Workbook Dim destsheet As Worksheet For Each ThisWorkSheet In wkbkorigin.Worksheets 'this throws subscript out of range if there is not a sheet in the destination 'workbook that has the same name as the current sheet in …

Read More
Declaring early bound MSXML object throws an error in VBA

I am trying to import xml data into excel.. So the first line of the code is Dim XMLDOC As MSXML2.DOMDocument and this gives an error "user defined type not defined"

Read More
What is "Dim fso, MyFile, FileName, TextLine" in VBA?

I received this code from one of those nice people here who are willing to spend their time and energy to share their knowledge with noobs: Sub ReadLinesFromAFileOneAfterAnother () Const ForReading = 1, ForWriting = 2, ForAppending = 8 Dim fso, MyFile, FileName, TextLine Set fso = CreateObject("…

Read More