Lỗi runtime error 9 subscript out of range trong excel năm 2024

In VBA, the “Subscript Out of Range” error is like trying to pick a book from a shelf that isn’t there! Let’s say you have a box with five apples and want to pick the seventh; you can’t, right? It’s the same here.

What is Subscript Out of Range [Run time: Error 9]

Subscript Out of Range Error [Run Time: Error 9] occurs when you refer to an object or try to use a variable in a code that doesn’t exist in the code, in that case, VBA will show this error. As every code that you write is unique, so the cause of the error would be.

In the following example, you have tried to activate the “Sheet1” which is an object. But as you can see in the workbook no worksheet exists with the name “Sheet1” [instead you have “Sheet2”] so VBA show “Subscript Out of Range” to notify you that there’s something wrong with the code.

There could be one more situation when you have to face the error “Subscript Out of Range Error” when you are trying to declare a dynamic array but forget to use the DIM and ReDim statement to redefine the length of the array.

Now in the above code, you have an array with the name “myArray” and to make it dynamic we have initially left the array length blank. But before you add an item you need to redefine the array length using the ReDim statement.

And that’s the mistake we have made in the above code and VBA has returned the “Script Out of Range” error.

Sub myMacro[] Dim myArray[] As Variant myArray[1] = "One" End Sub

Wrong Range Reference

In below code, if “ZZ100” refers to a cell outside the existing columns in “Sheet1”, when you refer to it will show an error.

Sub InvalidRange[]

Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets["Sheet1"]
Dim rng As Range
Set rng = ws.Range["ZZ100"]
rng.Value = "Data"
End Sub

How Do I Fix Subscript Out of Range in Excel?

1. Debug the Code

The best way to deal with this Subscript Out of Range is to write effective codes and make sure to debug the code that you have written [Step by Step].

When you run a code step by step it is easy for you to know on which line of that code you have an error as VBA will show you the error message for Error 9 and highlight that line with yellow color.

2. Use an Error Handler

The other thing that you can do is to use an “Error Handler” to jump to a specific line of error when it happens.

In the following code, we have written a line to activate the sheet but before that, we have used the goto statement to move to the error handler. In the error handler, you have a message box that shows you a message with the Err. Description that an error has occurred.

So, when you run this code and the “Sheet1” is not in the workbook where you are trying to activate it. It will show you a message box just like below.

And if the “Sheet1” is there then there won’t be any message at all.

Sub myMacro[] Dim wks As Worksheet On Error GoTo myError Sheets["Sheet1"].Activate myError: MsgBox "There's an error in the code: " & Err.Description & ". That means there's some problem with the sheet " & "that you want to activate" End Sub

3. Use Option Explicit

At the top of your module, enter Option Explicit to force VBA to declare all variables. It can help avoid typing mistakes while writing the code using variable names that might be causing unexpected behavior while running the code.

Note – You can also use a application like MZ-Tools to write VBA codes, this which offers help you while write a code. You can review your code and use error handling, and it also gives you automatic correction suggestions.

Chủ Đề