VBA Subscript Out of Range

Excel VBA Subscript Out of Range

VBA Subscript out of range error occurs because the object we are trying to access doesn’t exist. This is an error type in VBA coding and this is a “Run Time Error 9”. It is important to understand the concepts to write efficient code and it is even more important to understand the error of your VBA code to debug the code efficiently.

If your coding error and you don’t know what is that error is when you are gone.

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

On a similar note in this article, we will see one of the important error we usually encounter regularity i.e. “Subscript Out of Range” error in Excel VBA.

What is Subscript out of Range Error in Excel VBA?

For an example if you are referring the sheet which is not there is the workbook then we get Run time error 9: “Subscript out of Range”.

If you click on 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 Subscript Out of Range Error Occurs?

As I told as a doctor important to find the deceased before thinking about the medicine. VBA Subscript out of range error occurs when the line of code doesn’t read the object we entered.

For an example look at the below image, I have three sheets names as Sheet1, Sheet2, Sheet3.

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

Code:

Sub Macro2()

   Sheets("Sales").Select

End Sub

If I run this code using F5 key or manually, I will end up getting the Run time error 9: “Subscript out of Range”.

This is because I tried accessing the worksheet object “Sales” which do not exist in the workbook. This is a run time error because this error occurred while running the code.

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

Code:

Sub Macro1()

  Dim Wb As Workbook

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

End Sub

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

This is due to the workbook I am referring, which is either not open as of now or doesn’t exist at all.

VBA Subscript Error in Arrays

When you declare the array as the dynamic array and if don’t use the word DIM or REDIM to define the length of an array we usually get 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, I have declared the variable as an array but I have not assigned start and ending point rather I have straight away assigned the first array the value of 25.

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

To fix this issue I need to assign the length of an array by using 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 don’t want to see the error while the code is up and running but need error list at the end then you need to use “On Error Resume” error handler. 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 I have to use the error handler “On Error Resume Next” while running the code we will not get any error messages rather at 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 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 with along with practical examples and a downloadable template. Below you can find some useful excel VBA articles –

• VBA Font Color
• Declare Global Variables in VBA
• VBA Count Function | Examples
• How to Use AutoFill Method in VBA?
• Clear Contents in Excel VBA
• Uppercase using VBA
• Lowercase using VBA
• Progress Bar using VBA
• VBA Value