In this article, we will show the reasons behind the Subscript Out of Range error in Excel VBA. And, we will also provide you with the solutions to the issues. In the time using VBA, you may find this kind of issue. So, it is necessary to solve this issue. So, without any delay, let’s start the discussion.
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:
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.
Reasons Behind “Subscript Out of Range Error” in VBA
1. Nonexistent Workbook
When you try to access an Excel workbook that is not open, you will get a “Subscript out of range” error. Let’s follow the steps below to know the reasons behind the error.
STEPS:
- In this procedure, we will use the following dataset.
- Here, the dataset contains information about the sales and revenue of a shop.
- This Excel sheet is available in Subscript Out of Range in the VBA workbook.
- After that, press ALT + F11 to open the VBA window.
- Then, press Insert >> Module to insert a module where we will place our VBA code.
- Instantly, the module will open up.
- Then, write down the following code in the module:
Sub SubOutRange()
Workbooks("Sales").Activate
Worksheets.Select
End Sub
- Now, click F5 to run the code.
- But, the code is not running and is showing the following error.
- Here, the Sales workbook is not open.
- For this reason, the VBA Code is not finding the Sales workbook and showing Subscript Out of Range error.
Read More: VBA Object Variable or with Block Variable Not Set in Excel
2. 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. Let’s follow the steps below to know the reasons behind the error.
STEPS:
- In the Excel Workbook, we have Problem 2, and Problem 3 named sheets.
- Then, press Alt + F11 to insert the VBA window.
- After that, click Insert >> Module to insert the module.
- In the module, write down the following code in the module:
Sub Nonexistence_worksheet()
Sheets("Solution 2").Select
End Sub
- Now, press the F5 key on the keyboard to run the code.
- But, the code is not running.
- Here, the code is showing Subscript out of range
- As there is no Solution 2 worksheet available in our workbook, for this reason, it is showing this kind of error.
Read More: Reasons And Solutions for Excel Object Required Error in VBA
3. Undefined Array Elements
When we call undefined array elements, we will also find Subscript out of range error in Excel VBA. Let’s follow the steps below to learn the reason behind it.
STEPS:
- Firstly, insert a module.
- Previously, we have shown how to insert a module.
- Now, write down the following code in the module.
Sub Undefined_Array_Elements()
Dim MyArr(1 To 10) As Integer
MyArr(1) = 100
MyArr(2) = 20
MyArr(3) = 25
MyArr(4) = 21
MyArr(5) = 29
MyArr(16) = 110
MsgBox MyArr(1)
End Sub
- After writing the code, click on the F5 button on the keyboard to run the code.
- But, the code is not running.
- Here, the code is showing the Subscript out of range error.
- Because we have declared the range from 1 to 10.
- But, we are trying to access the 16th element of the array.
- For this reason, the code is showing this error.
Read More: [Fixed!] Excel Application-Defined or Object-Defined Error in VBA
4. Assigning an Invalid Array
When we assign an invalid array, we will also find Subscript out of range error in Excel VBA. Let’s follow the steps below to learn the reason behind it.
STEPS:
- First, insert a module.
- Previously, we have shown how to insert a module.
- Now, write down the following code in the module.
- After writing the code, click on the F5 button on the keyboard to run the code.
- But, the code is not running.
- Here, the code is showing the Subscript out of range error.
- Because we have not declared the range of the array.
- For this reason, the code is understanding the elements of the code and showing this error.
Read More: How to Fix Compile Error in Hidden Module in Excel
5. 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 1: Open Existent Workbook Before Running VBA Code
- First, we will open the Sales workbook containing 2 worksheets.
- Then, press Alt + F11 to open the Microsoft Visual Basic for Application window.
- After that, click Insert >> Module to insert the module.
- Instantly, a module will be inserted.
- Then, write down the previous code in the module.
- After writing the code, press F5 on the keyboard to run the code.
- Alternatively, you can click on the following run icon to run the VBA code.
- As a result, the code is not showing any kind of error.
- And, the worksheets of the Sales workbook are selected.
Read More: [Fixed!] Invalid Forward Reference in VBA
Solution 2: Create or Rename Worksheet Before Running VBA Code in Excel
- So, we have added the Solution 2 worksheet and run the code.
- As a result, the code is not showing any error and the Solution 2 worksheet is selected.
Read More: [Solved!] Excel VBA “Argument Not Optional” Error
Solution 3: Use Defined Array Elements
- Here, the array range is between 1 to 10.
- For this reason, we have to call the array in the range of 1 to 10.
- That’s why we have replaced MyArr(16) with MyArr(8).
- After editing, run the code by pressing F5.
- As a result, you can see the message box.
- Therefore, we have successfully solved this issue.
Solution 4: Insert Valid Array with Starting & Ending Points
- Go to the module to edit the code.
- Then, redefine the array with starting points between 1 and 10.
- After that, run the code by pressing F5 and the code will not show any error.
Sub Assign_Invalid_Array()
Dim MyArr() As Long
MyArr(1) = 10
End Sub
Read More: [Fixed!] Unable to Set the Visible Property of the Worksheet Class
Solution 5: Place Valid Key Name & Index
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.
Download Practice Workbook
You can download the free practice Excel template from here.
Conclusion
This article showed you the reasons and solutions of Excel Subscript out of range error in VBA. There is a practice workbook at the beginning of the article. Go ahead and give it a try. To read similar articles, check out the ExcelDemy website. Last but not least, please use the comment section below to post any questions or make any suggestions you might have.
