The error ‘Subscript out of range’ in Excel can occur due to several reasons, such as:<\/p>
- \n
- Object you are trying to use in the VBA code is not defined earlier or is deleted.<\/li>\n\n\n\n
- Entered a wrong declaration syntax of the array.<\/li>\n\n\n\n
- Wrong spelling of the variable name.<\/li>\n\n\n\n
- Referenced a wrong array element.<\/li>\n\n\n\n
- Entered incorrect name of the worksheet you are trying to refer.<\/li>\n\n\n\n
- Worksheet you trying to call in the code is not available.<\/li>\n\n\n\n
- Specified an invalid element.<\/li>\n\n\n\n
- Not specified the number of elements in an array.<\/li>\n\n\n\n
- Workbook in which you trying to use VBA is corrupted.<\/li>\n<\/ul>
Methods to Fix Excel VBA Error ‘Subscript out of Range’<\/strong><\/h2>
Following are some workarounds you can try to fix the runtime error 9 in Excel.<\/p>
Method 1: Check the Name of Worksheet in the Code<\/strong><\/h3>
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).<\/p>
Private Sub CommandButton1_Click()\nWorksheets(\"emp\").Range(\"A1:E5\").Select\nSelection.Copy\nWorksheets(\"emp3\").Activate\nWorksheets(\"emp3\").Range(\"A1:E5\").Select\nActiveSheet.Paste\nApplication.CutCopyMode = False\nEnd Sub<\/code><\/pre><\/pre>
![\"VBA](\"https:\/\/www.stellarinfo.com\/blog\/wp-content\/uploads\/2023\/04\/VBA-error-subscript-out-of-range-when-incorrect-name.jpg\")
<\/figure>When you run the above code, the Excel will throw the Subscript out of range error.<\/p>
So, check the name of the worksheet and correct it. Here are the steps:<\/p>
- \n
- Go to the Design<\/strong> tab in the Developer<\/strong> section.<\/li>\n\n\n\n
- Double-click on the Command<\/strong> button.<\/li>\n\n\n\n
- Check and modify the worksheet name (e.g. from “emp” to “emp2”).<\/li>\n<\/ul>
![\"Modified](\"https:\/\/www.stellarinfo.com\/blog\/wp-content\/uploads\/2023\/04\/modified-code-from-emp-to-emp2.jpg\")
<\/figure>- \n
- Now run the code.<\/li>\n\n\n\n
- The content in ‘emp’ worksheet will be copied to ‘emp2’ (see below).<\/li>\n<\/ul>
![\"Content](\"https:\/\/www.stellarinfo.com\/blog\/wp-content\/uploads\/2023\/04\/content-copied-from-emp-to-emp2.jpg\")
<\/figure>Method 2: Check the Range of the Array<\/h3>
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):<\/p>
![\"Runtime](\"https:\/\/www.stellarinfo.com\/blog\/wp-content\/uploads\/2023\/04\/run-time-error-9-when-not-declared-array.jpg\")
<\/figure>To fix this, specify the array variable:<\/p>
Sub FillArray()\nDim curExpense(364) As Currency\nDim intI As Integer\nFor intI = 0 to 364\ncurExpense(intI) = 20\nNext\nEnd Sub<\/code><\/pre>Method 3: Change Macro Security Settings<\/strong><\/strong><\/h3>
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:<\/p>
- \n
- Open your Microsoft Excel.<\/li>\n\n\n\n
- Navigate to File > Options > Trust Center<\/strong>.<\/li>\n\n\n\n
- Under Trust Center<\/strong>, select Trust Center Settings<\/strong>.<\/li>\n\n\n\n
- Click Macro Settings<\/strong>, select Enable all macros<\/strong>, and then click OK<\/strong>.<\/li>\n<\/ul>
![\"Macro](\"https:\/\/www.stellarinfo.com\/blog\/wp-content\/uploads\/2023\/04\/macro-settings-in-trust-center.jpg\")
<\/figure>Method 4: Repair your Excel File<\/strong><\/h3>
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:<\/p>
- \n
- In your MS Excel, click File > Open<\/strong>.<\/li>\n\n\n\n
- Browse to the location where the affected file is stored.<\/li>\n\n\n\n
- In the Open <\/strong>dialog box, select the corrupted workbook.<\/li>\n\n\n\n
- In the Open<\/strong> dropdown, click on Open and Repair<\/strong>.<\/li>\n\n\n\n
- You will see a prompt asking you to repair the file or extract data from it.<\/li>\n\n\n\n
- Click on the Repair<\/strong> option to extract the data as much as possible. If Repair<\/strong> button fails, then click Extract <\/strong>button to recover data without formulas and values.<\/li>\n<\/ul>
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.<\/p>
Conclusion<\/strong><\/h2>
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<\/a> 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.<\/p>\n","protected":false},"excerpt":{"rendered":"
- Under Trust Center<\/strong>, select Trust Center Settings<\/strong>.<\/li>\n\n\n\n
- Double-click on the Command<\/strong> button.<\/li>\n\n\n\n
- Go to the Design<\/strong> tab in the Developer<\/strong> section.<\/li>\n\n\n\n