What Is VBA Subscript Out Of Range?
VBA Subscript out of range error is a run-time error that occurs when the object or variable specified is not available in the code. In all programming languages, errors are bound to happen, and VBA is no different. It is important to know what is that error and why that error occurs to fix that error.
For example, if you have written a code to select the worksheet named ‘Sheet1’ in the workbook, and if that worksheet is not available in the workbook, then we will get the VBA Subscript Out of Range error. The error looks as shown below.
Key Takeaways
- Subscript Out of Range is an error that occurs when we try to access an object that doesn’t exist in the workbook.
- Array length also causes the subscript out of range error if the assigned array value is out of size.
- On Error Goto will prevent the error and skip the subprocedure.
- On Error Goto Label will help us to show the error message by using the Err.Description.
- The label name can be anything, but the same label name should be used when displaying the error message.
Subscript Out Of Range Error In Excel VBA
Subscript out of range error is a Run time error in VBA. For example, look at the following VBA code.
We have written a VBA code to activate the workbook named ‘VBA Error Examples.xlsm’ from the active workbook that we are working on. However, the workbook is not opened in our system or computer. When we run this code, we will get the VBA Subscript out of range error, as shown in the following image.
As we can see, we have the Run-time error ‘9’: followed by the error named ‘Subscript out of range.’
When we click on the End button, it will end the sub-procedure that we are executing, and if we click on Debug, it will take us to the VBA editor page where we can edit the code and fix the issue. The help button will take us to the Microsoft website page.
Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)
If you want to learn Excel and VBA professionally, then Excel VBA All in One Courses Bundle (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.
Why Subscript Out Of Range Error Occurs?
As we discussed earlier, VBA subscript out of range is an error, but as a coder, we need to know why this error occurs before we think about the solution.
We will show you all the possible scenarios of facing this error.
1. Subscript Out of Range Error In VBA For Nonexistent Worksheet
We can access any existing worksheets in the workbook. For example, we have the following 4 worksheets in the workbook.
We have written the code to select the worksheet ‘Sheet1’ like the following.
Sub Subscript_Error_Worksheet()
Worksheets(“Sheet1”).Select
End Sub
When we run this code, it will select the worksheet named ‘Sheet1.’ Now, let us delete the worksheet ‘Sheet1.’
Let us run the code and see what happens
We got the subscript out of range error because we are trying to access the worksheet that doesn’t exist in the workbook.
2. Subscript Out Of Range Error In VBA For Nonexistent Workbook
Similar to the previous example, if we try to access the workbook that doesn’t exist, then, we will get the VBA subscript out of range error.
For example, look at the following code.
Sub Subscript_Error_Workbook()
Workbooks(“Sales Report.xlsm”).Activate
End Sub
In the above code, we have written a code to activate the workbook “Sales Report.xlsm”. If this workbook is open on our computer, it will activate that workbook. But, if that workbook is not open, then, we will get the VBA subscript out of range error.
3. Shorthand Script
When we use the shorthand script, we need to be very careful. For example, if we want to select cell A10 in the active sheet, we can write the code as shown below:
ActiveSheet.Range(“A10”).Select
However, we can also use the shorthand script like the following:
[A10].Select
This will also select cell A10 in the active worksheet. However, we need to be extra careful while applying shorthand scripts because it not only leads to VBA Subscript Out of Range errors but many other errors as well.
How To Fix Subscript Out Of Range Error?
Once we get to know the reason for the error, it is relatively easy to fix that error. Following are the solution to fix VBA subscript out of range error.
For Worksheet Absence: In our earlier example, we were trying to access the worksheet ‘Sheet1’, which isn’t there. To fix this error, we need to insert a sheet and name the sheet ‘Sheet1.’
For Workbook Absence: We were trying to access the workbook “Sales Report.xlsm” but that workbook was not open to access. VBA cannot access the workbook, which isn’t open on our computer; so, to fix this issue, we need to keep the referenced workbook open and then run the macro to solve this issue.
VBA Subscript Error In Arrays
VBA Arrays are the commonly used technique to avoid multiple variables to store the same set of data. However, we need to be a little cautious while using the arrays in coding. The Subscript Out of Range error for Array in VBA is bound to happen as well.
We will discuss the possible scenarios of Subscript Out of Range error for Array in VBA.
#1 – Subscript Out of Range Error In VBA For Undefined Array Elements
When we declare the variable as an array, we usually define the length of the array by giving the start and end positions of the array.
For example, look at the following array defined in VBA coding.
Sub Subscript_Error_Array()
Dim K(3 To 5) As IntegerEnd Sub
We have defined the variable ‘K’ as an array to hold the position from 3 to 4 i.e., we need to assign values from the 3rd position to the 5th position only.
Now lets us assign the value for the 6th position.
For the array variable ‘K,’ for the 6th position, we have assigned the integer value 55 and trying to display the value in the message box.
Let’s run the code and see what happens.
We are getting the error ‘Subscript out of range’ because the array length is limited to 5 and we are assigning the value for the 6th position.
On similar lines, look at the following code.
Sub Subscript_Error_Array()
Dim K (3 To 5) As Integer
K(2) = 55
MsgBox K
End Sub
In this code, we are assigning the value for the 2nd position of the array, but here also, we get the same error because the array’s starting position is 3rd, but we are assigning the value for the 2nd position. The position of the array is out of range; hence, we get the subscript out of range error.
#2 – Subscript Out of Range Error In VBA For Invalid Array
There is another type of possible error that may come in the array because of an invalid array. In the previous example, we decided the length of the array while defining the variable. Inside the parenthesis, we have given the start and end positions of the array.
In some cases, we define the array but do not decide on the length of the array well in advance. For example, look at the following code.
Sub Subscript_Error_Array()
Dim K () As Integer
K(1) = 55
MsgBox K
End Sub
First, we have declared the array but have not decided on the length of the array inside the parenthesis. Next, we assigned the value to it by taking the first position.
We will get the subscript out of range error like the following when we run this code.
Before deciding on the start and end positions of the array, we assigned the value to the array variable; hence, we ended up getting the subscript out of range error.
To fix these issues, we need to first assign the start and end position of the array variable by using the REDIM statement.
Sub Subscript_Error_Array()
Dim K () As Integer
REDIM k (1 to 5)
K(1) = 55
MsgBox K
End Sub
As we can see, we have used the REDIM statement to assign the start and end positions of the array and then assigned the value to the array position value.
How To Show Errors At The End Of The VBA Code?
Errors are part and parcel of programming language coding. However, efficient coding requires efficient ways of handling those errors. Instead of showing the error, let the code run, and at the end, we will display a message box saying some error has occurred and that is the reason the program was not able to complete.
VBA allows us to show the errors at the end of the macro by using error handling techniques i.e., “On Error Goto”.
For example, look at the following code.
Sub Subscript_Error_Array()
Dim K (3 To 5) As Integer
K(6) = 55
MsgBox K
End Sub
For the array length 6th position, we assign the value, but the array maximum size is 5. We already know that this will give us an error subscript out of range.
To tackle this error, we can use the following line of code “On Error Goto”
Sub Subscript_Error_Array()
Dim K (3 To 5) As Integer
On Error GoTo MyError:
K(6) = 55
MsgBox K
MyError:
MsgBox Err.Description
End Sub
Before we assign a value to the array variable, we have written the code “On Error Goto MyError”. Whenever the error occurs, it doesn’t show the error message but jumps into the MyError message.
In MyError, we have used the Err.Description function that shows the error message in words like the following image.
This looks much better than the error message box for the users.
Important Things To Note
- When we reference a worksheet or workbook, the name of the worksheet or workbook should be exact words.
- Array variable length should be defined well in advance before we assign the values to the array variable.
- Even though the array length is not decided at the time of defining, we can still assign the start and end of the array by using the REDIM statement.
- Use the F8 key to run the code line by line to examine the error line causing the issue.
Frequently Asked Questions
Subscript out of range error occurs when we try to reference the object that doesn’t exist in the workbook or an array with no size or out of size.
For example, look at the following code.
Worksheets(“Sales”).Activate
The above code will activate the worksheet named “Sales”. However, if the worksheet “Sales” doesn’t exist, then, we will get the subscript out of range error.
We need to create a required worksheet or workbook or use them on an error to go to the label to handle the error effectively.
Subscript out of range comes up when we try to activate the worksheet, not in the workbook, or activate the workbook that is not open on our computer.
This error also occurs if the given worksheet name or workbook name is not exactly the same as the desired worksheet or workbook.
Download Template
This article must be helpful to understand the VBA Subscript Out of Range, with its formula and examples. You can download the template here to use it instantly.
Recommended Articles
This has been a guide to VBA Subscript Out of Range. Here we explain why it occurs and how to fix them with examples and downloadable excel template. You may learn more from the following articles –
Leave a Reply