Excel Subscript Out of Range: Causes and Fixes

How-to basics · Updated November 19, 2026

In Excel, “Subscript out of range” is usually VBA Runtime error 9: a macro is asking for an item that does not exist in a workbook, worksheet, collection, or array. Check the name or index on the line highlighted by the error, confirm the intended workbook is open, and verify that the requested item really exists before changing the code.

What the error means

VBA uses subscripts—names or numbered positions—to retrieve items from collections and arrays. For example, a workbook contains a collection of worksheets, and a macro can request a worksheet by its tab name or by its position. If the requested item cannot be found, VBA raises error 9 rather than returning a blank result.

This is generally a programming error, not a normal worksheet formula error. It differs from #REF!, which appears in a cell when a formula refers to an invalid reference. A macro may display error 9 while Excel is open, but the message does not by itself indicate that the workbook file is damaged. The specific line that fails, and the object or array being accessed there, are the useful clues.

The phrase can also appear in a macro-enabled workbook created by someone else, an add-in, or an automated process. If you do not write VBA, the immediate fix may be to identify which macro is running and ask its author to correct the reference, rather than editing worksheet formulas.

How VBA uses names and indexes

Excel exposes many items as collections. A workbook has worksheets, workbooks can be open at the same time, and a collection can be addressed by a name or a numeric index. These are different ways to find an object, and both can fail:

  • By name: Worksheets("Data") requests a sheet whose tab name is exactly Data.
  • By position: Worksheets(3) requests the third worksheet in that workbook’s worksheet collection.
  • By array index: values(1) requests the element at index 1, if that index falls within the array’s bounds.

Names are often safer than positions when a macro is intended to work with a particular sheet. However, a name-based reference still fails if the name is different, the sheet is in another workbook, or the code is looking in the wrong workbook. Position-based code can break after someone reorders, inserts, or deletes sheets. VBA’s collection numbering starts at 1, but arrays do not always start at 1; their lower bounds depend on how they were created and declared.

Common causes in Excel macros

Most error 9 cases come down to a mismatch between the object the code expects and the objects actually available at run time. Look for these common situations:

  • Misspelled or changed sheet name: The code asks for “Data,” while the tab says “Sales Data.” Spaces, punctuation, and spelling matter. A worksheet’s tab name is not necessarily the same as its VBA code name.
  • Wrong workbook reference: A macro asks for a sheet in ActiveWorkbook, but the active file is not the file containing that sheet. The workbook that stores the macro and the workbook currently selected can be different.
  • Workbook is not open: A request such as Workbooks("Source.xlsx") can only find that workbook if it is open in the current Excel session and its name matches the collection entry.
  • Invalid numeric position: The code requests worksheet 5 when that workbook has only four worksheets, or refers to a workbook position that does not exist.
  • Confusing worksheets with sheets: The Worksheets collection contains worksheet tabs; the broader Sheets collection can also include chart sheets. Code that assumes every sheet is a worksheet may not behave as expected when it uses the wrong collection or index.
  • Array bounds do not match the assumption: An array may start at 0 or at another lower bound, or contain fewer elements than the code expects. Using an index outside its actual bounds raises an error.
  • Unexpected input: A macro that splits text or reads a list may receive an empty string, a missing value, or fewer items than expected, then try to access an element that was never created.

For instance, a workbook may visibly show a tab called “Data ” with an accidental trailing space. That is not the same name as “Data.” Also, a file called “Source.xlsx” may appear in Explorer while Excel has it open under a different name, such as “Source (1).xlsx.” Check what Excel has actually loaded rather than relying only on the file you intended to open.

Find the exact failing reference

When the message appears, choose Debug if that option is available. Excel opens the Visual Basic Editor and highlights the statement that triggered the error. If the macro stops at an error handler instead, inspect the error-handling code and use breakpoints or temporary diagnostic output to identify the failing statement.

  1. Read the highlighted line and identify every collection or array reference on it.
  2. Check the exact workbook, sheet, name, or number being requested. Do not assume the active workbook is the intended one.
  3. In the Immediate window in the Visual Basic Editor, inspect relevant values, such as ?ActiveWorkbook.Name or ?ThisWorkbook.Name. The Immediate window can evaluate expressions while VBA is paused.
  4. Compare the requested sheet name with the visible tab name, including spaces. Confirm whether the code should use Worksheets or Sheets.
  5. If an array is involved, inspect its bounds with LBound and UBound before accessing an element.

On Windows, F8 steps through VBA one statement at a time after the code is paused in the editor. On a Mac, the Visual Basic Editor also supports stepping through code, but the function-key combination may depend on the keyboard’s Fn setting. You can also use the editor’s Debug menu. Step slowly through the statements immediately before the highlighted line: a workbook or variable may have been assigned an unexpected value earlier.

Fix workbook and worksheet references

