VBA Trick of the Week :: Tracking the Line Number Where Error Occurs – Erl

Posted: July 10, 2013 by Transformer in Excel
Tags: , ,

When an error occurs in your VBA project then it is extremely valuable to know what caused it so that you know how to counter it. Erl function returns the line number where an error occurs. For using this, your lines of code should be numbered (The line numbers don’t have to be in any order, you can assign random numbers to the lines of code). If the line of code where the error occurred is not numbered then it will return the line number which has been numbered before this. If none of the lines are numbered then it will return 0.


Sub Test_Erl()        
    Dim lngval          As Long    
    On Error GoTo ErrHandler     
1   Debug.Print "Error handler enabled"
2   lngval = 2 / 0        
ErrHandler:
    MsgBox "An error occured in line: " & Erl    
End Sub

In the above example an error occurs on line lngval = 2/0, and the line is numbered so Erl will return the line number i.e. 2.

Sub Test_Erl2()
    Dim lngval          As Long
    On Error GoTo ErrHandler
1   Debug.Print "Error handler enabled"
    lngval = 2 / 0
ErrHandler:
    MsgBox "An error occured in line: " & Erl
End Sub

In the above example an error occurs on line lngval = 2/0, but that line is not numbered. However the line before it is numbered so Erl will return that line number i.e. 1.

Sub Test_Erl3()
    Dim lngval          As Long
    On Error GoTo ErrHandler
    Debug.Print "Error handler enabled"
    lngval = 2 / 0
ErrHandler:
    MsgBox "An error occured in line: " & Erl
End Sub

In this above example no line is numbered. So Erl will return 0.

Advertisements
Comments
  1. snb says:

    Why not using the method you want to illustrate?

    Sub Test_Erl()
        On Error GoTo 400
    
    1   Debug.Print "Error handler enabled"
    2   lngval = 2 / 0
    
    400    MsgBox "An error occured in line: " & Erl
    End Sub
  2. Mike Randell says:

    There are toolbars that will show/hide line numbers e.g. CodeLiner , etc.

Share your thoughts/feedback

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s