how to get error detail in VBA programming?

Question

How can I get error details like line number, error message in VBA. It displays simple message like "Compile error", but does not show detail and line number of error.

Answer 1

How can I get error details like line number, error message in VBA.

Compile error will not let you compile the code and will directly take you to the line which has the error. For other runtime errors, you need to add line numbers to your code and then use ERL to get the line number. For example

Option Explicit

Sub Sample()
      Dim i As Long

10    On Error GoTo Whoa

20    i = "Sid"

30    Debug.Print i

Whoa:
40    MsgBox Err.Description & ". Error on line " & Erl
End Sub

Tip: I use MZ-Tools which helps in inserting/removing line numbers. I think (I am not sure) rubberduck also does that.

Answer 2

If your VBA code doesn't contain line numbers, then VBA can not show it.

Purpose of displaying line number is to trace and resolve an error which you can handle using:

On Error GoTo ErrHandler

And then Error Handler Block

ErrHandler:

Refer this.

Answer 3

Compile error is different than runtime error.

The compile error will not let the application build and run and the line of code which causes the error is highlighted. On the other hand, runtime error is when the application runs but something unexpected happens which raises an error, a division by zero for exampe.

Once you have sorted out the compile error and the app runs, you can handle rutime errors like this:

Option Explicit

Sub Something()
    On Error GoTo Trap

    'your code here

Leave:
    On Error GoTo 0
    Exit Sub

Trap:
    MsgBox Err.Number & " - " & Err.Description, vbCritical
    Resume Leave
End Sub