“Subscript out of range” (Error 9) in Excel VBA means your code tried to access a collection item, array element, or worksheet that doesn’t exist. The most common cause is referencing a worksheet or workbook by a name that isn’t present in the active workbook, or referencing an array index beyond its declared bounds. This guide explains exactly why the error appears and how to fix it step by step.
What Does “Subscript Out of Range” Actually Mean?
In VBA, a subscript is the index number you use to pick an item from a collection or an array. For example, Worksheets("Data") uses the subscript “Data” to find a sheet, and MyArray(3) uses the number 3 to pick the fourth element (since arrays are zero-based by default). When VBA raises Error 9, it means the subscript you supplied does not match any existing item. The error is not about a range of cells—it’s about a collection or array index being out of the valid set.
Common Causes and How to Identify Yours
Before fixing, you need to know which line caused the error. The debugger highlights the offending line when you click Debug on the error dialog. Common culprits include:
- Wrong sheet name:
Sheets("Sales")when the sheet is actually named “Sales Data”. - Sheet index out of range:
Sheets(5)when the workbook has only 3 sheets. - Array index too large:
Dim arr(1 To 3)then accessingarr(4). - Workbook not open:
Workbooks("Report.xlsx")when that file isn’t open. - Referencing a chart sheet or hidden sheet incorrectly.
To pinpoint the issue, hover over the variables in the highlighted line during break mode—you’ll see the actual values being used.
How to Fix Worksheet Reference Errors
Worksheet references are the #1 trigger. Here’s a reliable pattern to avoid them:
- Use the sheet’s CodeName (the name in the Project Explorer, e.g.,
Sheet1) instead of the tab name—it never changes unless you edit the VBA project. - If you must use the tab name, verify it exists before referencing:
If SheetExists("Sales") Then ... - Always qualify with the workbook:
ThisWorkbook.Worksheets("Sales")instead of justWorksheets("Sales")—this prevents referencing the wrong workbook if multiple are open.
For example, instead of Sheets("Data").Range("A1"), use:
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Data") ' error if no sheet named Data
ws.Range("A1").Value = 100
If the sheet name might be misspelled, add a check:
Dim ws As Worksheet
On Error Resume Next
Set ws = ThisWorkbook.Worksheets("Data")
On Error GoTo 0
If ws Is Nothing Then
MsgBox "Sheet 'Data' not found"
Exit Sub
End If
Array Subscript Errors and How to Avoid Them
Arrays are another frequent source. In VBA, Dim arr(1 To 5) creates a 5-element array with indices 1 through 5. Accessing arr(0) or arr(6) triggers Error 9. To prevent this:
- Use
LBoundandUBoundto loop safely:For i = LBound(arr) To UBound(arr). - If using a dynamic array with
ReDim, always reinitialize after resizing. - Be careful with
Split()results—they are always 0-based, so the last element isUBound(arr).
Example of a safe loop:
Dim arr(1 To 3) As Integer
Dim i As Integer
For i = LBound(arr) To UBound(arr)
arr(i) = i * 10
Next i
Workbook and Other Collection References
Beyond sheets, the same error can occur with workbooks, charts, and even user-defined collections. For workbooks, always check if the file is open:
Dim wb As Workbook
On Error Resume Next
Set wb = Workbooks("Report.xlsx")
On Error GoTo 0
If wb Is Nothing Then
MsgBox "Workbook not open"
Exit Sub
End If
For charts, Charts("Chart1") refers to chart sheets; if you meant an embedded chart, you need Worksheets("Sheet1").ChartObjects("Chart 1"). Always verify the exact collection name.
Debugging Tips and Tools
When the error appears:
- Click Debug to enter break mode.
- Use the Immediate Window (Ctrl+G) to type
?Worksheets.Countor?Sheets("Data").Nameto see what actually exists. - Add
Debug.Printstatements before the failing line to log values. - Use Watch Window to track variables.
For example, if the error is on Sheets(i), type ?i in the Immediate Window to see the current index.
Preventing the Error in Future Code
Adopt these habits to minimize future occurrences:
- Always declare variables with
Option Explicitto catch typos in sheet names. - Use
ThisWorkbookorActiveWorkbookexplicitly—never rely on implicit references. - Validate user input before using it as a subscript.
- Wrap risky operations in error handling with
On Error GoToand a clear message.
Example of robust error handling:
Sub SafeSheetAccess()
Dim ws As Worksheet
On Error GoTo ErrHandler
Set ws = ThisWorkbook.Worksheets("Data")
' ... use ws ...
Exit Sub
ErrHandler:
MsgBox "Error " & Err.Number & ": " & Err.Description
End Sub
Frequently Asked Questions
Why does the error occur only sometimes, not always?
It typically happens when the referenced object doesn’t exist at runtime—for example, a sheet that was deleted, or a workbook that isn’t open. The code runs fine when the object exists, but fails when it doesn’t.
Can I use On Error Resume Next to ignore it?
You can, but it’s risky because it hides the error and may cause further issues. Better to check existence first or handle the error gracefully with a message.
Does this error occur in Excel for Mac?
Yes, the error is identical in VBA on Mac. However, some workbook references might behave differently if file extensions are hidden. The fix is the same.
How do I find the exact sheet name that matches my code?
In the VBA editor, press F4 to open the Properties window and select the sheet—its Name property is the tab name, and the (Name) is the CodeName. Use the CodeName in code to avoid typos.
Is there a way to list all sheets in the Immediate Window?
Yes, type For Each s In ThisWorkbook.Sheets: Debug.Print s.Name: Next s and press Enter. This prints every sheet name, helping you spot mismatches.
What if the error is in a user-defined array?
Check the array’s bounds with LBound and UBound. If you used Dim arr() without ReDim, the array is empty and any access fails. Always ReDim before use.
Related
- Excel Template for Tracking Monthly Expenses
- Excel Template for Timesheet Job Tracking
- Excel Template for Saving Goal Tracking
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA Range Properties: Values, Formatting, and NavigationIn Excel VBA, a Range property either returns information about cells or another range object, or changes a…
- Excel VBA Array Add: Append Values with ReDim PreserveTo add an item to a VBA array, resize a dynamic array with ReDim Preserve, then assign the…
- Autofit VBA Range: Columns, Rows, and ExamplesIn Excel VBA, use the AutoFit method to size worksheet columns or rows to fit their contents. For…
- Excel VBA Array Loops: For, For Each, and ExamplesTo loop through an array in Excel VBA, use For Each when you need each value, or a…
- VBA Pivot Table Update: Refresh Tables and ReportsTo update a PivotTable with VBA, call its RefreshTable method; to refresh every PivotTable and external data range…
- Excel VBA Math Functions: What They Do and How to Use ThemExcel VBA includes built-in math functions such as Abs, Sqr, Log, Sin, and Round, which you can call…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.