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,
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.
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.
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.
If we try to Run the code shown above, we will get the error because there is no “Sheet2” worksheet available in our workbook.
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.
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.
To fix this issue, we need to assign the length of the array with a start and ending point.
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).
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.
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.