Excel Error 9, “Subscript out of range,” is a VBA runtime error: a macro has tried to retrieve an item using an array index, collection position, or name that does not exist. Check the line highlighted by the error, then verify that the requested worksheet, workbook, or array element is present and that the code refers to the intended workbook.
What Error 9 means
VBA uses numbered positions and names to locate items in collections and arrays. Error 9 occurs when the code asks for an item outside the available range. For example, a workbook might contain worksheets named Summary and Data, but a macro asks for a worksheet named Report. Excel cannot find that item, so VBA stops on the failing statement.
This is usually a problem in a macro, add-in, or workbook event procedure, not a worksheet formula error. A normal formula such as SUM does not raise VBA Error 9. The message may appear after you click a button, open a workbook, run a macro, or trigger code that responds to a worksheet change.
The number in the message is the VBA runtime error number. It does not tell you which particular object is missing. The highlighted code line and the values used by that line are what identify the cause.
Where the error commonly comes from
Look for code that uses square brackets or parentheses to retrieve an item. A collection can be addressed by a name or by position, and an array is addressed by an index. Typical examples include Workbooks("Sales.xlsx"), Worksheets("Data"), Worksheets(3), and values(5). The exact cause depends on which item the code is trying to retrieve.
- A missing or misspelled worksheet: The code asks for a sheet name that differs from the tab name, perhaps because the sheet was renamed, deleted, or has an extra space.
- The wrong workbook: A sheet exists, but not in the workbook object named in the code. This can happen when a macro uses
ActiveWorkbookeven though it should useThisWorkbook, or vice versa. - An invalid collection index: Code requests the third worksheet when the workbook has only two, or uses position zero for a collection whose positions start at one.
- An array index outside its bounds: Code uses an index smaller than the array’s lower bound or larger than its upper bound.
- An uninitialized dynamic array: Code calls
UBoundon a dynamic array before assigning it dimensions, or tries to read an element before the array has been populated.
Check worksheet and workbook references
For a worksheet lookup, compare the name in the code with the visible tab name, including spaces and punctuation. A sheet tab called “Annual Report” will not match Worksheets("AnnualReport"). The code must also point at the workbook that contains that sheet.
ThisWorkbook means the workbook containing the VBA code. ActiveWorkbook means the workbook currently active in Excel, which might be a different file. If a macro stored in a personal macro workbook is intended to work on the workbook you are editing, the active workbook may be appropriate. If a macro is designed to work on sheets in its own file, ThisWorkbook is usually the clearer reference.
A workbook can also contain chart sheets, which are sheets that hold charts rather than worksheet cells. The Sheets collection includes worksheets and chart sheets; Worksheets includes only worksheets. That distinction matters when code uses a numeric position. A chart sheet can change the position of an item in Sheets without changing its position in Worksheets.
To list the worksheet names in the workbook that contains the code, open the Visual Basic Editor and show the Immediate window. In Excel for Microsoft 365 on Windows, use Developer > Visual Basic, then press Ctrl+G in the editor. On Mac, open Tools > Macro > Visual Basic Editor; editor menus and shortcuts can differ by release. The following diagnostic macro prints each worksheet name in the Immediate window:
Sub ListWorksheetNames()
Dim ws As Worksheet
' Print the worksheet names in the workbook containing this code.
For Each ws In ThisWorkbook.Worksheets
Debug.Print ws.Name
Next ws
End Sub
Before running any macro, save a copy of the workbook. Macros can change workbook contents, and their actions cannot be undone with Ctrl+Z (or Command+Z on Mac). If the code is meant to inspect another workbook, adjust the workbook reference deliberately rather than assuming the active file is the right one.
Understand array bounds and collection positions
Array indexes do not always start at one. In VBA, the lower bound can depend on how the array was declared and on the module’s Option Base setting. Some functions also return arrays with a particular lower bound. For instance, Split returns a zero-based array, so the first element is at index zero. Code that assumes every first element is at index one can fail.
Use LBound and UBound to find an array’s lower and upper indexes rather than guessing. For example, the following code is intended for a populated array named items; it prints the first item using the array’s actual lower bound:
Sub PrintFirstItem()
Dim items() As String
Dim parts() As String
' Split returns an array whose lower bound is zero.
parts = Split("North,South", ",")
Debug.Print parts(LBound(parts))
End Sub
In this example, the output is North. The declaration of items is unused and can be removed; it illustrates a dynamic array declaration, not an array that is ready to read. Calling UBound(items) before assigning dimensions to that dynamic array can itself raise Error 9. For a dynamic array, first populate or size it, and handle the case where there are no elements before accessing a bound.
Collections are different from arrays: standard VBA collections are normally indexed starting at one. Asking a collection for item zero is invalid. Do not transfer an array’s indexing assumptions to a collection, or vice versa. A name-based lookup can also fail if the requested key does not exist.
Find the exact failing statement
When the error dialog appears, choose Debug if that option is available. The Visual Basic Editor highlights the statement that raised the error. If the dialog offers End instead, the code may be running from a compiled add-in or another context where source-level debugging is unavailable. If you have access to the macro, inspect the highlighted line and the variables it uses.
- Identify the collection, workbook, worksheet, or array being accessed on the highlighted line.
- Check the requested name or index. Watch for leading or trailing spaces, spelling changes, and indexes that start at zero when the code expects one.
- Confirm which workbook is being referenced. Compare
ThisWorkbookandActiveWorkbookwith the file that actually contains the item. - Check the number of available items and, for an array, its actual lower and upper bounds.
- Re-run the macro after correcting the cause, preferably on a saved copy of the workbook.
For a difficult case, set a breakpoint on the line before the failure and step through the procedure with F8 in the Windows Visual Basic Editor. On Mac, the editor’s menus or keyboard shortcut for stepping may vary, so use its Debug menu if the key differs. Inspect variable values in the Locals window or Immediate window. A breakpoint is useful because it lets you see whether a sheet name, workbook reference, or array index changes unexpectedly earlier in the procedure.
Fix the code without hiding the problem
Correct the reference so it matches the workbook’s real structure. If a macro requires a worksheet named Data, either restore or rename the intended sheet, or update the code to use the correct existing name. Avoid changing a worksheet name just to silence the error if other formulas, macros, or external links depend on the current name.
For code that should continue when an optional sheet is absent, check for the sheet before using it. One safe approach is to loop through the worksheets and compare names. This function returns the matching worksheet if present and returns Nothing if it is absent:
Function FindWorksheet(ByVal book As Workbook, ByVal sheetName As String) As Worksheet
Dim candidate As Worksheet
' Return the matching worksheet, or Nothing if no match exists.
For Each candidate In book.Worksheets
If candidate.Name = sheetName Then
Set FindWorksheet = candidate
Exit Function
End If
Next candidate
End Function
Before using the function’s result, the calling code should test whether the returned worksheet is Nothing. That lets the macro display a useful message, skip an optional step, or stop cleanly instead of trying to use a nonexistent sheet. If the sheet is required, stopping with a clear explanation is better than silently skipping work.
For array access, validate the index against LBound and UBound after confirming the array has been initialized. For collection access, check that the item exists or iterate through the collection to find it. Avoid using On Error Resume Next as a general fix: it suppresses errors and may let the procedure continue with an unset object or incomplete data. If you use it for a narrow, intentional check, restore normal error handling immediately and verify the result.
Prevent Error 9 in future macros
Make object references explicit. For example, qualify a worksheet with the workbook it belongs to instead of relying on whichever workbook happens to be active. Prefer a sheet name over a numeric position when the code depends on a particular sheet, since users can reorder tabs. Where names are configurable or optional, validate them before the macro starts its main work.
It also helps to build in checks for the conditions the code expects. A macro that requires three worksheets can confirm that they exist before changing cells. An array-processing routine can handle an empty input and use its actual bounds. When debugging, test with a workbook that has a renamed sheet, a missing optional sheet, or a different number of rows. These checks make the cause easier to identify and help avoid partial changes when assumptions are not met.
Frequently Asked Questions
Is Excel Error 9 caused by a formula?
Usually not. Error 9 is a VBA runtime error, so it generally comes from a macro, add-in, or event procedure trying to access a missing item. A worksheet formula can display an Excel error value, but that is different from VBA’s “Subscript out of range” message.
Why does my macro say a worksheet is missing when I can see it?
The code may be checking a different workbook, using a slightly different sheet name, or referring to a chart sheet or worksheet collection incorrectly. Check the exact tab text and whether the code uses ThisWorkbook or ActiveWorkbook.
Does Error 9 mean my workbook is corrupted?
No. It usually means the macro’s requested item could not be found at that moment. A renamed sheet, an unexpected workbook being active, or an invalid array index is more likely than workbook corruption.
How do I check the bounds of an array in VBA?
For an initialized array, use LBound(arrayName) and UBound(arrayName) to get its lower and upper indexes. A dynamic array that has not been dimensioned or populated needs separate handling before its bounds can be read.
Can I stop the error by adding On Error Resume Next?
That statement can suppress the error, but it does not make the requested item exist. The code may then use an unset worksheet or miss important work. Find and validate the reference instead, and use error suppression only for a limited, deliberate check.
Related
- Excel Custom Number Format: Hide Zeros Without Deleting Them
- Excel Custom Number Format for Thousands (k)
- Excel Custom Number Format: Add Leading Zeros
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel Macro If Statement: VBA Syntax and Examples GuideAn Excel macro uses VBA’s If...Then...Else statement to make a decision and run different instructions depending on whether…
- Convert an Excel Range to HTML in VBATo convert a range to a formatted HTML file in Excel for Microsoft 365 on Windows, add a…
- Delete Blank Rows in Excel VBA Safely and CorrectlyTo delete completely blank rows with VBA in Excel for Microsoft 365 on Windows, run a macro that…
- Excel VBA: Find a Cell Containing a Specific Date ValueTo find a cell containing a specific date with Excel VBA, loop through the date column and compare…
- Excel VBA Binary Strings: Text, Bytes, and BitsIn Excel VBA, a String is text, not a raw sequence of binary bytes. If you mean a…
- VBA Delete Workbook: Safe Methods and CodeYes. In Excel VBA for Microsoft 365 on Windows, the Kill statement can delete a workbook file by…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.