Excel VBA does not automatically show a source line number in a runtime error message. To identify the failing line in code, number the statements in the procedure and read the VBA function Erl in an error handler; without numbered lines, Erl normally returns 0.
What “error line number” can mean in VBA
People asking about an Excel VBA error line number may mean one of several different things. The distinction matters because VBA reports some error details automatically, but not all of them:
- Error number: A numeric identifier for the kind of error, such as 11 for division by zero. It is available as
Err.Number. - Error description: Text describing the error, available as
Err.Description. - Source line number: The number attached to a line in your VBA procedure. The
Erlfunction can return this number when an error occurs.
These are not interchangeable. An error dialog that says “Run-time error ‘1004’” gives an error code, not the line where the problem happened. Likewise, a highlighted statement in the Visual Basic Editor (VBE) can help while stepping through code, but the usual runtime error dialog does not provide a dependable source line number on its own.
How Erl reports the failing line
Erl is a VBA function that returns a line number associated with the most recent error in the current procedure. To make that result useful, put numeric line labels on the executable statements you want to diagnose. A common convention is to number lines in increments of 10, leaving gaps where you might later add statements.
For example, line labels such as 10, 20, and 30 are part of the VBA code. They are not the visual row numbers in the editor, and they are not automatically added by Excel. If an error occurs on a numbered statement, an error handler can record its label with Erl. If the error occurs on a statement that has no line number, the result may be 0 or may identify only the last numbered point reached, so number the relevant executable statements if precise diagnosis matters.
Line labels do not change the normal meaning of a procedure or fix an error. They give the error handler a location clue. You still need to inspect the statement, inputs, and surrounding logic to determine the cause.
A numbered VBA example
This example intentionally divides by zero so you can see how the location is captured. The declarations come before executable code, as VBA requires. If you run the procedure, the Immediate window should show error 11 and line 40.
Public Sub ErlDemo()
Dim numerator As Double
Dim denominator As Double
Dim answer As Double
Dim errorNumber As Long
Dim errorLine As Long
Dim errorDescription As String
10 On Error GoTo ErrorHandler
20 numerator = 12
30 denominator = 0
40 answer = numerator / denominator
50 Debug.Print answer
60 Exit Sub
ErrorHandler:
errorNumber = Err.Number
errorDescription = Err.Description
errorLine = Erl
Debug.Print "Error " & errorNumber & ": " & errorDescription
Debug.Print "VBA line: " & errorLine
End Sub
Before running any macro, save a copy of the workbook: macro actions cannot be undone with Ctrl+Z. Save a workbook containing VBA as an Excel Macro-Enabled Workbook (.xlsm) if you need to retain the code. In the VBE, open the Immediate window with Ctrl+G on Windows to view the output. On Mac, use the VBE’s View menu to display the Immediate window if that shortcut differs on your keyboard.
How to use the Visual Basic Editor to investigate
The VBE’s debugger can help identify a fault even when a procedure has not been numbered. In Excel for Microsoft 365 on Windows, open the editor with Alt+F11 or select Developer > Visual Basic. If the Developer tab is hidden, enable it through File > Options > Customize Ribbon. On Mac, use Developer > Visual Basic; the keyboard shortcut can depend on the keyboard and may require Fn with Option+F11.
When a runtime error appears, choose Debug if that option is available. The editor may highlight the statement that raised the error, allowing you to inspect variables and step through the procedure. While paused, hover over a variable to see its current value, or use the Locals and Watch windows from the VBE’s View menu. F8 steps through code on Windows; Mac keyboards may require Fn+F8.
Debugging interactively and reporting a line number in a deployed macro are different needs. A developer can pause and inspect a failing procedure in the VBE. A user who only sees a message box needs the program to capture useful context in an error handler, which is where numbered lines and Erl are helpful.
Choosing where and how to add line numbers
For a short procedure that rarely changes, manually numbering important executable statements can be sufficient. Use a consistent sequence, such as 10, 20, 30, and put the number at the start of the VBA statement. Numbering every relevant statement offers finer location information than numbering only the beginning of each logical section.
For a large project, manually maintaining labels can become tedious. Some VBA editor add-ins and code utilities can insert or remove line numbers, but they are separate tools, not a standard Excel feature. Before using one, check that it supports your editor and VBA project, review what it changes, and keep a backup. Do not assume that Excel has a built-in ribbon command that automatically numbers an entire VBA project.
When choosing an approach, consider:
- How often the procedure changes, and whether line numbers can be kept in sync.
- Whether you need a precise statement location or only a broad section of code.
- Whether the workbook will be maintained by other people who need to understand the numbering convention.
- Whether a third-party numbering utility is approved in your organization and compatible with your Excel setup.
Common reasons Erl returns zero or an unexpected number
The code is not numbered. This is the most common explanation for Erl returning 0. Adding a handler alone does not give VBA line labels to report. Number the executable statements that might fail, then test the handler with a controlled error in a copy of the workbook.
The failing statement has no label. If only occasional statements are numbered, Erl cannot reliably identify every unnumbered statement. A returned label may tell you the last numbered location reached rather than pinpointing the exact unnumbered operation. Treat it as a clue, not proof of the cause.
The error handler loses useful information. Capture Err.Number, Err.Description, and Erl as soon as control reaches the handler, before calling other routines or performing cleanup that could cause another error. A new error can replace the original contents of the Err object. Also make sure the handler does not immediately resume execution before the diagnostic values are recorded.
The handler is not active where the error occurs. An On Error GoTo statement applies within its procedure; it is not a project-wide handler. A called procedure may need its own error handling and should return or log relevant details to its caller. Errors raised in an active error handler are another special case and can bypass the handler’s intended flow.
The number is mistaken for an editor row. VBE line labels are numbers embedded in code, whereas the editor’s displayed rows are just positions in the window. Inserting or deleting code can change row positions without changing a line label, and labels need not be consecutive.
Alternatives and supporting diagnostics
Line numbers are useful, but they are only one part of diagnosing a macro. Often the most helpful error report includes the procedure name, the error number and description, and key input values. For example, if a routine processes worksheet rows, logging the row number and the relevant cell address can reveal a bad value more quickly than a source line alone.
For an error that can be reproduced, use breakpoints and step through the procedure with F8 on Windows, or the corresponding function-key combination on your Mac. The VBE’s Debug menu also includes commands for stepping, setting breakpoints, and inspecting execution. For intermittent failures, write diagnostic details to a log sheet or text file, taking care not to overwrite user data. Keep the original error details before running logging or cleanup code, since those operations can themselves fail.
Use On Error Resume Next sparingly. It suppresses normal error handling for subsequent statements until error handling is changed, which can conceal failures if the code does not check Err immediately. For a known, narrowly scoped operation, it can be appropriate when followed by an explicit error check and a clear reset of error handling. It is not a substitute for a useful handler or line numbers.
Excel platform and macro safety notes
VBA is available in the desktop versions of Excel for Windows and Mac, although editor shortcuts and some features can differ. Excel for the web can open macro-enabled workbooks, but it cannot create, edit, or run VBA macros. To investigate a VBA error, open the workbook in desktop Excel. If your organization restricts macros, follow its policy rather than lowering security settings to run unfamiliar code.
Only enable macros in files you trust. A macro can make changes to a workbook and may interact with files or other resources available to the computer. Test diagnostic changes on a copy, especially if the procedure edits, deletes, or imports data. Remember that Ctrl+Z does not reverse a macro’s actions; save checkpoints before running code that changes important content.
Frequently Asked Questions
Does Excel VBA show the error line number automatically?
No. A runtime error normally identifies the error type and description, not the source line. Add line labels and use Erl in an error handler to report a VBA line number.
Why does Erl return 0?
Usually, the error occurred in code without useful numeric line labels, or the procedure was not numbered. Add labels to the statements you need to track and make sure the active handler records Erl.
Is Erl the same as Err.Number?
No. Err.Number identifies the error category, while Erl reports a source line label associated with the error. For a useful diagnostic, record both along with Err.Description.
Can I get an error line number without numbering the code?
Not reliably through Erl. You can use the VBE debugger to pause on a reproducible error and inspect the highlighted statement, but an error handler needs line labels to report a meaningful Erl value.
Can Excel for the web run VBA?
No. Excel for the web does not run VBA macros or provide the desktop VBE. Open the workbook in Excel for Windows or Mac to run and debug its VBA code.
Related
- Excel Unmerge Cells and Fill Every Cell with Its Data
- Excel Undo Shortcut: Ctrl+Z and How It Works
- Excel UK Number Format: Dates, Currency and Separators
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA Code to Copy and Paste Values in ExcelTo copy a range’s results without its formulas, use VBA’s PasteSpecial with xlPasteValues; for a fast transfer between…
- Excel VBA: Find the First Empty Cell in a ColumnTo find the first empty cell in an Excel column with VBA, start at the first row you…
- Excel VBA Delete Worksheet: Code, Safety, and OptionsTo delete a worksheet with Excel VBA, call that worksheet’s Delete method. Excel normally displays a confirmation prompt;…
- OpenOffice and Excel VBA: What Works and What Doesn’tApache OpenOffice Calc can open many Excel workbooks, but it does not reliably run Microsoft Excel VBA macros.…
- Excel VBA: Set a PivotTable Filter ValueTo set a PivotTable report filter with VBA, get the PivotField and assign its item to CurrentPage: for…
- VBA While Loop: Syntax, Examples, and Safe UseA VBA While loop repeats a block of code as long as a condition is true. In Excel,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.