Excel’s “Subscript out of range” message is VBA runtime error 9: a macro has asked for an item that does not exist in a collection or array. Check the exact workbook, worksheet, file, or array index the highlighted line refers to, then correct the name or index—or make the macro verify that the item exists before using it.
What the error means in VBA
VBA uses collections to organize related objects. A workbook has collections of worksheets and charts, and Excel also maintains collections of open workbooks and other objects. A macro can refer to an item by its name, such as Worksheets("Data"), or by its position, such as Worksheets(1). If the requested name or position is not available in that collection, VBA cannot resolve the reference and raises error 9.
The same message can come from an array reference. For example, if an array has elements numbered 0 through 4, asking for element 5 is out of range. The wording is about a reference that falls outside the items available to VBA; it does not necessarily mean a worksheet is missing.
Excel usually displays a dialog with options such as End, Debug, and Help. Choose Debug to stop at the line that raised the error. That line is the best starting point, although the invalid value may have been created earlier—for example, by reading a sheet name from a cell or building a workbook name from text.
How workbook and worksheet references work
A common source of confusion is which workbook a reference points to. ThisWorkbook means the workbook containing the VBA code. ActiveWorkbook means the workbook currently active in Excel. They can be different: a macro stored in a personal macro workbook or an add-in may operate on a separate workbook that the user has open.
For instance, ThisWorkbook.Worksheets("Data") looks for a worksheet named Data in the file that contains the macro. If Data is in the workbook the user is viewing instead, that reference may fail. Conversely, replacing ThisWorkbook with ActiveWorkbook can cause a macro to act on the wrong file if focus changes. Choose the workbook deliberately rather than relying on whichever workbook happens to be active.
There is also a difference between Worksheets and Sheets. Worksheets contains worksheet tabs; Sheets can include chart sheets as well. A chart sheet can affect a position-based reference such as Sheets(2) without being a worksheet. If your code needs cells, use the Worksheets collection and identify the intended sheet by name whenever possible.
Find the failing reference step by step
- Choose Debug. Note the highlighted line. If the dialog has no Debug option, the error may be occurring in a context that does not allow stepping through the code.
- Read the whole reference. Identify whether it refers to a workbook, worksheet, chart, array, or another collection. For example, in
Workbooks(fileName).Worksheets(sheetName), either name could be the problem. - Check the value at runtime. Use the VBA editor’s Immediate window or inspect variables while paused. Look for extra spaces, misspellings, unexpected extensions, blank values, or a name that differs from what is visible in Excel.
- Check the collection’s actual contents. Confirm that the workbook is open, the sheet exists in the referenced workbook, or the array has the bounds the code assumes.
- Re-run after correcting the cause. Test with the workbook and inputs that produced the error, not only with a simplified example.
On Windows, press Alt+F11 to open the Visual Basic Editor; in the editor, use View > Immediate Window to show the Immediate window. On a Mac, Option+F11 may open the editor, and some keyboards require Fn+Option+F11. Excel for the web does not run VBA macros; use the desktop app to debug them. If a macro is paused, the editor’s Debug controls let you step through statements and inspect how the reference is being formed.
Common causes and what to check
- Renamed or missing worksheet: The code asks for “Summary,” but the tab is named “Summary 2026,” or the sheet was deleted. Check spelling, spaces, punctuation, and the workbook named in the reference.
- Wrong workbook: The sheet exists, but not in
ThisWorkbookorActiveWorkbookas assumed. Qualify the reference with the intended workbook object. - Workbook is not open:
Workbooks("Sales.xlsx")searches the collection of currently open workbooks; it does not open a file from disk. The workbook may be closed, or its open name may not match the string used in code. - Position is no longer valid: A macro uses
Worksheets(3), but the workbook has only two worksheets. Insertions, deletions, or a different file can change what a numbered position means. - Chart sheets affect the index: A numbered reference through
Sheetsmay count a chart sheet. Use a named worksheet reference when the macro needs a worksheet. - Array bounds differ from expectations: VBA arrays do not always start at 0 or 1. The lower bound can depend on how the array was declared or populated, so do not assume an index is valid without checking it.
- Text used as a name is not exact: A name read from a cell or file can include leading or trailing spaces, a different file extension, or a typo. Compare the runtime value with the real object name.
Use a check before accessing a worksheet
When a worksheet may legitimately be absent, test for it and give a useful message instead of letting the macro stop unexpectedly. The example below looks in the workbook containing the code. Before running any macro, save a copy of the workbook: macros cannot be undone with Ctrl+Z.
Sub CheckForDataSheet()
Dim ws As Worksheet
' Look for the sheet in the workbook containing this code.
On Error Resume Next
Set ws = ThisWorkbook.Worksheets("Data")
On Error GoTo 0
If ws Is Nothing Then
MsgBox "The Data worksheet was not found in " & ThisWorkbook.Name, vbExclamation
Exit Sub
End If
MsgBox "Found worksheet: " & ws.Name, vbInformation
End Sub
The error handling is deliberately limited to the attempted lookup, then switched off immediately. This pattern is useful when the absence of a sheet is an expected possibility. It should not be used to hide errors across a large section of code: broad error suppression can make a macro appear to succeed while skipping important work. If the sheet is required, you can instead stop with a clear message and fix the workbook or the reference.
For a workbook reference, first decide whether the file should already be open. If so, check the open workbook’s exact name and handle the case where it is missing. If the macro should open a file, use an explicit path and check that the file exists before calling the open operation. A bare workbook name in the Workbooks collection is not a substitute for a file path or an instruction to open that file.
Array indexes and loops
Error 9 also occurs when code requests an array element outside its lower and upper bounds. An array might have been declared with a specific range, filled from a worksheet, or returned by another procedure. The valid indexes are determined by that array—not by what a programmer expects based on a different array or a previous run.
Inspect the array’s bounds with VBA’s LBound and UBound functions before writing a loop. A loop that always starts at 1 can fail if the array starts at 0; a loop that goes through a fixed maximum can fail when the array is smaller. When the array is definitely allocated and contains elements, a loop from its lower bound to its upper bound avoids hard-coding those assumptions. If an array might not have been allocated, check that condition before asking for its bounds.
For worksheet data, another frequent trap is assuming that the number of returned items matches the number of rows in the source range. Empty input, filtered results, or a change in the data can produce a different size. Trace the point where the array is created, then compare the requested index with its actual bounds. Do not “fix” the error by changing an index arbitrarily; that can replace a visible error with incorrect results.
Prevent the error in reusable macros
Reliable macros make their assumptions explicit. Use named references for objects whose identity matters, qualify worksheet references with a workbook, and check externally supplied names before using them. If a macro depends on a particular sheet layout, it can validate required tabs at the beginning and stop with a message that names the missing tab. That is easier to diagnose than allowing a later line to fail after some changes have already been made.
Position-based references can still be suitable when the position itself is meaningful—for example, processing every worksheet in a loop. In that case, iterate over the collection rather than assuming a fixed count, and account for whether the code is traversing Worksheets or Sheets. When names come from users or cells, consider trimming accidental spaces and report the value the macro actually received. Avoid silently substituting a different sheet: a macro that runs on the wrong data can be more damaging than one that stops.
Before sharing a macro-enabled workbook, test it with the expected workbook open, with optional sheets absent, and with representative input values. Also verify which workbook stores the code and which workbook it should change. These checks are especially important for macros launched from an add-in, a personal macro workbook, or a button that users may click while another file is active.
Frequently Asked Questions
Is “Subscript out of range” the same as Run-time error 9?
Yes. “Subscript out of range” is the message associated with VBA runtime error 9. It means the macro requested an item outside the available names or index bounds.
Why does my macro say a sheet is missing when I can see it?
The reference may be searching a different workbook, or the name in the code may not exactly match the tab name. Check whether the code uses ThisWorkbook or ActiveWorkbook, and compare the actual sheet name character by character.
Does Workbooks("Report.xlsx") open the file?
No. That expression looks for a workbook with that name in Excel’s open Workbooks collection. Open the file separately, or write code that checks the file path and opens it before referring to it.
Should I use a worksheet number or its name?
Use the name when the macro needs a particular worksheet, because a name communicates intent and is less likely to point to a different tab after sheets are rearranged. Use a position only when processing order is genuinely part of the task.
Can I run or fix a VBA macro in Excel for the web?
No. Excel for the web does not run VBA macros. Open the workbook in desktop Excel for Windows or Mac to use the Visual Basic Editor and debug the code.
Related
- Excel: Highlight Cells With Dates Before Today
- Excel Hex Number Format: Convert Numbers to Hex
- Excel Data Validation Lists: Setup, Tips, and Fixes
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA AutoFilter Sort: Filter and Sort Data RowsTo filter and sort rows with Excel VBA, apply AutoFilter to the data range, then configure that range’s…
- Excel VBA: Check Whether a Sheet Is ProtectedIn Excel VBA, check Worksheet.ProtectContents to see whether a worksheet’s cells are protected. For a broader check that…
- Excel UserForm Toolbox: What It Is and How to Use ItThe Excel UserForm Toolbox is the panel in the Visual Basic Editor (VBE) that lets you add controls—such…
- Excel VBA Mid: How the Function WorksIn Excel VBA, Mid returns part of a string, starting at a position you specify; positions begin at…
- VBA Code to Open an Excel File from a Folder SafelyTo open an Excel workbook from a folder with VBA, call Workbooks.Open with the workbook’s full file path.…
- VBA Find the Last Row With Data in an Excel WorksheetTo find the last row in a known column, start at the bottom of that column and use…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.