Excel VBA Subscript Out of Range: Causes and Fixes

VBA & macros · Updated November 19, 2026

Excel VBA “Run-time error 9: Subscript out of range” means your code asked for an item that does not exist at the specified position or under the specified name. The fix is to check the reference at the highlighted line—often a workbook, worksheet, or array index—and make sure the item exists in the collection or array you are actually using.

What the error means

VBA uses collections and arrays to organize items. A collection might contain the open workbooks or a workbook’s worksheets; an array holds values under numbered positions. Error 9 occurs when code requests a member outside the available collection or array bounds. It is a runtime error, so VBA has started executing the procedure before discovering the invalid reference.

For example, Worksheets("Data") asks for a worksheet whose name is exactly Data in the workbook being referenced. If that workbook has no worksheet with that name, the reference fails. Likewise, asking for array element 5 fails if the array only has elements numbered 0 through 4. The error message identifies the general problem, but the highlighted line and the values used on that line tell you which reference needs attention.

This is not the same as a syntax error: VBA can compile a line that refers to a sheet name or index that will not exist when the macro runs. Nor does the message necessarily mean that Excel has lost data. It usually means the code’s assumptions about names, indexes, or which workbook is active do not match the current state.

Find the failing reference first

When the error dialog appears, choose Debug. The Visual Basic Editor opens and highlights the statement that failed. Read the entire statement, including which workbook or array it uses. A line such as Worksheets("Summary").Range("A1").Value = 1 may look straightforward, but without a workbook qualifier, Worksheets refers to the active workbook. That may not be the file containing the macro.

  1. Choose Debug in the error dialog and note the highlighted line.
  2. Use the Immediate window, available from View > Immediate Window, to inspect expressions such as ?ThisWorkbook.Name or ?ActiveWorkbook.Name.
  3. Check collection counts and array bounds, for example ?ThisWorkbook.Worksheets.Count, ?LBound(values), and ?UBound(values).
  4. Step through the procedure with F8 on Windows to see which values change before the failure.

On Windows, Alt+F11 opens or switches to the VBA Editor in Excel; F8 steps through code. On a Mac, Option+F11 commonly opens the editor, and you may need to hold Fn to use function-key shortcuts, depending on keyboard settings. You can also use Excel’s Developer tab and its Visual Basic command. Menu names and shortcuts can differ by Excel and keyboard configuration.

Workbook and worksheet name problems

A frequent cause is a worksheet name that does not match the code. Sheet tabs may have been renamed, deleted, or copied with a changed name such as Data (2). Spaces count: "Annual Report" is not the same text as "AnnualReport". Check the tab itself, and remember that a worksheet’s visible tab name is distinct from its VBA code name.

Another common cause is an unqualified reference. Worksheets("Data") uses the active workbook, which can change when a user opens another file or when a macro activates a different workbook. If the sheet should be in the workbook containing the macro, use an explicit reference such as ThisWorkbook.Worksheets("Data"). If the target is another workbook, set a workbook variable to that file and use it consistently.

ThisWorkbook means the workbook where the running VBA code is stored. ActiveWorkbook means the workbook currently active in Excel. They may be different—for example, when code is stored in Personal.xlsb or an add-in. The workbook’s filename is also not its worksheet name. If code refers to Workbooks("Report.xlsx"), that exact workbook must be open, and its name in the Workbooks collection may include a different extension or saved filename.

Collections can also include different kinds of sheets. Worksheets contains worksheets, while Sheets can include chart sheets as well. Choosing one collection or the other affects what its indexes refer to; do not assume the third item in both collections is the same sheet.

Check array bounds and loop indexes

Arrays can start at different lower bounds. A VBA array declared with bounds such as Dim values(1 To 5) As String has valid indexes 1 through 5. An array created by Split normally starts at 0. The Option Base statement can affect the default lower bound for certain arrays declared without an explicit lower bound, but it does not change the bounds of every array source. Check the actual bounds rather than guessing.

Use LBound and UBound to make a loop follow the array’s real range. With values holding the result of Split("red,green,blue", ","), the following loop prints each item without assuming that the first index is 1:

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

    ' Save a copy of the workbook before running a macro.
    values = Split("red,green,blue", ",")

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

For a two-dimensional array returned by reading a multi-cell range’s Value or Value2, use the correct row and column indexes. A common mistake is to treat that result like a one-dimensional array or to start a loop at zero. A loop that works for a one-cell range can also behave differently because Excel may return a single value rather than a two-dimensional array in that case. If your code handles both cases, test the actual data shape before indexing.

