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

by

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!

Leave a Comment