VBA Subscript Out Of Range (Run-Time Error '9') - WallStreetMojo

All Courses LoginAll BlogsVBA ResourcesVBA Subscript Out of RangeVBA Subscript Out of Range

Publication Date :

23 May, 2019

Blog Author :

Jeevan A Y

Edited by :

Ashish Kumar Srivastav

Reviewed by :

Dheeraj Vaidya, CFA, FRM

Share

GET: WSM ALL COURSES ACCESS

Table Of Contents

Excel VBA Subscript Out of Range

Subscript out of range is an error we encounter in VBA when we try to reference something or a variable that does not exist in a code. For example, suppose we do not have a variable named x. Then, if we use the MsgBox function on x, we will encounter a “Subscript out of range” error.

VBA “Subscript out of range” error occurs because the object we are trying to access does not exist. It is an error type in VBA coding, a "Run Time Error 9." It is important to understand the concepts to write efficient code. It is even more important to understand the error of your VBA code to debug the code efficiently.

If you make a coding error and do not know what that error is when you are gone.

A doctor cannot give medicine to his patient without knowing what the disease is. Doctors and patients both know there is a disease (error), but it is more important to understand the disease (error) rather than give medicine to it. If you can understand the error perfectly, it is much easier to find the solution.

Similarly, this article will see one of the important errors we regularly encounter, i.e., the "Subscript out of range" error in Excel VBA.

Table of contents

• Excel VBA Subscript Out of Range
  • What is Subscript out of Range Error in Excel VBA?
  • Why Subscript Out of Range Error Occurs?
  • VBA Subscript Error in Arrays
  • How to Show Errors at the End of the VBA Code?
  • Recommended Articles

What is Subscript out of Range Error in Excel VBA?

For example, if you are referring to the sheet, not the workbook, then we get Run-time error ‘9’: "Subscript out of range."

If you click on the "End" button, it will end the sub procedure. If you click on "Debug," it will take you to the line of code where it encountered an error, and help will take you to the Microsoft website page.

Why Does Subscript Out of Range Error Occur?

As we said, as a doctor, it is important to find the deceased before thinking about the medicine. VBA “Subscript out of range” error occurs when the line of code does not read the object we entered.

For example, look at the below image. We have three sheets: Sheet1, Sheet2, and Sheet3.

Now in the code, we have written the code to select the sheet "Sales."

Code:

Sub Macro2() Sheets("Sales").Select End Sub

If we run this code using the F5 key or manually, we will get the Run-time error ‘9’: "Subscript out of range."

It is because we tried accessing the worksheet object "Sales," which does not exist in the workbook. It is a run time error because it occurred while running the code.

Another common subscript error is when we refer to the workbook, which is not there. For example, look at the below code.

Code:

Sub Macro1() Dim Wb As Workbook Set Wb = Workbooks("Salary Sheet.xlsx") End Sub

The above code says variable WB should be equal to the workbook "Salary Sheet.xlsx." As of now, this workbook is not open on the computer. If we run this code manually or through the F5 key, we will get Run time error 9: "Subscript out of Range."

It is due to the workbook we are referring to which is either not open or does not exist at all.

VBA Subscript Error in Arrays

When you declare the array as the dynamic array, and if you don't use the word DIM or REDIM in VBA to define the length of an array, we usually get the VBA "Subscript out of range" error. For example, look at the below code.

Code:

Sub Macro3() Dim MyArray() As Long MyArray(1) = 25 End Sub

In the above, we have declared the variable as an array but have not assigned a start and ending point. Rather, we have assigned the first array the value of 25.

If we run this code using the F5 key or manually, we will get Run time error '9': "Subscript out of Range."

To fix this issue, we need to assign the length of an array by using the "ReDim" word.

Code:

Sub Macro3() Dim MyArray() As Long ReDim MyArray(1 To 5) MyArray(1) = 25 End Sub

This code does not give any errors.

How to Show Errors at the End of the VBA Code?

If you do not want to see the error while the code is up and running but needs an error list at the end, then you need to use the "On Error Resume" error handler. For example, look at the below code.

Code:

Sub Macro1() Dim Wb As Workbook On Error Resume Next Set Wb = Workbooks("Salary Sheet.xlsx") MsgBox Err.Description End Sub

As we have seen, this code will throw Run time error 9: "Subscript out of range” in Excel VBA. But we must use the error handler On Error Resume Next in VBA while running the code. So, we will not get any error messages. Rather, the end message box shows me the error description like this.

You can download the Excel VBA Subscript Out of Range Template here:- VBA Subscript Out of Range Template

Recommended Articles

This article has been a guide to VBA Subscript Out of Range. Here, we learned the Error called “Subscript out of range” (Run-time error'9′) in Excel VBA, along with practical examples and a downloadable template. Below you can find some useful Excel VBA articles: -

• Declare Global Variables in VBA
• VBA AutoFill
• Clear Contents in VBA
• Excel VBA UCase Function

FINANCIAL MODELING 2-DAY BOOTCAMP @70% OFF – YEAR END SALE

Master Financial Modeling in Just 2 Days | Build and Forecast IS, BS & CF from Scratch | Perfect for Graduates and Professionals Looking to Upskill this Sale.

Join WallStreetMojo YouTube

INVESTMENT BANKING MASTERY PROGRAM @70% OFF – YEAR END SALE

Experience the Exact Training Used by Top Investment Banks | Learn FM, DCF, LBO, M&A, Accounting & More | Unlock Wall Street-Level Skills this Sale.

Join WallStreetMojo Instagram

CHATGPT & AI FOR EXCEL COURSE @70% OFF – YEAR END SALE

Boost Productivity 10X with AI-Powered Excel | Automate Reports, Eliminate Errors & Advance Your Analytics Skills | Transform Your Workflow this Sale.

Join WallStreetMojo LinkedIn

EXCEL ALL-IN-ONE BUNDLE COURSE @70% OFF – YEAR END SALE

Master Excel, VBA & Power BI Like a Pro | 70+ Hours of Expert-Led Training | Learn Real-World Applications & Earn Your Certification this Sale.

Join WallStreetMojo Facebook

Join WallStreetMojo X