Skip to content
Excel VBA 📅 2026-02-05

Excel VBA Runtime Error 9: Subscript out of Range - Solved

🚨 Symptoms & Diagnosis

When working with Excel VBA, encountering a "Run-time error '9': Subscript out of range" can halt your data analysis or automation tasks. This error typically manifests with messages similar to these:

Run-time error '9': Subscript out of range

Sometimes, it points directly to the problematic line in your VBA code:

ActiveSheet.HPageBreaks(2).Location.Address

Or when attempting to assign a value:

MyArray(8) = 234

Or even when trying to reference a worksheet that isn't quite right:

Worksheets("NonExistentSheet").Range("A1")

Root Cause: This error occurs when your VBA code attempts to access an element of an array outside its declared bounds, references a collection member (like a worksheet or a custom object) that doesn't exist by its specified index or name, or uses an invalid index on specific Excel objects such as HPageBreaks or VPageBreaks. It often stems from incorrect array sizing, misspelled sheet names, or an object simply not being present in its collection.

🛠️ Solutions

Quick Fix / Workaround

Immediate Mitigation: Add Bounds Checking and Error Handling

To quickly mitigate the "Subscript out of range" error, implement immediate bounds checking for arrays and basic error handling for collection or object access. This allows your code to gracefully handle the situation rather than crashing.

  1. Open the VBA Editor by pressing Alt + F11.
  2. Navigate to Debug > Compile VBAProject to catch any immediate syntax errors that might contribute to indexing issues.
  3. Press F8 to step through your code line by line and identify the exact statement causing the error.
  4. Before the suspect line, add On Error Resume Next and then check Err.Number immediately after the line to diagnose if an error occurred.
' For array access:
Dim MyArray(1 To 5) As Long ' Example array
Dim i As Long
i = 6 ' Index outside bounds

If i >= LBound(MyArray) And i <= UBound(MyArray) Then
    MyArray(i) = 123
Else
    MsgBox "Index " & i & " is out of range. Max allowed index is " & UBound(MyArray) & ".", vbExclamation
End If

' For collection (e.g., Worksheet) access:
Dim ws As Worksheet
Dim sheetName As String
sheetName = "SheetThatMightNotExist"

On Error Resume Next ' Temporarily ignore errors
Set ws = Worksheets(sheetName)
On Error GoTo 0      ' Re-enable normal error handling

If ws Is Nothing Then
    MsgBox "Worksheet '" & sheetName & "' not found in this workbook. Please check the name.", vbExclamation
Else
    ws.Range("A1").Value = "Hello" ' Safely use the worksheet
End If

Permanent / Best Practice Fix

Best Practice Fix: Implement Defensive Programming and Explicit Declarations

For robust and maintainable VBA solutions, employ defensive programming techniques. This involves validating indices, iterating through collections, and using explicit declarations to catch errors at design time rather than runtime.

  1. View the Project Explorer (Ctrl + R) in the VBA Editor. Carefully verify that all sheet names or object names you are referencing in your code match exactly (case-sensitive) the names displayed in the Project Explorer. Pay attention to hidden sheets or typos.
  2. Use the Immediate Window (Ctrl + G) to test counts and existence before running your full code. For example:
    • ? Worksheets.Count
    • ? ActiveSheet.HPageBreaks.Count
    • ? ThisWorkbook.Worksheets("Data").Name
  3. Replace direct indexed access with For Each loops when iterating through collections (like worksheets or objects in a custom collection) to ensure you only interact with existing members.
  4. Add Option Explicit at the very top of every module (Sub or Function) to force declaration of all variables. This helps catch misspellings of variables that might be intended as indices.
Option Explicit ' Always include this at the top of your modules!

