VBA Subscript Out Of Range Runtime Error (Error 9) - Excel Champs

Skip to content Tutorials

• Excel Basics
• Keyboard Shortcuts
• Excel Tips
• Excel Functions
• Excel Formulas
• Advanced Excel
• Pivot Table
• VBA
• Power‌ Query

Home / VBA / VBA Subscript Out of Range Runtime Error (Error 9)

- Written by Puneet

Contents hide What is Subscript Out of Range (Run time: Error 9) Subscript Out of Range Wrong Range Reference How Do I Fix Subscript Out of Range in Excel? 1. Debug the Code 2. Use an Error Handler 3. Use Option Explicit Related Tutorials

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.

Subscript Out of Range

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.

What is VBA

Related Tutorials

• VBA Automation Error (Error 440)
• VBA Error 400
• VBA Invalid Procedure Call Or Argument Error (Error 5)
• VBA Object Doesn’t Support this Property or Method Error (Error 438)
• VBA Object Required Error (Error 424)

• VBA Out of Memory Error (Error 7)
• VBA Overflow Error (Error 6)
• VBA Runtime Error (Error 1004)
• VBA Type Mismatch Error (Error 13)

About Excel Champs

Excel Champs is one of the top Excel blogs where you can find some of the most amazing tutorials on Excel basics, formulas, VBA, charts, and much more...

Contact - Terms - Policy

Quick Links

• Free Online Excel Courses
• Excel Blog
• Excel Inventory Template
• Testimonials

Excel Guides

• How to use Excel
• Describe Your Excel Skills in Resume
• Excel for Accountants
• Excel Tips and Tricks
• What is VBA
• Pivot Tables in Excel
• Excel Skills