Excel VBA Error 9: Subscript Out of Range

VBA & macros · Updated November 19, 2026

Excel VBA run-time error 9, “Subscript out of range,” means your code requested an item that does not exist at the position or under the name it specified. Check the failing line in the VBA editor, then verify the workbook, worksheet, collection name, or array bounds that line uses.

What Error 9 Means

VBA uses subscripts and names to retrieve members of arrays and collections. An array has a defined range of indexes; a collection, such as a workbook’s worksheets, contains a set of named or numbered items. Error 9 occurs when code asks for an index outside that range or a name that is not present in the collection.

For example, Worksheets("Data") asks for a worksheet whose tab name is exactly Data in the workbook being referenced. If that workbook has no such worksheet, VBA cannot return the requested object. Similarly, asking for element 4 of an array that only has elements 0 through 2 goes beyond its bounds.

This is a run-time error: VBA can often compile the code because the syntax is valid, but it cannot fulfill the request when the line runs. The error does not, by itself, mean the workbook is damaged. It usually points to a mismatch between what the code expects and what is actually open, named, or stored.

How VBA Resolves Names and Indexes

A reference such as Worksheets(1) uses a position, while Worksheets("Data") uses a name. Worksheet positions follow the tab order and are one-based: the first worksheet is 1, not 0. Adding, deleting, or moving tabs can change which sheet occupies a particular position, so names are often clearer when the intended tab has a stable name.

Workbook qualification matters just as much. ThisWorkbook means the workbook containing the VBA code; ActiveWorkbook means the workbook that is currently active in Excel. Those can be different, particularly when a macro is stored in Personal.xlsb or an add-in, or when code opens another file. An unqualified reference may use the active workbook or sheet, which can change as the user interacts with Excel.

There is also a distinction between the worksheet tab name and its VBA code name. Worksheets("Data") refers to the tab’s visible name. A code name such as Sheet1 is a separate VBA project property and can remain unchanged after the tab is renamed. Check which kind of reference the failing line uses before changing names.

Common Causes to Check

  • Wrong or changed worksheet name: The tab may be called “Sales Data” rather than “Sales,” or may contain an extra space. Worksheet names must match exactly, including spaces and punctuation.
  • Wrong workbook: The code looks in ActiveWorkbook when the expected tab is in ThisWorkbook, or the file it expects is not open.
  • Invalid collection index: The code asks for worksheet 5 when only four worksheets exist, or assumes the tab order never changes.
  • Array index outside its bounds: A loop starts or ends at an incorrect index, or an index value is derived from data that is empty or shorter than expected.
  • Uninitialized dynamic array: Code calls LBound or UBound before assigning or resizing a dynamic array.
  • Wrong collection member: The code asks for a workbook, worksheet, chart, or other collection item by a name that is not in that particular collection.

Remember that Worksheets contains worksheet tabs, while Sheets can include chart sheets as well. This difference can matter when code loops through sheets by position or assumes every member is a worksheet. A tab that looks like a sheet may be a chart sheet, so confirm the object type and collection used by the code.

Find the Exact Line Causing the Error

When the error dialog appears, choose Debug. The Visual Basic Editor highlights the statement that failed. Read the entire statement and identify every object, name, and index it uses; an error may be triggered by a reference nested inside a longer line.

  1. In Excel for Microsoft 365 on Windows, open the VBA editor from Developer > Visual Basic. If Developer is not shown, enable it through File > Options > Customize Ribbon and select Developer.
  2. Run the macro again and select Debug when the error appears. If the problem only occurs with a particular file or selection, reproduce those same conditions.
  3. Inspect the highlighted line and check the workbook and worksheet references first. Confirm the exact tab name and which workbook is active.
  4. Use the editor’s View > Locals Window to inspect variables, and View > Immediate Window to examine values. The Immediate window can also run simple expressions while execution is paused.
  5. Use Debug > Step Into to proceed one statement at a time and watch which value or reference is incorrect. If execution does not stop at the right point, add a breakpoint by clicking beside a line in the editor.

On Mac, Excel includes a Visual Basic Editor, though its interface and available developer options can differ from Windows. Excel for the web does not run VBA macros; open the workbook in the desktop app to debug or execute VBA. Do not assume that dismissing the dialog fixes the underlying reference: the same condition can cause the next run to fail again.

Fix Worksheet and Workbook References

Use a fully qualified reference when the macro should work with a particular workbook. For example, if the macro is stored in the workbook that contains the Data tab, ThisWorkbook.Worksheets("Data") makes the intended workbook explicit. If the code should use another file, first confirm that file is open and reference the correct workbook object rather than relying on whichever workbook happens to be active.