Use an explicit workbook reference when it matters which file the macro should inspect. ThisWorkbook means the workbook containing the running VBA code. ActiveWorkbook means the workbook currently active in Excel; it can change when the user selects another file. Neither is universally correct, so choose according to the task.

For a macro stored in the same file as the target sheet, a qualified reference can make the intention clear. Before running any macro, save a copy of the workbook: VBA changes cannot be undone with Ctrl+Z.

Sub CheckDataSheet()
    Dim targetSheet As Worksheet

    ' ThisWorkbook is the file containing this macro.
    Set targetSheet = ThisWorkbook.Worksheets("Data")
    MsgBox targetSheet.Name
End Sub

If the macro is supposed to work with whichever workbook is active, use that object deliberately and verify it is the intended file. Do not simply replace every instance of ActiveWorkbook with ThisWorkbook; that can redirect a macro to the wrong file. Likewise, opening a workbook by name and then accessing its sheets requires the workbook to have opened successfully and the sheet name to match.

When a sheet may or may not exist, test for it before using it. A small helper function can check the worksheet collection without stopping the macro on a missing sheet:

Function WorksheetExists(sheetName As String, book As Workbook) As Boolean
    Dim targetSheet As Worksheet

    ' Return True only when this workbook has the requested worksheet.
    On Error Resume Next
    Set targetSheet = book.Worksheets(sheetName)
    On Error GoTo 0
    WorksheetExists = Not targetSheet Is Nothing
End Function

In calling code, pass the specific workbook you mean and handle the False result with a clear message or an alternate action. The temporary On Error Resume Next is limited here to the lookup; leaving it active over a larger block can hide unrelated errors. The function checks worksheets, not chart sheets.

Check array bounds and changing input

Arrays have lower and upper bounds, and code should not assume that the first item is always numbered 1. An array declared with a fixed range can have different bounds from an array produced by a function or populated using a custom declaration. Use LBound(arrayName) and UBound(arrayName) to get the actual first and last valid indexes. If the array may be empty or uninitialized, handle that condition before reading its bounds or an element.

A common pattern is a loop whose ending index is calculated from the data. If the data contains no items, an assumed index may not exist. Similarly, a macro that splits a line of text into parts should check how many parts were returned before requesting a particular part. Validate external input—such as a cell value, file name, or imported text—at the point where it enters the macro, not only after an error occurs.

When the code uses a collection, ask whether its contents can change during the macro. A user may close a workbook, a preceding step may remove a sheet, or an earlier routine may alter the collection. Checking the collection immediately before access is more reliable than assuming it still matches a count captured earlier.

Prevent the error from returning

Prevention is mostly about making assumptions explicit and giving the macro a useful response when an assumption is not met. These practices reduce both error 9 and the time needed to diagnose it:

  • Use descriptive constants or clearly named variables for sheet names, rather than repeating unexplained text in many places.
  • Qualify worksheet references with the intended workbook instead of relying on whichever file happens to be active.
  • Prefer named references over numeric positions when sheets may be reordered.
  • Check that a workbook opened and that required worksheets or input values exist before processing them.
  • Use LBound and UBound for array limits instead of hard-coding an assumed first or last index.
  • Keep error handling narrow. An error handler should explain or manage a known condition, not suppress every failure in a long procedure.

If you receive the error in a workbook you did not write, first note what action triggers it: opening the file, clicking a button, refreshing data, or running a particular macro. If macros are disabled, do not enable them just to dismiss the warning. Enable macros only when you trust the workbook and its source; if a trusted business file still fails, send the highlighted line or the exact reproduction steps to its maintainer.

Frequently Asked Questions

Is “Subscript out of range” the same as #REF!?
No. “Subscript out of range” is commonly a VBA Runtime error 9 raised when code requests a missing collection item or an invalid array index. #REF! is an Excel cell error for an invalid formula reference.

Why does Worksheets("Sheet1") fail when I can see the sheet?
The code may be searching the wrong workbook, or the tab’s actual name may differ from “Sheet1,” including a space or other character. Check the highlighted line and compare the tab name in the specific workbook being referenced.

What is the difference between ThisWorkbook and ActiveWorkbook?
ThisWorkbook is the file containing the VBA code. ActiveWorkbook is the file currently active in Excel. A macro can be stored in one workbook while working on another, so using the wrong one can make an otherwise valid sheet lookup fail.

Can I fix this without knowing VBA?
If you can identify the triggering button or action, you can report it to the workbook’s author or IT support along with the error text and the highlighted line. Avoid changing code you do not understand, especially in a business-critical file; save a copy before any macro changes.

Does the error mean my workbook is corrupted?
Not usually. Error 9 generally points to a reference the code cannot resolve. A renamed sheet, a workbook that is not open, or an invalid array index is more likely than file corruption, though separate file problems can of course occur.

Why does it happen only on another computer?
The other computer may have a different workbook name or folder, a missing worksheet, a different open-workbook state, or different input data. Have the macro’s owner compare the failing reference and the available workbook contents on both machines.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.