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?
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
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
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
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 Nextis used to ignore errors and allow the code to continue executing.
- After attempting to access the array element,
If Err.Number <> 0 Thenis used to check if an error occurred.
- If there was an error, a message box is displayed, and
Err.Clearis used to clear the error.
On Error GoTo 0is 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.