Solved Microsoft Excel 2019 File Error The document cannot be saved
[Fixed] Excel VBA Runtime Error 9: Subscript Out of Range
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).
1 | Private Sub CommandButton1_Click() |
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:
1 | Sub FillArray() |
Method 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.
Also read:
- How to Soft Reset Infinix Zero 30 5G phone? | Dr.fone
- How to Factory Reset Samsung Galaxy A05s without Losing Data | Dr.fone
- Three Solutions to Hard Reset Tecno Spark 20C? | Dr.fone
- How to Factory Reset Motorola Moto E13 without Losing Data | Dr.fone
- How to Reset Honor Magic5 Ultimate Without the Home Button | Dr.fone
- How To Reset Your Apple iPhone 15? | Dr.fone
- Complete Tutorial for Motorola G54 5G Hard Reset | Dr.fone
- The way to get back lost data from X Flip
- The way to get back lost videos from Tecno Pova 5
- Complete Guide to Hard Reset Your Honor Play 7T | Dr.fone
- How To Repair iOS System Issues of Apple iPhone 14 Pro? | Dr.fone
- How To Repair Apple iPhone 13 Pro System? | Dr.fone
- Best Fixes For Asus ROG Phone 8 Hard Reset | Dr.fone
- How To Transfer WhatsApp From Apple iPhone 14 to other iPhone 15 Pro Max devices? | Dr.fone
- Simple ways to get lost messages back from X5
- Different Methods for Resetting Realme 12 5G Phones with Screen Locked and Not | Dr.fone
- Top 5 Ways to Fix Excel 2013 File Not Opening Error | Stellar
- How To Transfer WhatsApp From Apple iPhone 8 Plus to other iPhone 12 Pro Max devices? | Dr.fone
- How to Upgrade Apple iPhone 6 to the Latest iOS Version? | Dr.fone
- The way to recover deleted contacts on 12+ 5G without backup.
- Xiaomi won't play MOV videos, how to fix ?
- Hard Resetting an Google Pixel 8 Device Made Easy | Dr.fone
- Is your Vivo S17 Pro working too slow? Heres how you can hard reset it | Dr.fone
- How do I reset my Honor 70 Lite 5G Phone without technical knowledge? | Dr.fone
- How To Transfer WhatsApp From Apple iPhone 7 to other iPhone devices? | Dr.fone
- How to Reset a Tecno Pova 5 Pro Phone That Is Locked | Dr.fone
- How to Downgrade Apple iPhone 12 mini without Losing Anything? | Dr.fone
- How to Recover Apple iPhone SE (2020) Data From iOS iTunes? | Dr.fone
- Unlock iPhone SE
- How To Transfer WhatsApp From Apple iPhone 13 to other iPhone 12 Pro Max devices? | Dr.fone
- Reset pattern lock Tutorial for Infinix Note 30
- How To Transfer Data From Apple iPhone 6s Plus To Others devices? | Dr.fone
- Solutions to Repair Corrupt PDF v1.3 File | Stellar
- Undelete lost photos from Y100t.
- Undelete lost call logs from Realme GT 5 Pro
- Solutions to Repair Corrupt PDF v1.3 File
- How to Factory Reset Itel P55+ in 5 Easy Ways | Dr.fone
- Unlock android phone if you forget the Galaxy A05 password or pattern lock
- How To Transfer WhatsApp From Apple iPhone 7 to other iPhone 15 Pro devices? | Dr.fone
- Three Solutions to Hard Reset Motorola Moto G13? | Dr.fone
- In 2024, How To Fix Apple iPhone 15 Could Not Be Activated/Reached Issue
- How to Unlock iPhone 15 Passcode without Computer?
- Complete Tutorial to Use VPNa to Fake GPS Location On Google Pixel 8 Pro | Dr.fone
- In 2024, What Is a SIM Network Unlock PIN? Get Your Samsung Galaxy F04 Phone Network-Ready
- Full Guide How To Fix Connection Is Not Private on Oppo A18 | Dr.fone
- Solved Warning Camera Failed on Honor X8b | Dr.fone
- Super Easy Ways To Deal with Oppo Reno 10 5G Unresponsive Screen | Dr.fone
- How does the stardust trade cost In pokemon go On Vivo Y78+? | Dr.fone
- In 2024, Unveiling the Secrets of Shopee Livestream Selling System
- How To Transfer WhatsApp From iPhone X to others devices? | Dr.fone
- In 2024, Reset iTunes Backup Password Of iPhone 15 Pro Prevention & Solution
- Remove Device Supervision From your Apple iPhone 6s | Dr.fone
- Detailed guide of ispoofer for pogo installation On Poco X5 Pro | Dr.fone
- In 2024, A Step-by-Step Guide on Using ADB and Fastboot to Remove FRP Lock from your Tecno Spark 10C
- Three Ways to Sim Unlock Vivo S17
- 6 Proven Ways to Unlock Lava Blaze Pro 5G Phone When You Forget the Password
- How to Transfer Photos from Vivo Y100A to New Android? | Dr.fone
- In 2024, Can I Bypass a Forgotten Phone Password Of Xiaomi Redmi Note 12 Pro 4G?
- 3 Ways to Track Vivo V27 Pro without Them Knowing | Dr.fone
- In 2024, Complete Fixes To Solve Apple iPhone XR Randomly Asking for Apple ID Password | Dr.fone
- iSpoofer is not working On Oppo F25 Pro 5G? Fixed | Dr.fone
- All Things You Need to Know about Wipe Data/Factory Reset For Nokia 130 Music | Dr.fone
- Want to Uninstall Google Play Service from Tecno Camon 20 Pro 5G? Here is How | Dr.fone
- How to Track OnePlus Nord N30 SE Location without Installing Software? | Dr.fone
- Title: Solved Microsoft Excel 2019 File Error The document cannot be saved
- Author: Ian
- Created at : 2024-04-30 12:04:20
- Updated at : 2024-05-01 20:07:04
- Link: https://techidaily.com/solved-microsoft-excel-2019-file-error-the-document-cannot-be-saved-by-stellar-guide/
- License: This work is licensed under CC BY-NC-SA 4.0.