← Back to Error Directory

VBA Run-time Error 9 (Subscript Out of Range) — Complete Fix

A name or index that doesn't exist where your code expects it — here's every context this shows up in and how to defend against each.

Table of Contents

1. What "Subscript" Actually Refers To

In programming terminology, a "subscript" is an index used to access one specific item within a collection — like an array position, or a named item within a collection such as Worksheets or Workbooks. Error 9 fires whenever your code tries to access an item using a subscript (whether a number or a name) that the collection doesn't actually contain at that moment.

2. Interactive Demo: Sheet Existence Checker

Simulate checking whether a sheet name exists before referencing it, exactly the way a defensive VBA function would.


Summary, Data, Charts, Notes

3. Cause 1: Referencing a Non-Existent Sheet Name

The most common cause — hardcoding a sheet name string that has since been renamed, deleted, or was simply typed incorrectly:

Sheets("Report").Activate     ' Error 9 if the sheet is actually named "Reports"

This is especially common in macros shared across multiple workbooks or maintained over a long period, where sheet names drift from what the original code expects.

4. Cause 2: Referencing a Workbook That Isn't Open

Similarly, referencing a workbook by name using the Workbooks collection requires that workbook to currently be open in the same Excel session:

Workbooks("Budget.xlsx").Activate     ' Error 9 if Budget.xlsx isn't currently open

Fix by explicitly opening the workbook first if it might not already be open, or by checking with a reusable function (see section 8 below) before attempting to reference it.

5. Cause 3: Array Index Beyond Bounds

Arrays in VBA have defined upper and lower bounds. Attempting to access an index beyond those bounds throws error 9:

Dim arr(1 To 5) As String
arr(10) = "test"     ' Error 9 - index 10 exceeds the declared bound of 5

Use UBound() and LBound() to check an array's actual bounds programmatically rather than assuming a fixed size, especially for arrays that are populated dynamically.

6. Cause 4: Accessing an Array Before ReDim

A dynamically-sized array declared without an initial size (using empty parentheses) must be sized with ReDim before any element can be accessed:

Dim arr() As String
arr(0) = "test"        ' Error 9 - array hasn't been sized yet
ReDim arr(0 To 5)
arr(0) = "test"         ' Correct - array is now properly sized

7. A Reusable SheetExists Function

Rather than repeating defensive checks throughout your code, build a single reusable function to test sheet existence safely before every reference:

Function SheetExists(sheetName As String) As Boolean
    Dim ws As Worksheet
    On Error Resume Next
    Set ws = ThisWorkbook.Sheets(sheetName)
    SheetExists = Not ws Is Nothing
    On Error GoTo 0
End Function

' Usage:
If SheetExists("Report") Then
    Sheets("Report").Activate
Else
    MsgBox "Sheet 'Report' not found."
End If

8. A Reusable WorkbookIsOpen Function

Function WorkbookIsOpen(wbName As String) As Boolean
    Dim wb As Workbook
    On Error Resume Next
    Set wb = Workbooks(wbName)
    WorkbookIsOpen = Not wb Is Nothing
    On Error GoTo 0
End Function

' Usage:
If WorkbookIsOpen("Budget.xlsx") Then
    Workbooks("Budget.xlsx").Activate
Else
    Workbooks.Open "C:\Files\Budget.xlsx"
End If

9. Frequently Asked Questions

What does Subscript Out of Range mean in VBA?

This error means your code tried to reference an item by index or name that doesn't exist in a collection, most commonly a worksheet name that has been renamed or deleted, an array index beyond the array's declared bounds, or a workbook that isn't currently open.

How do I check if a worksheet exists before referencing it in VBA?

Loop through the Worksheets collection and compare each sheet's Name property against the target name, or use an error-handling function that attempts the reference inside a Function block with On Error Resume Next and returns True or False based on whether Err.Number remains zero.

Why does an array give Subscript Out of Range even though I declared it correctly?

This usually happens when the code accesses an index beyond the array's actual populated range, such as referencing element 10 in an array that was only redimensioned to hold 5 elements, or accessing an index before the array has been sized at all with ReDim.

Related Guides

Editorial Disclaimer: This guide is developed to the best of our domain knowledge and tested against Excel 2019+, Microsoft 365, and Google Sheets. Content is updated continuously as spreadsheet calculation engines evolve.