VBA Subscript out of Range

Excel VBA Subscript out of Range

VBA Subscript out of Range or majorly knows as Run-Time Error 9 happens when we select such cell or sheet or workbook which actually does not come under range or criteria defined in Excel. It is like we have selected the range of 100 cells or a column and we have called out the values stored in 120 cells of the same column. Which means that we are going out of range to select and call out the values which are not in our defined criteria. When this kind of situation happens, we get a “Run-Time Error 9” message while compiling or running the code. VBA Subscript out of Range error message guides us to rectify the error which is related to the range we have selected in Excel.

Example of Excel VBA Subscript out of Range

Below are the different examples of VBA Subscript out of Range in Excel.

VBA Subscript out of Range – Example #1

We will first consider a simple example. For this, we need to go to VBA windows and add a new module by going in Insert menu option as shown below.

We will get a white blank window of Module. This is where we need to do coding work.

Now write Subcategory of performed function, for best practice keep the name of a function in Subcategory, as we did here for VBA Subscript out of Range.

Code:

Sub Subscript_OutOfRange1()

End Sub

Here in excel, we have only one sheet named as “Sheet1” as shown below.

But we will write a code to select a sheet which is not even added and see what happens.

Now go to VBA window and write Sheets(2) followed by Select function as shown below. Which means, we are selecting Sheet sequence of 2^nd position with Select function.

Code:

Sub Subscript_OutOfRange1()

  Sheets(2).Select

End Sub

Now compile the complete code or do it step by step to know which part of the code is an error. As we have only one line of code, we can directly run the code by clicking on the play button below the menu bar. We will get an error Message saying “Run-Time error 9, Subscript out of range” in the VBA as shown below.

This shows that we are trying to select that sheet which doesn’t exist. If we add a new sheet or change the sheet sequence in code from 2^nd to 1^st then we may get a successful code run. Let’s add another sheet and see what happens.

Now again run the code. And as we did not see any error, which means our code completes the successful run.

VBA Subscript out of Range – Example #2

In another example, we will see again a simple code of activating a Worksheet. For this again we will write the code. Start writing the Subcategory in the name of a performed function or in any other name as shown below.

Code:

Sub Subscript_OutOfRange2()

End Sub

Now with the help of Worksheet, we will activate Sheet1 as shown below.

Code:

Sub Subscript_OutOfRange2()

  Worksheets("Sheet1").Activate

End Sub

Now compile the complete code and run. We will notice there is no error message been popped-up which means code run is successful. Now let’s put the space in between “Sheet 1”

Again compile and run the code.

As we can see above, even if our complete process and way of writing the code are correct but we have taken in correct sheet name as “Sheet 1”. Which in reality has no space between “Sheet1”.

This shows, there are the still chances of getting an error if do not spell or write correct sheet name or workbook name.

VBA Subscript out of Range – Example #3

In this example, we will see how choosing incorrect Array range may create and show Run-time error 9. Start writing Subcategory again in the name of the performed function as shown below.

Code:

Sub Subscript_OutOfRange3()

End Sub

Now with the help of DIM define an Array of any size and gives it to String or Integers. Which depends, what we want to store in Array, numbers or text.

Here we have considered an array of 2×3 as String as shown below.

Code:

Sub Subscript_OutOfRange3()

  Dim SubArray(2, 3) As String

End Sub

By this, it will form a table for 2 rows and 3 columns and we can store any values as per our need. As we have selected String then we will consider text or alphabets in it.

Now in the second line of code, select the created array but with an extra or more column and assign a text as ABC or any other text as per your choice. Here, we have selected an Array of 2×5 as shown below.

Code:

Sub Subscript_OutOfRange3()

  Dim SubArray(2, 3) As String

  SubArray(2, 5) = ABC

End Sub

Now compile and run the code. As we can see in below screenshot, we got a VBA Subscript out of Range error message of Run-time error 9.

Reason for getting this error is because we have selected an incorrect Array range within 2 extra columns from 2×3 to 2×5, which is beyond the limit of code. Now if we again select the correct range of array as 2×3 and see what happens.

After compiling and running the code. We will see we did not receive any error which means our code run was successful.

Pros of Excel VBA Subscript out of Range

• VBA Subscript out of Range allows us to know what kind of error has happened. So that we can specifically find the solution of the obtained error code.
• As VBA subscript out of range ‘Run-time error 9’ is quite useful in knowing what kind of error has occurred in excel.

Things to Remember

• It is recommended to use Subcategory in the name of the performed function with a sequence of code so that it would be easy to track it properly.
• Save the file as Macro-Enabled Workbook to avoid losing written code.
• If you have huge lines of code then it is better to compile each line of code one by one by pressing F8 key. This method compiles each step of code so that we can directly know which portion of code actually has the error in the first go.

Recommended Articles

This has been a guide to Excel VBA Subscript out of Range. Here we discussed why VBA Subscript out of Range error occurs (Run-time Error 9) along with some practical examples and downloadable excel template. You can also go through our other suggested articles –

1. VBA IsError
2. VBA Get Cell Value
3. VBA On Error
4. VBA XML