Solved: How do I fix the VBA runtime error 9 subscript out of range in Excel

This blog article explains how to solve the VBA subscript out of range error in Excel. The error occurs when you try to access an element or an object that does not exist or is out of the defined range, such as a non-existent worksheet, a closed workbook, or an invalid array element. The error can be fixed by checking the code and making sure that the references are valid and within the range, or by using some methods to avoid the error, such as using the UBound function, the ReDim statement, or the For Each…Next loop.

Problem

If you use VBA macros in Excel, you may encounter the runtime error 9: subscript out of range. This error occurs when you try to access an element or an object that does not exist or is out of the defined range. For example, you may get this error if you refer to a worksheet that is not in the workbook, or an array index that is beyond the array size. In this article, we will explain the common causes of the VBA subscript out of range error and how to fix it with some easy solutions.

Cause 1: Referencing a Non-Existent Worksheet

One of the most common causes of the VBA subscript out of range error is when you try to reference a worksheet that is not in the workbook. For example, if you have a workbook with only two worksheets named Sheet1 and Sheet2, and you write the following code:

Sub Example1()
    Sheets("Sheet3").Activate
End Sub

This is because there is no Sheet3 in the workbook, so the code is trying to access a non-existent object in the Sheets collection.

Solution 1

There are several ways to fix this error, depending on your situation. Here are some possible solutions:

• Create the worksheet that you want to reference. For example, you can add a new worksheet and name it Sheet3, or you can copy an existing worksheet and rename it Sheet3.
• Check the spelling of the worksheet name and make sure it matches the name in the workbook. Sometimes, the error may be caused by a typo or a case-sensitive issue. For example, if the worksheet name is Sheet3, but you write Sheets(“sheet3”).Activate, you will get the error because VBA is case-sensitive.
• Use the index number of the worksheet instead of the name. For example, you can write Sheets(3).Activate to activate the third worksheet in the workbook. However, this method is not recommended because the index number may change if you add or delete worksheets in the workbook.
• Use the For Each…Next loop to loop through all the worksheets in the workbook and perform the same action on each worksheet. For example, you can write the following code to unhide all the worksheets in the workbook:

Sub Example2()
    Dim ws As Worksheet
    For Each ws In Worksheets
        ws.Visible = xlSheetVisible
    Next ws
End Sub

Cause 2: Referencing a Closed Workbook

Another common cause of the VBA subscript out of range error is when you try to reference a workbook that is not open. For example, if you have a closed workbook named Employees.xlsx on your computer, and you write the following code:

Sub Example3()
    Workbooks("Employees.xlsx").Activate
End Sub

This is because a closed workbook is not part of the Workbooks collection, which consists of all the open workbooks on your computer.

Solution 2

There are also several ways to fix this error, depending on your situation. Here are some possible solutions:

• Open the workbook that you want to reference before running the code. For example, you can write the following code to open the Employees.xlsx workbook and then activate it:

Sub Example4()
    Workbooks.Open ("Employees.xlsx")
    Workbooks("Employees.xlsx").Activate
End Sub

• Use the For Each…Next loop to check whether the workbook that you want to reference is open or not. For example, you can write the following code to loop through all the open workbooks and compare the name of each workbook with the name of the workbook that you want to reference. If the workbook is open, the code will activate it. If the workbook is not open, the code will display a message box and exit the sub:

Sub Example5()
    Dim wb As Workbook
    Dim wbName As String
    wbName = "Employees.xlsx"
    For Each wb In Workbooks
        If wb.Name = wbName Then
            wb.Activate
            Exit Sub
        End If
    Next wb
    MsgBox "The workbook " & wbName & " is not open."
End Sub

• Use the On Error Resume Next statement to ignore the error and continue the execution of the code. For example, you can write the following code to try to activate the Employees.xlsx workbook. If the workbook is not open, the code will not display the error message, but it will also not activate the workbook. Therefore, this method is not recommended because it may cause unexpected results or hide other errors in your code:

Sub Example6()
    On Error Resume Next
    Workbooks("Employees.xlsx").Activate
End Sub

Cause 3: Referencing an Invalid Array Element

The third common cause of the VBA subscript out of range error is when you try to reference an array element that is not valid or is out of the defined range. For example, if you have an array named arr with 5 elements, and you write the following code:

Sub Example7()
    Dim arr(1 To 5) As Integer
    arr(1) = 10
    arr(2) = 20
    arr(3) = 30
    arr(4) = 40
    arr(5) = 50
    MsgBox arr(6)
End Sub

This is because the array arr has only 5 elements, so the index 6 is not valid or is out of the range of the array.

Solution 3

