Run time error 9, Subscript out of range eroor is showing while using Excel VBA

sachin kumar 5 Reputation points
2026-08-19T04:43:10.06+00:00

Run time error 9, Subscript out of range error is showing while using Excel VBA,

When i am using Excel VBA and write coding for run a macro and when i run the code this error is showing, I tried every thing to solve this, but not working, I am unable to run any macro, Please solve this issue, As soon as possible.

I am attaching a screenshot of this error.

Developer technologies | Visual Basic for Applications

2 answers

Sort by: Oldest
Most helpful Newest Oldest

  1. AI answer

    2026-08-19T04:43:30.0133333+00:00

    Run-time error 9: Subscript out of range in Excel VBA means the code is trying to access something that does not exist at that index or name.

    Common causes in VBA:

    1. Array index is outside the valid range
      • The subscript is smaller or larger than the allowed bounds.
      • Check the array declaration.
      • Use LBound and UBound before accessing elements.
      • If the array was declared without a size, it must be dimensioned first with Dim or ReDim.
      Example that causes the error: 
         Dim MyArray() As Integer
   MyArray(8) = 234
    2. A collection or workbook/sheet item does not exist
      • This happens when code refers to a nonexistent member, such as an invalid collection key or an object that is not open or not present.
      • A similar example is trying to access Workbooks(5) when only three workbooks are open.
      • If looping through a collection, For Each...Next can be safer than hard-coded indexes.
    3. A sheet, macro, or object name is misspelled
      • Check spelling carefully, especially if the index or name is stored in a variable.
      • If code uses names with special or Unicode characters, that can also cause this issue in some environments.
    4. The code expects an array, but the variable is not an array in scope
      • Verify the variable name is correct.
      • Misspelling can create a different variable instead of using the intended array.

    For Visual Basic arrays generally, the lowest subscript is 0, and the highest valid subscript is returned by GetUpperBound.

    A practical way to fix it in the VBA editor:

    1. Open the VBA editor with Alt+F11.
    2. Run the macro again.
    3. When the error appears, click Debug.
    4. Check the highlighted line and verify:
      • array index is within LBound and UBound
      • workbook, worksheet, or collection item exists
      • object names are spelled exactly correctly
      • arrays were dimensioned before use
    5. If the code refers to sheet names or macro names containing special characters, test by renaming them to simple ASCII names.

    If the failing line is shared, the exact cause can usually be identified from that one statement.

    AI-generated content may be incorrect. Read our transparency notes for more information.

    Was this answer helpful?

  2. Tony Thach (WICLOUD CORPORATION) 1,120 Reputation points Microsoft External Staff Moderator
    2026-08-19T06:44:30.55+00:00

    Hi @sachin kumar ,

    Run-time error 9, “Subscript out of range,” means that the macro is trying to access an item that does not exist, commonly:

    • An incorrect workbook name: 
      
  Workbooks("Report.xlsx")
    • An incorrect worksheet name: 
      
  ThisWorkbook.Worksheets("Data")
    • A worksheet index greater than the number of available sheets: 
      
  ThisWorkbook.Worksheets(5)
    • An array index outside its valid range.

    When the error appears, click Debug and check the highlighted line. Verify workbook and worksheet names carefully, including spaces and file extensions.

    For arrays, check the valid indexes with:

    
Debug.Print LBound(MyArray), UBound(MyArray)

    You can also display all worksheet names with:

    
Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets

    Debug.Print "[" & ws.Name & "]"

Next ws

    The screenshot mentioned in your question does not appear to be attached. Please provide:

    1. The screenshot of the error.
    2. The complete macro code.
    3. The line highlighted after clicking Debug.

    Please remove any confidential information before posting. The highlighted line is needed to identify the exact invalid workbook, worksheet, collection, or array index.

    If this explanation and the diagnostic steps were helpful, I would appreciate it if you could follow the instructions here, so that others experiencing similar behavior can benefit from the answer as well.  

    Was this answer helpful?

    8 people found this answer helpful.

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.