How to Fix Runtime Error in Excel: Step-by-Step Guide

Runtime errors in Excel usually appear as a pop-up saying “Runtime error ‘9’: Subscript out of range” or similar, and they typically occur when a macro or VBA (Visual Basic for Applications) code tries to access an object that doesn’t exist. This guide walks you through the most common causes and fixes, from simple settings tweaks to editing the code itself. You’ll learn how to identify the error, fix it safely, and prevent it from happening again.

What You’ll Need

Access to the Excel file that triggers the error (if it’s a specific file).
If the error appears when opening Excel itself, you’ll need to be able to start Excel in Safe Mode (hold Ctrl while opening).
Basic familiarity with the Visual Basic Editor (VBE) – you only need to open it and view code, not write complex macros.
Administrator rights on your computer if the issue involves add-ins or system files (usually not required for most fixes).

Step-by-Step

Identify when the error occurs. Note whether it happens when you open a specific workbook, when you run a macro, or when you perform a certain action (like clicking a button). This tells you whether the problem is in the file’s code or in Excel’s startup configuration.
Open the Visual Basic Editor. Press Alt + F11 to open the VBE. If the error is tied to a specific workbook, make sure that workbook is open first.
Find the offending code. In the VBE, look at the “Project Explorer” (usually on the left). Expand the project for the workbook that’s causing the error. Double-click on modules, sheet objects, or “ThisWorkbook” to see the code. The error message often highlights the line that caused it – it will be highlighted in yellow when you click “Debug” on the error dialog.
Check for common code issues. The most frequent causes are:
- Subscript out of range (Error 9): The code references a worksheet, workbook, or array index that doesn’t exist. For example, Sheets("Data") when there’s no sheet named “Data”.
- Object variable not set (Error 91): A variable that should hold an object (like a Range or Worksheet) is empty. This often happens when a Set statement is missing or fails.
- Type mismatch (Error 13): The code tries to assign a value of one type to a variable of another type (e.g., putting text into a numeric variable).
Fix the code. Based on the error, make the correction. For example, if the code references a sheet that doesn’t exist, either rename the sheet to match the code, or change the code to use the correct sheet name. If you’re not comfortable editing code, you can often work around it by renaming objects to match what the code expects.
Disable add-ins. If the error occurs when Excel starts or when you open any file, an add-in might be the culprit. Go to File > Options > Add-ins. At the bottom, select “COM Add-ins” from the dropdown and click “Go”. Uncheck any add-ins that aren’t essential (especially third-party ones), then restart Excel.
Repair Office. If the error persists across all files, your Office installation might be corrupted. Go to Control Panel > Programs > Programs and Features, find Microsoft Office, right-click and choose “Change”, then select “Quick Repair” (or “Online Repair” if that doesn’t work). This can take a few minutes.
Check for macro security settings. If macros are disabled, some code may not run properly and cause errors. Go to File > Options > Trust Center > Trust Center Settings > Macro Settings. Select “Disable VBA macros with notification” (recommended) or “Enable all macros” (temporarily, for testing only).
Update Excel. Ensure you have the latest updates installed via File > Account > Update Options > Update Now. Some runtime errors are fixed by Microsoft’s patches.
Test the fix. After making changes, save the file and reopen it. If the error was in a macro, run it again. If it no longer appears, you’re done.

Common Mistakes

Ignoring the error message details. The error code and description tell you exactly what’s wrong. Skipping this step leads to random guessing.
Editing code without a backup. Always save a copy of the workbook before modifying VBA code. A small mistake can break the entire macro.
Disabling all macros permanently. This is a security risk and may not even fix the error if the problem is in the code itself.
Deleting sheets or modules to “clean up” without checking if other code references them. This often creates new runtime errors.
Using the wrong sheet name. Excel sheet names are case-insensitive, but they must match exactly (including spaces). A common typo is “Sheet1” vs “Sheet 1”.

Tips

Use the Debug button. When the error dialog appears, click “Debug” to see the exact line of code that failed. This is the fastest way to pinpoint the issue.
Check the Immediate Window. In the VBE, press Ctrl + G to open the Immediate Window. You can type ?Sheets.Count to see how many sheets exist, or ?Sheets("Data").Name to test if a sheet name is valid.
Use error handling in your own macros. If you write VBA, add On Error Resume Next or On Error GoTo to handle unexpected situations gracefully.
Keep your VBA code simple. Break complex macros into smaller procedures. This makes it easier to isolate errors.
Search for the exact error message online. For example, “Runtime error 9 Excel” will show many forum threads with specific solutions for that error code.

Troubleshooting

If the error still appears after trying the steps:

Try opening the file in Excel Safe Mode. Hold Ctrl while clicking the Excel icon. If it opens without the error, the problem is likely an add-in or a startup file. Disable add-ins as described in Step 6.
Check if the error is specific to one file. If other workbooks work fine, the issue is in that file. Try copying the data to a new workbook (but be careful: if the file has macros, you’ll need to copy the modules too).
Use the “Repair” feature on the workbook. In Excel, go to File > Open, select the file, click the arrow next to “Open”, and choose “Open and Repair”. This can fix corrupted parts of the file.
If the error occurs when running a macro, step through the code. In the VBE, place your cursor inside the macro and press F8 to execute it line by line. This shows you exactly where it fails and lets you inspect variable values.
Consider that the error might be from a third-party add-in. If you recently installed an add-in, uninstall it and see if the error goes away.

Frequently Asked Questions

Q: What does “Runtime error 9: Subscript out of range” mean?
A: It means the code tried to access an element (like a worksheet, workbook, or array index) that doesn’t exist. For example, referencing Sheets("Data") when no sheet is named “Data”.

Q: Can I fix a runtime error without knowing VBA?
A: Yes, in many cases. If the error is due to a missing sheet or named range, you can simply rename the sheet or create the missing object. If it’s more complex, you might need to ask someone with VBA knowledge or search for the specific error message.

Q: Why does the runtime error only happen on some computers?
A: This is often due to different Excel versions, different regional settings (e.g., date formats), or different add-ins installed. The code might rely on a feature that’s not available or behaves differently on another machine.

Q: Will disabling macros fix the runtime error?
A: It might, if the error is caused by a macro that runs automatically (like in the Workbook_Open event). But if you need the macros to work, you’ll have to fix the code instead of disabling them.

Q: How do I find which macro is causing the error?
A: If the error appears when you open the file, check the “ThisWorkbook” code module for a Workbook_Open procedure. If it appears when you click a button, right-click the button and select “Assign Macro” to see which macro is linked. Then open that macro in the VBE.

Q: Is there a way to prevent runtime errors in the future?
A: Yes. Always test macros on a copy of your data, use error handling (like On Error Resume Next) in your code, and keep your Excel and Office updated. Also, avoid hard-coding sheet names if you can use the sheet’s code name (e.g., Sheet1 instead of Sheets("Sheet1")).

General steps for current mainstream versions; menus can differ by device and version. See the disclaimer or report an error.

More in this bed

The whole Tech How-To bed →