Run Time Error 9 VBA Subscript out of Range

Run Time Error 9 VBA Subscript out of range is one of the most common errors. But it’s actually one of the most useful error messages. Excel uses this error to inform you that a cell, range, worksheet, or workbook that you have referenced does not exist. Therefore it’s out of range. In this article, I will take you through everything you need to know about this error. At the end of this article, you will be able to identify the problem in your VBA code and fix it.

What is VBA Subscript out of range?

VBA subscript out of range is a common Excel error that appears in a pop-up window when you try to run a code that references materials that have been removed or do not exist. For example, if you reference sheet1 then rename it to something else or delete it then you will face Run-time error ‘9’ VBA subscript out of range. 

It’s a very easy error to fix because you just need to correct the references. For instance, check for typos, restore deleted files, or reference available materials.

5 Ways to fix this error

You can fix this error in several ways depending on your code. Here are the best solutions

Why does the Subscript out-of-range error occur?

Undefined Array

One of the main reasons why the error occurs is because of an undefined array. When declaring an array you need to give a start and an end position. For example

Sub undefinedarray()

    Dim B(5 To 15) As Integer

    B(4) = 55

    MsgBox B(6)

End Sub

4 is does not appear in the range 5 to 15 that I have specified. So when you run this code you get the error

But when I use 6 which is defined then the VBA works as expected

Sub undefinedarray()

    Dim B(5 To 15) As Integer

    B(6) = 55

    MsgBox B(6)

End Sub

Invalid Array

Apart from an undefined array you may also experience this error due to an invalid array. This occurs due to not specifying start and end positions. For example

Sub undefinedarray()

    Dim B() As Integer

    B(1) = 55

    MsgBox B

End Sub

Thus it causes an error

However, you can fix this by specifying the start and end positions

Sub undefinedarray()

    Dim B(1 To 100) As Integer

    B(1) = 55

    MsgBox B(1)
End Sub

Missing Worksheet

If your VBA code is referencing a deleted or misspelled work sheet then you are going to face the error. For example the code below references Sheet11 but it does not exist. 

Sub RefreshWorksheet()
    ' Call the RefreshWorksheet macro every 5 seconds
    Application.OnTime Now + TimeValue("00:00:05"), "RefreshWorksheet"
    
    ' Replace "Sheet11" with the name of your worksheet
    ThisWorkbook.Sheets("Sheet11").Calculate
End Sub

Sub StartRefreshTimer()
    ' Call the RefreshWorksheet macro for the first time
    Application.OnTime Now + TimeValue("00:00:01"), "RefreshWorksheet"
End Sub

Sub StopRefreshTimer()
    ' Cancel the scheduled RefreshWorksheet macro
    On Error Resume Next
    Application.OnTime Now + TimeValue("00:00:05"), "RefreshWorksheet", , False
    On Error GoTo 0
End Sub

Take a look at the image and see there is no Sheet 11.

So when I run it the Subscript out-of-range error appears

The solution is to label the worksheet that you are referring to correctly. If it is misspelled then correct it. Otherwise, if the worksheet has been deleted then you need to restore it. 

Learn about other common Excel errors

Non-existent  Workbook

Another reason why this error occurs is when the VBA code references a non-existent workbook

Non-existent means it’s missing or has not been activated. In some cases, VBA code can only read the contents of Workbooks that are active. If this is the case in your situation then you will definitely face the Subscript out-of-range error. 

Mispelt Worksheets or workbooks

Sometimes the worksheets or workbooks may be available. However, a small misspelling can cause the error. 

How to Fix the VBA Subscript out of range error

Use valid array

Make sure to plan well in advance so that the array falls in place between the start and end position. 

Restore deleted sheets or workbooks

You can easily solve the error by restoring the deleted sheets or workbooks. Of course, you only need to restore it if it’s in reference and has been deleted. Then run the code again and this time you will not face the error.

Use the VBA Code generator to debug the code

If you still don’t understand where the issue is then you take advantage of a VBA generator. Use it to debug your code or generate new code instantly that works. 

Use error handling to ignore the error 

You can use error handling techniques to get rid of the error. For example

Sub HandleSubscriptOutOfRangeError()
    Dim myArray(1 To 5) As Integer
    On Error Resume Next

    ' Replace with the index you want to access
    Dim index As Integer
    index = 10

    Dim value As Integer
    value = myArray(index)

    ' Check for subscript out of range error
    If Err.Number <> 0 Then
        MsgBox "Index is out of range."
        Err.Clear ' Clear the error
    Else
        MsgBox "Value at index " & index & ": " & value
    End If

    On Error GoTo 0 ' Reset error handling to default
End Sub

In this example:

• On Error Resume Next is used to ignore errors and allow the code to continue executing.
• After attempting to access the array element, If Err.Number <> 0 Then is used to check if an error occurred.
• If there was an error, a message box is displayed, and Err.Clear is used to clear the error.
• On Error GoTo 0 is used to reset error handling to its default state.

Note that using On Error Resume Next should be done with caution, as it suppresses all errors, not just subscript out of range errors. Ensure that you handle errors appropriately to prevent unexpected behavior in your code.