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

Here’s an overview of VBA code that outputs errors.

What Is the 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 in Excel. This is a “Run-Time Error 9” type of error in VBA coding in Excel. The error usually looks like this:

Reasons Behind the “Subscript Out of Range Error” in VBA

Reason 1 – Nonexistent Workbook

We will use the following dataset.
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.

We opened the VBA window.
We inserted a module where we will place our VBA code.

Here’s the code we used:

Sub SubOutRange()
Workbooks("Sales").Activate
Worksheets.Select
End Sub

This VBA code will select the worksheets of the Sales workbook.

We tried to run the code.
The Sales workbook is not open.
The VBA Code is not finding the Sales workbook and shows the Subscript Out of Range error.

Reason 2 – Nonexistent Worksheet

In our Excel Workbook, we have sheets named Problem 2 and Problem 3.

We inserted a new VBA code module.

We used the following code:

Sub Nonexistence_worksheet()
Sheets("Solution 2").Select
End Sub

The code selects the Solution 2 worksheet.

We tried to run the code.
We get the error Subscript out of range.
As there is no Solution 2 worksheet available in our workbook, the code can’t find and activate it.

Reason 3 – Undefined Array Elements

We used 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

We are defining values to an array and want to show the value of Myarr(1) in the message box.

We tried to run the code but it’s showing the Subscript out of range error.
We have declared the range from 1 to 10 but we are trying to access the 16th element of the array (MyArr(16)=110). This causes an error.

Reason 4 – Assigning an Invalid Array

Here’s the code we used:

Here, we are declaring an array as Long and inserting a value in 1st element of the array.

We tried to run the code, but it’s showing the Subscript out of range error.
The code is trying to enter the first element of an array, but the value is not actually an array, so it results in an error.

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 1 – Open the Correct Workbook Before Running VBA Code

We will open the Sales workbook containing 2 worksheets.

We’ll open the VBA editor and insert a module:

Insert the code in the module.

Press F5 on the keyboard to run the code.

The code won’t show an error since the workbook is named correctly.

Solution 2 – Create or Rename a Worksheet Properly Before Running VBA Code in Excel

We have added the Solution 2 worksheet and ran the code.

Solution 3 – Use Defined Array Elements

The array range is between 1 to 10.
We have to call the array in the range of 1 to 10.
We have replaced MyArr(16) with MyArr(8).

Running the code no longer returns an error.

Solution 4 – Insert a Valid Array with Starting and Ending Points

Go to the module to edit the code.
Redefine the array with starting points between 1 and 10.
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 a Valid Key Name and 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].

Download the Practice Workbook

Subscript Out of Range.xlsm

Get FREE Advanced Excel Exercises with Solutions!