[Fixed!] Subscript Out of Range Error in Excel VBA

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.

Subscript Out of Range Error in Excel VBA


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 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.

Dataset for Showing Subscript Out of Range Error for Nonexistent 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.

Inserting Module

  • Instantly, the module will open up.
  • Then, write down the following code in the module:
Sub SubOutRange()
Workbooks("Sales").Activate
Worksheets.Select
End Sub

Writing Code for Selecting Worksheets of Sales Workbook

Here, this VBA code will select the worksheets of the Sales workbook.
  • 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.

Showing Subscript Out of Range Error for Nonexistent Workbook


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 worksheets.

Creating Sales Workbook to Solve Error

  • Then, press Alt + F11 to open the Microsoft Visual Basic for Application window.
  • After that, click Insert >> Module to insert the module.

Inserting Module to Write VBA Code

  • Instantly, a module will be inserted.
  • Then, write down the previous code in the module.

Writing VBA Code to Select Excel Worksheets of Sales Workbook

  • 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.

Running VBA Code to Select Sheets of Sales Workbook

  • As a result, the code is not showing any kind of error.
  • And, the worksheets of the Sales workbook are selected.

Result After Solving Subscript Out of Range Issue in Excel VBA


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.

Worksheets for Showing Subscript Out of Range Error for Nonexistent Worksheet

  • Then, press Alt + F11 to insert the VBA window.
  • After that, click Insert >> Module to insert the module.

Placing Module from Insert Tab

  • In the module, write down the following code in the module:
Sub Nonexistence_worksheet()
Sheets("Solution 2").Select
End Sub

Writing Code for Selecting an Excel Sheet

Here, we will select the Solution 2 worksheet.
  • 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.

Showing Subscript Out of Range Error for Nonexistent Worksheet


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.

Creating Worksheet to Solve Subscript Out of Range Issue


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

Writing Code for Defining Values in Array

Here, we are defining values to an array and want to show the value of Myarr(1) in the message box.
  • 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.

Showing Subscript Out of Range Error for Undefined Array Elements


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).

Modifying Code Using Defined Array Elements

  • After editing, run the code by pressing F5.
  • As a result, you can see the message box.
  • Therefore, we have successfully solved this issue.

Result After Solving Subscript out of Range Error in VBA Excel


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.

Writing Code for Defining Value in Elements of Array

Here, we are declaring an array as Long and inserting a value in 1st element of the array.
  • 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.

Showing Subscript Out of Range Error for Assigning Undefined Array


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

Modifying VBA Code to Solve Error by Inserting Valid Array with Starting & Ending Points


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.


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

5 Excel Hacks You Never Knew

Genius tips to help you unlock Excel's hidden features

FREE EMAIL BONUS

ExcelDemy
Logo