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

Question

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

sachin kumar 5

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.

Barry Schwarz 6,106 Reputation points

2026-08-19T05:16:55.39+00:00

When the error occurs, click on Debug. This will take you to the line in the macro that has the problem. You can then look at the code and use the debug print command to examine the values in the expression to determine which one is causing the problem.

If you still need help,, you will need to show us the code.

2 answers

Your answer

Barry Schwarz 6,106 Reputation points

2026-08-19T05:16:55.39+00:00

When the error occurs, click on Debug. This will take you to the line in the macro that has the problem. You can then look at the code and use the debug print command to examine the values in the expression to determine which one is causing the problem.

If you still need help,, you will need to show us the code.

Answer 1

Tony Thach (WICLOUD CORPORATION) 560 Microsoft External Staff Moderator

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:

The screenshot of the error.
The complete macro code.
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.

Tony Thach (WICLOUD CORPORATION) 560 Reputation points Microsoft External Staff Moderator

2026-08-24T02:43:57.0366667+00:00

Hi @sachin kumar , Just checking in to see if you had a chance to review my previous response. Please feel free to reach out if you have any questions - I’m here to help.
Tony Thach (WICLOUD CORPORATION) 560 Reputation points Microsoft External Staff Moderator

2026-08-26T02:33:29.9366667+00:00

As there have been no recent responses, I’ll proceed with closing this thread. If you still need help or have any further questions, please don’t hesitate to let me know. Thank you.

Answer 2

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:

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
```
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.
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.
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:

Open the VBA editor with Alt+F11.
Run the macro again.
When the error appears, click Debug.
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
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.