Subscript out of range vba excel ошибка

Subscript out of range is an error we encounter in VBA when we try to reference something or a variable that does not exist in a code. For example, suppose we do not have a variable named x. Then, if we use the MsgBox function on x, we will encounter a “Subscript out of range” error.

VBA “Subscript out of range” error occurs because the object we are trying to access does not exist. It is an error type in VBA codingVBA code refers to a set of instructions written by the user in the Visual Basic Applications programming language on a Visual Basic Editor (VBE) to perform a specific task.read more, a “Run Time Error 9.” It is important to understand the concepts to write efficient code. It is even more important to understand the error of your VBA codeVBA error handling refers to troubleshooting various kinds of errors encountered while working with VBA. read more to debug the code efficiently.

If you make a coding error and do not know what that error is when you are gone.

A doctor cannot give medicine to his patient without knowing what the disease is. Doctors and patients both know there is a disease (error), but it is more important to understand the disease (error) rather than give medicine to it. If you can understand the error perfectly, it is much easier to find the solution.

Similarly, this article will see one of the important errors we regularly encounter, i.e., the “Subscript out of range” error in Excel VBA.

You are free to use this image on your website, templates, etc., Please provide us with an attribution linkArticle Link to be Hyperlinked
For eg:
Source: VBA Subscript Out of Range (wallstreetmojo.com)

What is Subscript out of Range Error in Excel VBA?

For example, if you are referring to the sheet, not the workbook, then we get Run-time error ‘9’: “Subscript out of range.”

If you click on the “End” button, it will end the sub procedure. If you click on “Debug,” it will take you to the line of code where it encountered an error, and help will take you to the Microsoft website page.

Why Does Subscript Out of Range Error Occur?

As we said, as a doctor, it is important to find the deceased before thinking about the medicine. VBA “Subscript out of range” error occurs when the line of code does not read the object we entered.

For example, look at the below image. We have three sheets: Sheet1, Sheet2, and Sheet3.

Now in the code, we have written the code to select the sheet “Sales.”

Code:

Sub Macro2()

   Sheets("Sales").Select

End Sub

If we run this code using the F5 key or manually, we will get the Run-time error ‘9’: “Subscript out of range.”

It is because we tried accessing the worksheet object “Sales,” which does not exist in the workbook. It is a run time error because it occurred while running the code.

Another common subscript error is when we refer to the workbook, which is not there. For example, look at the below code.

Code:

Sub Macro1()

  Dim Wb As Workbook

  Set Wb = Workbooks("Salary Sheet.xlsx")

End Sub

The above code says variable WB should be equal to the workbook “Salary Sheet.xlsx.” As of now, this workbook is not open on the computer. If we run this code manually or through the F5 key, we will get Run time error 9: “Subscript out of Range.“

It is due to the workbook we are referring to which is either not open or does not exist at all.

VBA Subscript Error in Arrays

When you declare the array as the dynamic array, and if you don’t use the word DIM or REDIM in VBAThe VBA Redim statement increases or decreases the storage space available to a variable or an array. If Preserve is used with this statement, a new array with a different size is created; otherwise, the current variable’s array size is changed.read more to define the length of an array, we usually get the VBA “Subscript out of range” error. For example, look at the below code.

Code:

Sub Macro3()

   Dim MyArray() As Long

   MyArray(1) = 25

End Sub

In the above, we have declared the variable as an array but have not assigned a start and ending point. Rather, we have assigned the first array the value of 25.

If we run this code using the F5 key or manually, we will get Run time error ‘9’: “Subscript out of Range.”

To fix this issue, we need to assign the length of an array by using the “ReDim” word.

Code:

Sub Macro3()

   Dim MyArray() As Long
   ReDim MyArray(1 To 5)

   MyArray(1) = 25

End Sub

This code does not give any errors.

How to Show Errors at the End of the VBA Code?

If you do not want to see the error while the code is up and running but needs an error list at the end, then you need to use the “On Error Resume” error handler. For example, look at the below code.

Code:

Sub Macro1()

Dim Wb As Workbook
On Error Resume Next
Set Wb = Workbooks("Salary Sheet.xlsx")

MsgBox Err.Description

End Sub

As we have seen, this code will throw Run time error 9: “Subscript out of range” in Excel VBA. But we must use the error handler On Error Resume Next in VBAVBA On Error Resume Statement is an error-handling aspect used for ignoring the code line because of which the error occurred and continuing with the next line right after the code line with the error.read more while running the code. So, we will not get any error messages. Rather, the end message box shows me the error description like this.

You can download the Excel VBA Subscript Out of Range Template here:- VBA Subscript Out of Range Template

Recommended Articles

This article has been a guide to VBA Subscript Out of Range. Here, we learned the Error called “Subscript out of range” (Run-time error’9′) in Excel VBA, along with practical examples and a downloadable template. Below you can find some useful Excel VBA articles: –

• Declare Global Variables in VBA
• VBA AutoFill
• Clear Contents in VBA
• Excel VBA UCase Function

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.

You can download this VBA Subscript out of Range Excel Template here – VBA Subscript out of Range Excel Template

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