Excel VBA Subscript Out of Range: Fixes

VBA & macros · Updated November 22, 2026

“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 accessing arr(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:

  1. 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.
  2. If you must use the tab name, verify it exists before referencing: If SheetExists("Sales") Then ...
  3. Always qualify with the workbook: ThisWorkbook.Worksheets("Sales") instead of just Worksheets("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 LBound and UBound to 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 is UBound(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:

  1. Click Debug to enter break mode.
  2. Use the Immediate Window (Ctrl+G) to type ?Worksheets.Count or ?Sheets("Data").Name to see what actually exists.
  3. Add Debug.Print statements before the failing line to log values.
  4. 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 Explicit to catch typos in sheet names.
  • Use ThisWorkbook or ActiveWorkbook explicitly—never rely on implicit references.
  • Validate user input before using it as a subscript.
  • Wrap risky operations in error handling with On Error GoTo and 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

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.