Subscript Out of Range (Run time: Error 9)

Subscript Out of Range Error (Run Time: Error 9) occurs when you refer to lớn 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.

Bạn đang xem: Lỗi run time error 9

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

Subscript Out of Range

There could be one more situation when you have sầu to lớn face this error “Subscript Out of Range Error” when you are trying khổng lồ declare a dynamic array but forget to lớn use the DIM and ReDyên statement lớn redefine the length of the array.

Now in the above sầu code, you have an array with the name of “myArray” & khổng lồ make it dynamic we have sầu initially left the array length blank. But before you add an thành phầm to you need to lớn redefine the array length using the ReDlặng statement.

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

Sub myMacro()Dyên myArray() As VariantmyArray(1) = "One"End Sub

How Do I Fix Subscript Out of Range in Excel?

The best way to lớn khuyến mãi with this Subscript Out of Range is lớn write effective codes and make sure khổng lồ debug the code that you have written (Step by Step).

Xem thêm: Download Winrar 6 - Winrar Full Download V6


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 và highlight that line with yellow color.

The other thing that you can vì chưng is khổng lồ use an “Error Handler” to jump lớn a specific line of error when it happens.

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

So, when you run this code và the “Sheet1” is not in the workbook where you are trying khổng lồ activate it. It will show you a message box just lượt thích below.

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

Sub myMacro()Dyên wks As WorksheetOn Error GoTo myErrorSheets("Sheet1").ActivatemyError:MsgBox "There"s an error in the code: " và Err.Description & _". That means there"s some problem with the sheet " và _"that you want lớn activate"End Sub

More on VBA Errors

Type Mismatch (Error 13) | Runtime (Error 1004) | Object Required (Error 424) | Out of Memory (Error 7) | Object Doesn’t Support this Property or Method (Error 438) | Invalid Procedure điện thoại tư vấn Or Argument (Error 5) | Overflow (Error 6) | Automation error (Error 440) | VBA Error 400

If you are preparing for an interview, make sure khổng lồ kiểm tra out these VBA interview questions.