Summary: The runtime error 9 in Excel usually occurs when you use different objects in a code or the object you are trying to use is not defined. This post will discuss the reasons behind the Excel VBA error "Subscript out of Range” and the solutions to resolve the issue. It will also mention an Excel repair tool that can help fix the error if it occurs due to corruption in worksheet.
Many users have reported encountering the error “Subscript out of range” (runtime error 9) when using VBA code in Excel. The error often occurs when the object you are referring to in a code is not available, deleted, or not defined earlier. Sometimes, it occurs if you have declared an array in code but forgot to specify the DIM or ReDIM statement to define the length of array.
Causes of VBA Runtime Error 9: Subscript Out Of Range
The error ‘Subscript out of range’ in Excel can occur due to several reasons, such as:
- Object you are trying to use in the VBA code is not defined earlier or is deleted.
- Entered a wrong declaration syntax of the array.
- Wrong spelling of the variable name.
- Referenced a wrong array element.
- Entered incorrect name of the worksheet you are trying to refer.
- Worksheet you trying to call in the code is not available.
- Specified an invalid element.
- Not specified the number of elements in an array.
- Workbook in which you trying to use VBA is corrupted.
Methods to Fix Excel VBA Error ‘Subscript out of Range’
Following are some workarounds you can try to fix the runtime error 9 in Excel.
Method 1: Check the Name of Worksheet in the Code
Sometimes, Excel throws the runtime error 9: Subscript out of range if the name of the worksheet is not defined correctly in the code. For example – When trying to copy content from one Excel sheet (emp) to another sheet (emp2) via VBA code, you have mistakenly mentioned wrong name of the worksheet (see the below code).
Private Sub CommandButton1_Click()
Worksheets("emp").Range("A1:E5").Select
Selection.Copy
Worksheets("emp3").Activate
Worksheets("emp3").Range("A1:E5").Select
ActiveSheet.Paste
Application.CutCopyMode = False
End Sub
When you run the above code, the Excel will throw the Subscript out of range error.
So, check the name of the worksheet and correct it. Here are the steps:
- Go to the Design tab in the Developer section.
- Double-click on the Command button.
- Check and modify the worksheet name (e.g. from “emp” to “emp2”).
- Now run the code.
- The content in ‘emp’ worksheet will be copied to ‘emp2’ (see below).
Method 2: Check the Range of the Array
The VBA error “Subscript out of range” also occurs if you have declared an array in a code but didn’t specify the number of elements. For example – If you have declared an array and forgot to declare the array variable with elements, you will get the error (see below):
To fix this, specify the array variable:
Sub FillArray()
Dim curExpense(364) As Currency
Dim intI As Integer
For intI = 0 to 364
curExpense(intI) = 20
Next
End SubMethod 3: Change Macro Security Settings
The Runtime error 9: Subscript out of range can also occur if there is an issue with the macros or macros are disabled in the Macro Security Settings. In such a case, you can check and change the macro settings. Follow these steps:
- Open your Microsoft Excel.
- Navigate to File > Options > Trust Center.
- Under Trust Center, select Trust Center Settings.
- Click Macro Settings, select Enable all macros, and then click OK.
Method 4: Repair your Excel File
The name or format of the Excel file or name of the objects may get changed due to corruption in the file. When the objects are not identified in a VBA code, you may encounter the Subscript out of range error. You can use the Open and Repair utility in Excel to repair the corrupted file. To use this utility, follow these steps:
- In your MS Excel, click File > Open.
- Browse to the location where the affected file is stored.
- In the Open dialog box, select the corrupted workbook.
- In the Open dropdown, click on Open and Repair.
- You will see a prompt asking you to repair the file or extract data from it.
- Click on the Repair option to extract the data as much as possible. If Repair button fails, then click Extract button to recover data without formulas and values.
If the “Open and Repair” utility fails to repair the corrupted/damaged macro-enabled Excel file, then try an advanced Excel repair tool, such as Stellar Repair for Excel. It can easily repair severely corrupted Excel workbook and recover all the items, including macros, cell comments, table, charts, etc. with 100% integrity. The tool is compatible with all versions of Microsoft Excel.
Conclusion
You may experience the “Subscript out of range” error while using VBA in Excel. You can follow the workarounds discussed in this blog to fix the error. If the Excel file is corrupt, then you can use Stellar Repair for Excel to repair the file. It’s a powerful software that can help fix all the issues that occur due to corruption in the Excel file. It helps to recover all the data from the corrupt Excel files (.xls, .xlsx, .xltm, .xltx, and .xlsm) without changing the original formatting. The tool supports Excel 2021, 2019, 2016, and older versions.