Check the workbook, sheet, and collection index

Excel’s worksheet collection is indexed starting at 1, but an index can still be invalid if it exceeds the collection’s count. For example, Worksheets(4) fails if the selected workbook has only three worksheets. An index can also point to a different sheet after someone inserts, deletes, or reorders tabs. A name is often clearer when the code needs a particular worksheet; an index is appropriate when the intent is genuinely positional.

For a workbook reference, check whether the workbook is open before asking for it by name. For a worksheet reference, check the relevant workbook’s worksheet count or look for the name explicitly. In either case, qualify the parent collection: a valid sheet in one workbook does not make the same name valid in another.

When missing sheets are an expected possibility, handle that situation deliberately rather than allowing a runtime error to interrupt the procedure. The following function checks worksheet names in a specified workbook and returns True only when it finds a match:

Function WorksheetExists(ByVal sheetName As String, ByVal wb As Workbook) As Boolean
    Dim ws As Worksheet

    ' Save a copy of the workbook before running a macro.
    For Each ws In wb.Worksheets
        If StrComp(ws.Name, sheetName, vbTextCompare) = 0 Then
            WorksheetExists = True
            Exit Function
        End If
    Next ws
End Function

Call it with the workbook you mean, such as WorksheetExists("Data", ThisWorkbook). If it returns False, decide what the macro should do: show a useful message, create the sheet, or stop safely. Do not silently continue as though the required sheet exists.

Use debugging and error handling carefully

In the VBA Editor, F8 runs a procedure one statement at a time on Windows. This is useful when a loop variable, workbook reference, or array bound changes during execution. The Immediate window can evaluate a simple expression while execution is paused. You can also use Debug > Compile VBAProject to find compile-time problems, although compiling will not prove that a worksheet or array element exists at runtime.

Avoid using On Error Resume Next across a large section of code as a way to “fix” error 9. It suppresses errors and can let a macro continue with an unset object or incomplete result. If you use it for a narrow existence check, turn normal handling back on immediately with On Error GoTo 0, then explicitly test the result. Better still, use a clear lookup or a dedicated function when missing items are part of normal operation.

Before running a macro that changes workbook contents, save a copy of the workbook. VBA actions generally cannot be reversed with Ctrl+Z, so a successful run that changes or deletes data may not be recoverable through Excel’s Undo command. If a macro stops in break mode, inspect the failing reference before choosing Reset or continuing; changing code while paused can make the actual cause harder to identify.

Common misconceptions and prevention

Error 9 does not always mean a sheet is missing. The same message can result from an invalid workbook collection index, an array index outside its bounds, or code referring to the wrong workbook. Fixing only the visible sheet name will not help if the code is looking in a different open file. Start with the exact object expression on the highlighted line and verify each part from the outside in: workbook, collection, item name or index, then any array position.

To prevent repeat failures, make assumptions explicit. Use workbook variables instead of relying on whichever file happens to be active, qualify worksheet references, and use LBound and UBound for array loops. If a macro depends on a sheet, validate that requirement near the beginning and give a clear message if it is not met. Also check behavior after users rename sheets, close files, or change the workbook’s contents.

Frequently Asked Questions

How do I fix Run-time error 9 in Excel VBA?
Choose Debug, identify the highlighted reference, and confirm that the named workbook, sheet, or array element exists. Qualify the correct workbook and check collection counts or array bounds before using the item.

Why does VBA say subscript out of range when the sheet exists?
The code may be searching the active workbook rather than the workbook that contains the sheet. It may also be using a different spelling, spacing, or sheet collection. Check the workbook qualifier and the exact tab name.

Does worksheet indexing start at zero or one?
Worksheet collection indexes start at 1. Array indexes depend on how the array was created and declared, so inspect an array with LBound and UBound instead of assuming its first index.

What is the difference between ThisWorkbook and ActiveWorkbook?
ThisWorkbook is the file containing the running code. ActiveWorkbook is the file currently selected in Excel. They can be different, so use the one that matches the workbook the macro should work on.

Can I prevent error 9 if a worksheet might be missing?
Yes. Check the workbook’s worksheet names before accessing the target, then handle a missing sheet explicitly—for example, by displaying a message or exiting the procedure. This is safer than suppressing errors across the whole macro.

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.