To verify a tab name, look at its visible label in Excel and compare it carefully with the text in the code. Rename either the tab or the string in the code so they agree. Avoid using a numeric worksheet index as a substitute for a meaningful name unless the macro deliberately depends on tab order.

A simple check can search the intended workbook’s worksheet collection before the macro tries to use a tab. Before running any macro, save a copy of the workbook: VBA changes cannot be undone with Ctrl+Z. The following example reports whether a worksheet exists in the workbook containing the code, without attempting a missing-name reference:

Sub CheckForDataSheet()
    Dim ws As Worksheet
    Dim sheetName As String
    Dim found As Boolean

    ' Search this workbook's worksheet tabs by exact name.
    sheetName = "Data"
    found = False

    For Each ws In ThisWorkbook.Worksheets
        If ws.Name = sheetName Then
            found = True
            Exit For
        End If
    Next ws

    Debug.Print "Data sheet found: " & found
End Sub

Open the Immediate window to see the result printed by Debug.Print. If the macro is stored somewhere other than the workbook containing the data, change the workbook reference intentionally; do not switch to ActiveWorkbook without confirming that it is the right file.

Fix Array Bounds and Loop Indexes

Array bounds are not always zero-based. An array declared with explicit bounds, such as Dim values(1 To 3) As String, has indexes 1 through 3. A dynamic array must be allocated with ReDim before it can hold elements. The Option Base statement can affect the default lower bound for certain arrays declared without an explicit lower bound, so code should not assume every array starts at zero or one.

Use LBound and UBound to get an array’s actual lower and upper bounds rather than hard-coding them. For a two-dimensional array, specify the dimension, such as LBound(values, 1). Arrays returned by assigning a multi-cell range’s Value property are two-dimensional, with row and column dimensions. A one-cell range, however, returns a single value rather than a two-dimensional array, so code that expects an array should account for that case.

For example, assuming the workbook contains a worksheet named Data and cells A1:A3 contain values, this loop uses the first dimension’s actual bounds. It does not require the loop to assume a particular lower index:

Sub ReadDataColumn()
    Dim values As Variant
    Dim i As Long

    ' Read a three-row range from the Data worksheet.
    values = ThisWorkbook.Worksheets("Data").Range("A1:A3").Value

    For i = LBound(values, 1) To UBound(values, 1)
        Debug.Print values(i, 1)
    Next i
End Sub

This example still requires the Data worksheet to exist; array-safe looping does not repair a missing worksheet reference. If a loop uses a calculated index, inspect the calculation and the bounds together. For instance, a loop that begins at 1 but accesses values(i - 1) will request index 0 on its first pass, which fails if the array starts at 1.

Prevent the Error in Future Macros

Make assumptions explicit and check them near the point where they matter. Use meaningful names for worksheets, qualify workbook and worksheet references, and derive loop limits from the collection or array being traversed. If a macro accepts a sheet name from a cell or user input, validate that value before using it as a collection key.

  • Prefer a worksheet variable set from a known workbook over repeated unqualified references.
  • Check that a workbook is open before trying to access it by name.
  • Use For Each when you need to process every member of a collection, rather than assuming a fixed item count.
  • Allocate dynamic arrays before using them, and check their bounds before indexing.
  • Test cases with missing tabs, empty data, and changed tab order, not only the normal workbook layout.

Error handling can make a macro respond gracefully to an expected missing item, but it should not hide a reference mistake. Broad use of On Error Resume Next may let execution continue with an unset object or incorrect result. If error handling is used, keep it narrow, inspect whether the operation succeeded, and restore normal error handling promptly. A deliberate validation check is usually easier to maintain than silently ignoring the error.

Frequently Asked Questions

Is Excel VBA error 9 the same as “Subscript out of range”?
Yes. Run-time error 9 is displayed with the description “Subscript out of range.” It means a requested array element or collection member was not available at the specified index or name.

Why does VBA say a worksheet is missing when I can see it?
The code may be looking in a different workbook, or the worksheet’s tab name may not exactly match the text in the code. Check whether the statement uses ThisWorkbook or ActiveWorkbook, and compare the visible tab name, including spaces.

Does a worksheet’s code name have to match its tab name?
No. The code name and the visible worksheet tab name are separate properties. A reference such as Worksheets("Data") uses the tab name; a reference such as Sheet1 may use the worksheet’s VBA code name.

Can On Error Resume Next fix error 9?
It can suppress an error temporarily, but it does not make the missing item exist. Use it only for a narrow, intentional check and verify the result; otherwise, correct the workbook, name, index, or array bounds causing the problem.

Can I run VBA in Excel for the web?
No. Excel for the web can open and edit many workbook contents, but VBA macros must be run and debugged in the Excel desktop application. The editor and developer controls can vary between Windows and Mac.

Related

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

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

Spotted an error in this guide? Let us know.