Excel Subscript Out of Range Error in VBA (with 5 Solutions)

In this article, we will show you the reasons for the “Subscript out of Range” error in Excel VBA and how to solve those.


Download Practice Template

You can download the free practice Excel template from here.


What is Subscript Out of Range Error in VBA?

VBA Subscript out of range error occurs when we try to access any nonexistent member or nonexistent array collection in Excel. This is a “Run-Time Error 9” type of error in VBA coding in Excel.

The error usually looks like this,

subscript out of range error in vba

5 Reasons with Solutions of Subscript Out of Range Error in VBA

This section will discuss the 5 most common reasons for the occurrence of the Subscript out of range error and what are the solutions to it.

1. Subscript Out of Range Error in VBA for Nonexistent Workbook

When you try to access an Excel workbook that is not open, you will get a “Subscript out of range” error.

subscript out of range error for workbook in vba

If we try to Run the code shown above, we will get the error because there is no Excel workbook named “Sales” that is currently open.

Solution

To solve this error, first open the Excel workbook that you want to access and then Run the macro.


2. Subscript Out of Range Error in VBA for Nonexistent Worksheet

When you try to access a worksheet which doesn’t exist in the Excel workbook then you will also get the “Subscript out of range” error in VBA.

subscript out of range error for worksheet in vba

If we try to Run the code shown above, we will get the error because there is no “Sheet2” worksheet available in our workbook.

Solution

To solve this error, you need to have the Excel sheet that you want to access in the running workbook and then Run the macro.


3. Subscript Out of Range Error in VBA for Undefined Array Elements

If you don’t define the length of a dynamic Array with the word DIM or REDIM in Excel VBA, then you will get the “Subscript out of range” error.

In the above code, we declared the Array in the dimension from 5 to 10 but referred to a subscript of index 3, which is lower than 5.

Solution

To solve this, declare the index in between the Array dimension.

 

This piece of code works perfectly fine because here we referred to the subscript of the index 5, which is inside the range of 5 to 10.


4. Subscript Out of Range Error in VBA for Invalid Collection/ Array

When the subscript is larger or smaller than the range of possible subscript, then the Subscript out of range error will occur.

 

Look at the above example, we declared the variable as an Array, but instead of assigning a start and end point, we have directly assigned the first array with the value of 20.

Solution

To fix this issue, we need to assign the length of the array with a start and ending point.

subscript out of range error for array in vba

This code doesn’t give any error because now we have declared the Array with a starting point of 1 and an ending point of 5.


5. Subscript Out of Range Error in VBA for Shorthand Script

If you use a shorthand from a subscript and it refers to an invalid element then you will get the “Subscript out of range” error in Excel VBA. For example, [A2] is the shorthand for ActiveSheet.Range(A2).

Solution

To fix this, you have to use a valid key name and index for the collection. Instead of writing ActiveSheet.Range(A2), you can just write [A2].


Advantage of Excel Subscript Out of Range Error in VBA

  • VBA Subscript out of range error or “Run-Time Error 9” is really useful in specifying the position of the error where it occurred in the VBA code.
  • This error helps users to find the type of error so that they can check and find the solutions according to the error code.

Things to Remember

  • As this error compiles each step of code to direct us exactly which part of the code we actually need to take actions for, so it is better to compile each line of code one by one by pressing F8 key if you have a huge line of code.

Conclusion

This article showed you the reasons and solutions of Excel Subscript out of range error in VBA. I hope this article has been very beneficial to you. Feel free to ask any questions regarding the topic.


You May Also Like to Explore

Tags:

Sanjida Ahmed

Sanjida Ahmed

Hello World! This is Sanjida, an Engineer who is passionate about researching real-world problems and inventing solutions that haven’t been discovered yet. Here, I try to deliver the results with explanations of Excel-related problems, where most of my interpretations will be provided to you in the form of Visual Basic for Applications (VBA) programming language. Being a programmer and a constant solution seeker, made me interested in assisting the world with top-notch innovations and evaluations of data analysis.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo