Excel VBA runtime error 9, “Subscript out of range,” means code tried to access an item that does not exist at the requested index or name. The usual fix is to identify the highlighted line and correct the worksheet, workbook, array, or collection reference it uses.
What runtime error 9 means
VBA uses subscripts to find items in arrays and collections. A subscript may be a number, such as an array position, or a name, such as a worksheet tab name. If the requested item is not present, VBA cannot complete the reference and raises runtime error 9.
The message is about VBA code, not an ordinary worksheet formula. It is different from a cell showing #REF!, which indicates an invalid reference in a formula. A macro can cause error 9 while reading a worksheet or array, but the error itself is reported by the VBA runtime. It generally occurs while a procedure is running rather than when VBA is compiling its syntax.
The error does not identify which kind of reference is wrong. A line such as Worksheets("Data").Range("A1").Value could fail because the expected sheet is missing from the workbook being referenced. An array expression such as items(5) could fail because the array has no fifth element. The highlighted line and the values used in that line are the best clues.
Common causes in Excel macros
Look first for a name or index that the code assumes exists. Typical causes include:
- A worksheet name does not match. The code asks for
Worksheets("Data"), but the tab is namedData 2026,Datawith a trailing space, or something else. - The code uses the wrong workbook. A sheet exists in one open workbook but not in the workbook the macro actually references.
- A workbook is not open.
Workbooks("Source.xlsx")looks up an open workbook; it does not open a file from disk. - An array index is outside its bounds. A loop or calculation requests an element before the lower bound or after the upper bound.
- A collection index is invalid. For example, the code asks for item 4 when a collection contains only three items.
- A sheet-type assumption is wrong. A chart sheet belongs to the
Sheetscollection but is not a worksheet in theWorksheetscollection.
A related misconception is that a hidden sheet cannot be referenced. A hidden worksheet can still be addressed by name in VBA. If the name is correct, hiding alone is not a reason for error 9.
Find the exact failing line
When the error dialog appears, choose Debug. The Visual Basic Editor highlights the statement that failed. If the dialog offers only End, the procedure may be using an error handler; inspect the handler and temporarily add a breakpoint or step through the procedure to find where the problem begins.
- In desktop Excel for Windows, open the VBA editor with Alt+F11. On a Mac, use Option+F11; depending on the keyboard, you may also need to hold Fn. You can also open the editor from the Developer tab by selecting Visual Basic.
- In the editor, select Debug > Compile VBAProject to catch compile-time issues, then run the macro again.
- When the editor highlights the error line, inspect each workbook, sheet name, index, and variable used on that line. Hover over a variable to see its current value.
- Use F8 to execute the procedure one statement at a time. On some Mac keyboards, use Fn+F8. Watch for the first value or reference that differs from what the code expects.
Do not assume the line immediately before the highlight is correct simply because it ran. It may have assigned an unexpected workbook, sheet name, or array value that the failing line then uses.
Check workbook and worksheet references
A common source of confusion is the difference between ThisWorkbook and ActiveWorkbook. ThisWorkbook means the workbook containing the VBA code. ActiveWorkbook means the workbook currently active in Excel. If a macro stored in a personal macro workbook or add-in is meant to work on the file in front of the user, ThisWorkbook may point to the wrong file. Conversely, if the macro is meant to work on its own workbook, relying on ActiveWorkbook can send it to another open file.
Qualify sheet references with the workbook you intend to use. For example, in a macro stored in the workbook that contains the Data tab, the following procedure checks whether the sheet exists before using it:
Sub CheckDataSheet()
Dim ws As Worksheet
' Look for the sheet in the workbook that contains 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 this workbook."
Exit Sub
End If
MsgBox "Found the Data worksheet."
End Sub
The error handling is deliberately limited to the single lookup; broad use of On Error Resume Next can conceal unrelated failures and make debugging harder. If the source sheet is in another workbook, first make sure that workbook is open, then refer to the correct workbook variable rather than assuming it is active. Check the filename, including its extension, against the workbook name shown in Excel.
Before running or changing a macro, save a copy of the workbook. VBA actions cannot be undone with Ctrl+Z, and a macro can change or delete data as well as report an error.
Check array bounds and loop logic
Arrays do not all start at the same index. A VBA array declared with explicit bounds might start at 1 or 0, and the module-level Option Base setting can affect some implicitly sized arrays. Functions such as Array and results returned by other operations may have their own bounds. Avoid guessing: use LBound and UBound to find the first and last valid indexes.
A range containing several cells is commonly read into a two-dimensional Variant array. For a range covering A1:A3, the row indexes are 1 through 3 and the column index is 1. Asking for row 4 would be out of range. This example iterates over the actual row bounds:
Sub PrintDataRows()
Dim values As Variant
Dim i As Long
' This example expects a Data worksheet with values in A1:A3.
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
For a two-dimensional array, the first argument to LBound or UBound identifies the dimension: 1 for rows and 2 for columns in this example. When a loop computes an index separately, check that calculation too. A loop that starts at 1 and continues through the number of elements may still fail if the array actually begins at 0 or if the count has been calculated incorrectly.
Collections, indexes, and sheet types
Excel collections include items such as open workbooks, worksheets, and sheets. Many Excel collections use one-based numeric positions, so the first item is often 1 rather than 0. The item order can change when a workbook is opened, closed, or when sheets are moved, which makes a hard-coded number less reliable than a meaningful name.
Names and indexes also refer to different collections. Worksheets("Summary") searches worksheets by name; Sheets(1) selects the first sheet of either worksheet or chart-sheet type. If code expects a worksheet but uses a sheet index, it may select a chart sheet and fail later when it tries to use a worksheet-only property. Be explicit about whether the code needs a worksheet or any sheet.
For array and collection errors, inspect both the requested subscript and the items that actually exist. In the VBA editor, the Locals window can help show variable values while execution is paused. For dynamic data, build loops around the collection’s count or an array’s bounds rather than assuming a fixed number of items.
Prevent the error from returning
Robust macros check assumptions at the point where those assumptions matter. Before looking up a worksheet, confirm the intended workbook and sheet name. Before using an array position, obtain its bounds. Before processing an open workbook by name, confirm that it is open or deliberately open the file. If a missing item is an expected possibility, display a useful message or skip that item instead of letting the procedure stop without context.
- Use named references where practical, and qualify them with a workbook variable.
- Use descriptive variable names so it is easier to tell which workbook or array a line refers to.
- Check spelling, spaces, file extensions, and whether a tab was renamed.
- Test macros with a copy of the workbook and with likely edge cases, such as an empty range or a missing optional sheet.
- Keep error handling narrow and report what the macro could not find.
Excel for the web can open a macro-enabled workbook, but it cannot run, create, or edit VBA macros. Use desktop Excel to debug VBA. Excel for Mac supports VBA, although keyboard shortcuts and some editor details can differ from Windows.
Frequently Asked Questions
What does subscript out of range mean in Excel VBA?
It means VBA requested an array element or collection member that does not exist at the specified index or name. Find the highlighted statement and check the workbook, sheet, and index values it uses.
Why does Worksheets(“Sheet1”) cause error 9?
The referenced workbook may not contain a worksheet with that exact name, or the code may be looking in a different workbook than expected. Check the tab name and qualify the reference with the intended workbook.
What is the difference between ThisWorkbook and ActiveWorkbook?ThisWorkbook is the workbook containing the running VBA code. ActiveWorkbook is the workbook currently active in Excel. They can be different, so choose the one that matches the task.
Can a hidden worksheet cause runtime error 9?
No, not by itself. VBA can refer to a hidden worksheet by name. A misspelled name, wrong workbook, or reference to the wrong collection is a more likely explanation.
How do I avoid array subscript errors?
Use LBound and UBound to determine valid indexes, and make loops use those bounds. Check dimensions as well as positions when working with arrays that contain data from a range.
Related
- Excel Number Format for Indian Rupees: ₹ and Lakhs
- Excel Number Format Color Codes and Custom Formats
- Excel Number Format Brackets for Negative Numbers Explained
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Errors & fixes
- Average in Excel Not Working? Common Causes and FixesIf AVERAGE in Excel is not working, first check that the referenced cells contain numbers and that none…
- Excel Find Not Working? Fix Search Problems in a WorksheetIf Excel’s Find feature is not working, first open Home > Find & Select > Find, expand the…
- Excel Cell Won’t Expand to Show All Text? Fix ItIn Excel for Microsoft 365 on Windows, select the cell or cells, choose Home > Wrap Text, then…
- How to Fix a SUM Error in ExcelTo fix a SUM error in Excel, first check that the formula points to the right cells, then…
- Excel Not Dragging Formula? Fix Fill Handle IssuesIf Excel will not drag a formula, enable the fill handle in File > Options > Advanced, then…
- Array Function Not Working in Excel? Fixes That WorkIf an array function is not working in Excel, first check whether your Excel version supports it, whether…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.