How to Fix Excel ‘Subscript Out of Range’ Run-time Error 9

Executing a VBA macro or automated reporting script in Microsoft Excel only to have execution halt with a modal dialogue reading “Run-time error ‘9’: Subscript out of range” stops workbook automation. In Visual Basic for Applications (VBA), a subscript out of range error indicates that your code is attempting to reference an element that does not exist within the targeted collection or array. The most frequent culprits include misspelled worksheet tab names, referencing closed workbooks, indexing array bounds beyond their dimensioned upper limits, or unhandled sheet deletions.

Click Debug to Identify the Failing Code Line

When the error dialogue pops up, Excel offers an immediate diagnostic button that pinpoints the exact statement causing the crash.

  1. When the “Run-time error ‘9’” dialogue appears, click the Debug button.
  2. The Visual Basic Editor (VBE) will launch immediately, highlighting the offending line of code in bright yellow.
  3. Hover your mouse cursor over the variables on that highlighted line to inspect their current runtime values.
  4. If the highlighted line references a sheet (e.g. Worksheets("Sheet1").Activate) or an array (e.g. MyArray(10)), this statement is attempting to access a nonexistent index.

Verify Exact Worksheet and Workbook Tab Names

VBA collection references like Worksheets("Sales Data") require character-for-character accuracy, including spaces. A single trailing space in an Excel sheet tab breaks the reference.

  1. Examine the sheet name inside quotation marks in your VBA code.
  2. Switch to the main Excel workbook and look at the sheet tabs along the bottom.
  3. Double-click the tab of the target worksheet.
  4. Check if there is an accidental leading or trailing space at the beginning or end of the tab name.
  5. Ensure the name matches the code string perfectly, or update the code to match the revised sheet tab name.
  6. Alternatively, reference the sheet by its permanent code name (e.g. Sheet1.Range("A1")) rather than its user-editable tab name (Worksheets("Sales")).

Check Array Bounds and Lower Indexing

In VBA, arrays default to a lower index bound of 0 unless explicitly declared otherwise using Option Base 1. Referencing an index outside declared boundaries triggers Error 9.

  1. In your VBA module, locate where the array was initially dimensioned (e.g. Dim CustomerList(1 To 5) As String).
  2. If your loop attempts to access CustomerList(6) or CustomerList(0), VBA throws Subscript out of range.
  3. Use dynamic array bounds checking in your loops to prevent hardcoded index overflows:
    For i = LBound(CustomerList) To UBound(CustomerList)
        ' Process array element
    Next i
  4. LBound and UBound dynamically calculate the minimum and maximum valid subscript boundaries at runtime.

Ensure External Target Workbooks Are Actively Open

VBA’s Workbooks("Report.xlsx") collection index only references workbooks currently loaded in memory.

  1. If your macro references a secondary workbook, verify whether that file is already open in Excel.
  2. If the file is closed on disk, Workbooks("Report.xlsx") will fail with Error 9.
  3. Precede the reference with an explicit file opening statement:
    Workbooks.Open "C:\Path\To\Report.xlsx"

Reset Corrupted VBA Project Modules

Compiled P-code in long-running Excel macro workbooks can become desynchronised from the text source code.

  1. In the VBA editor, click Debug on the top menu bar.
  2. Click Compile VBAProject.
  3. Resolve any syntax warnings, save the workbook, and test macro execution.

Get the best tech tips delivered straight to your inbox.

Join thousands of readers mastering Apple, Google, Microsoft, and Linux.