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

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

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.

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

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.

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

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.

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.

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.

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.

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.

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

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.

Subscript Out of Range.xlsm

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.

Get FREE Advanced Excel Exercises with Solutions!

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

What is Subscript Out of Range Error in VBA?

Reasons Behind “Subscript Out of Range Error” in VBA

Solution 1: Open Existent Workbook Before Running VBA Code

Solution 2: Create or Rename Worksheet Before Running VBA Code in Excel

Solution 3: Use Defined Array Elements

Solution 4: Insert Valid Array with Starting & Ending Points

Solution 5: Place Valid Key Name & Index

Advantage of Excel Subscript Out of Range Error in VBA

Things to Remember

Conclusion

Related Articles:

Leave a reply Cancel reply