Sub SafeArrayAccess()
    Dim arr() As Variant
    ' Dynamically size array from a range to avoid fixed bounds issues
    arr = Range("A1:C3").Value

    Dim i As Long, j As Long
    ' Iterate safely through array bounds
    For i = LBound(arr, 1) To UBound(arr, 1) ' Loop rows
        For j = LBound(arr, 2) To UBound(arr, 2) ' Loop columns
            Debug.Print "Cell(" & i & "," & j & "): " & arr(i, j)
        Next j
    Next i
End Sub

Sub SafeWorksheetAccess()
    Dim ws As Worksheet
    Dim sheetName As String
    sheetName = "Data" ' Ensure this name exists

    On Error GoTo NoSheet ' Custom error handler for missing sheet

    Set ws = ThisWorkbook.Worksheets(sheetName)
    ws.Range("A1").Value = "Accessed Safely!"
    MsgBox "Worksheet '" & sheetName & "' updated successfully.", vbInformation
    Exit Sub ' Exit before error handler

NoSheet:
    ' Check if the error is specifically for a missing sheet
    If Err.Number = 9 Then ' Subscript out of range
        MsgBox "Error: Worksheet '" & sheetName & "' not found in this workbook. Please verify the name and try again.", vbCritical
    Else
        MsgBox "An unexpected error occurred: " & Err.Description, vbCritical
    End If
    Err.Clear
End Sub

Sub IterateHPageBreaksSafely()
    Dim hBreak As HPageBreak
    Dim breakCount As Long

    On Error Resume Next ' Temporarily disable errors for count check
    breakCount = ActiveSheet.HPageBreaks.Count
    On Error GoTo 0

    If breakCount > 0 Then
        For Each hBreak In ActiveSheet.HPageBreaks
            Debug.Print "Horizontal Page Break at: " & hBreak.Location.Address
        Next hBreak
    Else
        MsgBox "No horizontal page breaks found on the active sheet.", vbInformation
    End If
End Sub

🧩 Technical Context (Visualized)

The "Subscript out of range" error (Error 9) fundamentally relates to how the Excel VBA Runtime Engine manages access to indexed data structures. This includes array indexing, accessing members within collections (like Worksheets, Charts, Shapes), and referencing specific parts of objects such as HPageBreaks or VPageBreaks. The error occurs when the code requests an element that doesn't exist at the specified position or with the given key within these structures.

graph TD
    A[VBA Code Attempts Resource Access] --> B{"Is Access for Array, Collection, or Object Element?"};
    B -- Yes --> C{Is Index/Key Within Valid Bounds/Existing Members?};
    C -- Yes --> D[Successful Data/Object Access];
    C -- "No (Index/Key Invalid/Missing)" --> E{VBA Runtime Engine Flags Error};
    E --> F["Run-time Error 9: Subscript out of range"];
    F --> G["Debugging & Remediation: Check Bounds, Keys, Names, Initialize Objects"];

✅ Verification

After implementing the solutions, verify that the error is resolved and your VBA code runs as expected:

  1. Open the VBA Editor (Alt + F11), go to View > Immediate Window (Ctrl + G). Type ? UBound(MyArray) (replacing MyArray with your actual array name) and press Enter. This will confirm the upper bound of your array. Similarly, ? LBound(MyArray) for the lower bound.
  2. Run your corrected VBA Subroutine (F5). If On Error GoTo is used, ensure the MsgBox for Err.Number (if you temporarily added one) does not display 9.
  3. Use Debug > Step Into (F8) through the problematic section of your code. The yellow highlight should now pass over the previously error-prone line without stopping or showing an error message.
  4. In the Project Explorer (Ctrl + R), double-check the exact spelling and capitalization of any sheet names or object names your code references. An exact match is crucial.

📦 Prerequisites

To apply these fixes, you will need: * Microsoft Excel 2016 or newer (Office 365 preferred for the latest features). * The VBA Editor enabled (access through the Developer tab; if not visible, enable it via File > Options > Customize Ribbon > Developer). * Admin rights are generally not required for these VBA-specific debugging and coding practices.