Sanjida Ahmed
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.
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 Possible 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.
Reason 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.
Solution: Open Existent Workbook Before Running VBA Code
To solve this error, you need to open the Excel workbook that you want to access and then Run the code.
- To do so, firstly, 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.
Reason 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. 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.
Solution: Create or Rename Worksheet Before Running VBA Code in Excel
To solve this error, you need to have the Excel sheet that you want to access in the running workbook and then Run the VBA Code. So here, we need to add the Solution 2 worksheet to our workbook. We can add by creating or renaming this worksheet.
- 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.
Reason 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.
Solution: Use Defined Array Elements
To solve this, we need to call the array elements in the defined range.
- 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.
Reason 4. Assign 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:
- Firstly, 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.
Solution: Insert Valid Array with Starting & Ending Points
To solve this issue, we need to insert the valid array with starting and ending points.
- So, 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
Reason 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: Place Valid Key Name & Index
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. 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.