There are also several ways to fix this error, depending on your situation. Here are some possible solutions:

• Check the declaration of the array and make sure it matches the number of elements that you want to store in the array. For example, if you want to store 6 elements in the array arr, you can write the following code to declare the array with 6 elements:

Sub Example8()
    Dim arr(1 To 6) As Integer
    arr(1) = 10
    arr(2) = 20
    arr(3) = 30
    arr(4) = 40
    arr(5) = 50
    arr(6) = 60
    MsgBox arr(6)
End Sub

• Use the UBound function to get the upper bound of the array and avoid referencing an element that is beyond the array size. For example, you can write the following code to loop through all the elements in the array arr and display them in a message box. The code will use the UBound function to get the highest index of the array and avoid the error:

Sub Example9()
    Dim arr(1 To 5) As Integer
    Dim i As Integer
    arr(1) = 10
    arr(2) = 20
    arr(3) = 30
    arr(4) = 40
    arr(5) = 50
    For i = 1 To UBound(arr)
        MsgBox arr(i)
    Next i
End Sub

• Use the ReDim statement to resize the array dynamically and add more elements to the array as needed. For example, you can write the following code to declare the array arr with 5 elements, and then use the ReDim statement to increase the size of the array to 6 elements and assign a value to the new element:

Sub Example10()
    Dim arr(1 To 5) As Integer
    arr(1) = 10
    arr(2) = 20
    arr(3) = 30
    arr(4) = 40
    arr(5) = 50
    ReDim Preserve arr(1 To 6)
    arr(6) = 60
    MsgBox arr(6)
End Sub

Note: The Preserve keyword is used to keep the existing values in the array when resizing it. If you omit the Preserve keyword, the array will be erased and all the elements will be set to their default values.

Frequently Asked Questions (FAQs)

Question: How do I find the line of code that causes the VBA subscript out of range error?

Answer: When you get the error message, you can click the Debug button to go to the Visual Basic Editor and see the line of code that causes the error. The line of code will be highlighted in yellow. You can also use the Immediate window to check the values of the variables or objects that are involved in the error.

Question: How do I prevent the VBA subscript out of range error from occurring?

Answer: The best way to prevent the error from occurring is to write clear and accurate code that follows the VBA syntax and rules. You should also use proper naming conventions for your variables and objects, and avoid using reserved words or keywords as names. You should also test and debug your code frequently and use error handling techniques to handle any possible errors gracefully.

Question: How do I fix the VBA subscript out of range error in a user-defined function?

Answer: If you get the error in a user-defined function, you can use the same methods as described above to fix it. However, you should also make sure that the function returns a valid value that matches the data type of the function. For example, if you have a function that returns an integer value, you should not return a string value or an array value. For example, if you have the following function:

Function SumArray(arr() As Integer) As Integer
    Dim i As Integer
    Dim sum As Integer
    sum = 0
    For i = LBound(arr) To UBound(arr)
        sum = sum + arr(i)
    Next i
    SumArray = sum
End Function

You should not write the following code to call the function:

Sub Example11()
    Dim arr(1 To 5) As Integer
    Dim result As String
    arr(1) = 10
    arr(2) = 20
    arr(3) = 30
    arr(4) = 40
    arr(5) = 50
    result = SumArray(arr)
    MsgBox result
End Sub

This will cause the error because the function returns an integer value, but the result variable is declared as a string. You should write the following code instead:

Sub Example12()
    Dim arr(1 To 5) As Integer
    Dim result As Integer
    arr(1) = 10
    arr(2) = 20
    arr(3) = 30
    arr(4) = 40
    arr(5) = 50
    result = SumArray(arr)
    MsgBox result
End Sub

Summary

The VBA subscript out of range error is a common runtime error that occurs when you try to access an element or an object that does not exist or is out of the defined range. The error can be caused by various reasons, such as referencing a non-existent worksheet, a closed workbook, or an invalid array element. To fix the error, you need to check the code and make sure that the references are valid and within the range. You can also use some methods to avoid the error, such as using the UBound function, the ReDim statement, or the For Each…Next loop. You should also test and debug your code frequently and use error handling techniques to handle any possible errors gracefully.

Disclaimer: The information provided in this article is for educational purposes only and does not constitute professional advice. The author and the publisher are not liable for any errors or omissions in this article or for any damages arising from the use of this article. The users are advised to consult a qualified VBA expert before applying any of the solutions in this article. The users are also responsible for complying with the terms and conditions of the software and websites that are mentioned in this article. The author and the publisher do not endorse or promote any of the software or websites that are mentioned in this article. The users are advised to exercise caution and discretion when using any of the software or websites that are mentioned in this article.