How to find the exact line causing a crash using Erl.

When a VBA macro encounters a runtime error, Excel’s default error message displays the error number and description, but it does not reveal the line number where the failure occurred. When code runs on another user’s machine—where the VBA editor cannot be opened in break mode—tracking down the failing line can require guessing. The hidden VBA function Erl solves this problem by returning the exact line number of the statement that triggered the error.

Quick Answer

Add numerical line labels (such as 10:, 20:, 30:) at the start of key statements in your procedures. In your error handling block, query the built-in Erl function alongside Err.Number and Err.Description. If an error occurs, Erl returns the line label number of the failing statement.

---

What Is the Erl Function?

Erl (Error Line) is an undocumented, built-in function native to the VBA runtime engine. It requires no external library references or setup.

• If an error occurs on a statement that has a line number label, Erl returns that line number as a Long.
• If the failing statement has no line label, Erl returns 0.
• If a procedure uses line labels intermittently, Erl returns the most recent line label passed prior to the crash.

Code Execution Flow:
  10: Set wb = Workbooks.Open("data.xlsx")   <-- Success
  20: Set ws = wb.Sheets("Inventory")        <-- Crash! Sheet does not exist

Error Handler Traps Error:
  Err.Number      = 9 (Subscript out of range)
  Err.Description = "Subscript out of range"
  Erl             = 20                       <-- Pinpoints exact failure point

---

Step-by-Step Implementation: Using Erl in Production Code

1. Basic Implementation with Explicit Line Labels

To use Erl, place numeric labels at the beginning of executable lines, followed by a colon or space:

Public Sub ProcessMonthlyReport()
    On Error GoTo ErrorTrap

10: Dim wb As Workbook
20: Dim ws As Worksheet
30: Dim lastRow As Long

40: Set wb = Workbooks.Open("C:\Reports\MonthlyData.xlsx")
50: Set ws = wb.Sheets("SummaryData")
60: lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
70: ws.Range("B2:B" & lastRow).Value = ws.Range("A2:A" & lastRow).Value * 1.15

80: wb.Close SaveChanges:=True
    Exit Sub

ErrorTrap:
    Dim errorMsg As String
    errorMsg = "Macro execution failed!" & vbCrLf & _
               "Error Code: " & Err.Number & vbCrLf & _
               "Description: " & Err.Description & vbCrLf & _
               "Failing Line: " & Erl

    MsgBox errorMsg, vbCritical, "Runtime Diagnostic Failure"

    ' Clean up open resources if needed
    If Not wb Is Nothing Then wb.Close SaveChanges:=False
End Sub

If "MonthlyData.xlsx" is missing, the message box reports Failing Line: 40. If the sheet "SummaryData" is missing or renamed, it reports Failing Line: 50.

---

Diagnostic Best Practices: Label Numbering Strategies

You do not need to label every single line of code. Labeling every statement adds visual clutter and makes routines harder to read. Use these strategic approaches instead:

Strategy A: Milestone Block Numbering (Recommended)

Place line labels only at the start of operational blocks or statements that interact with outside resources (such as opening files, querying databases, or working with dynamic ranges):

Sub ImportData()
    On Error GoTo ErrHandler

100: ' --- Initialization ---
    Dim conn As Object
    Set conn = CreateObject("ADODB.Connection")

200: ' --- Database Connection ---
    conn.Open "Provider=MSOLEDBSQL;Data Source=Server01;..."

300: ' --- Data Extraction ---
    Dim rs As Object
    Set rs = conn.Execute("SELECT * FROM Inventory")

400: ' --- Worksheet Output ---
    Sheet1.Range("A2").CopyFromRecordset rs

    Exit Sub
ErrHandler:
    MsgBox "Failure in execution block: " & Erl & " - " & Err.Description
End Sub

If conn.Open fails due to network dropouts, Erl returns 200, pointing straight to the connection block.

Strategy B: Automated Line Numbering via Add-Ins

For large enterprise codebases containing thousands of lines, adding line labels by hand is impractical. Most professional VBA developers use free developer add-ins (such as MZ-Tools) to automatically inject and remove line numbers across entire projects before distributing files to end users.

If you add and remove code lines frequently during maintenance, export and re-import your modules to clear any residual compiled code using Cleaning up the “VBA Project Junk” using code export/import.

---

Diagnosing Why Erl Returns 0

If your error handling block triggers but Erl reports 0, work through this checklist:

1. Missing Line Labels: There are no numeric labels before the statement that failed. If a crash occurs between line 50 and an unlabelled line, Erl reports 50. But if the error happens before the first label in the procedure, Erl returns 0.
2. Error Raised in a Called Subroutine: If Procedure A calls Procedure B, and Procedure B crashes without its own error handler, execution bubbles up to Procedure A’s handler. In this case, Erl returns the line number from Procedure A where the call occurred—not the line inside Procedure B.
3. Labels Used Non-Numeric Text: Text labels (like ErrorHandler:, Step1:, or Cleanup:) are valid jump targets for GoTo, but they are ignored by Erl. Only whole numbers (e.g., 10:, 20:) register with the Erl parser.

---

Combining Erl with Real-Time Debugging Tools

During development, you can inspect Erl directly in the Immediate Window while troubleshooting paused code:

? Erl
 60

Review the techniques in How to use the Immediate Window to debug variable values in real-time to test failing lines interactively.

When debugging issues that only appear intermittently in production, combine Erl with silent logging rather than modal dialogs. Use Debug.Print: The developer’s best friend for silent error logging or write the line number, timestamp, and user ID to an external text file.

For procedures that need temporary code pauses rather than permanent line labels during initial testing, compare your options in The “Stop” statement vs. “Breakpoints”: When to use each.