[2024] How to Fix Run Time Error 1004 in Excel

Don’t worry if you come across the run time error 1004 in Excel while working with VBA. Here are 8 quick fixes to get rid of it. To deal with corrupted Excel file, just use 4DDiG File Repair.

5 mins read

Updated on 2024-02-25 13:07:11 to File Tips

“Getting Excel Run time error 1004”

“Hi, I keep receiving an error when I try to run a macros on a spreadsheet. The error message says Run-time error ‘1004’: Application-defined or object-defined error. How can I correct the macros so I am able to run it?”

—A user from Reddit
excel run time error 1004

Users working with macros and VBA often encounter the Excel run time error 1004. It is usually accompanied by different types of messages, including Application-defined or object-defined error, Method 'Range' of object '_Worksheet' failed, or copy method of worksheet class failed, etc. No matter what types of 1004 Runtime Error you are facing with, we will help you figure out the reasons behind them. Moreover, we will show you how to fix run-time error 1004 in simple steps. So, let’s get started.

Part 1. What Does Run Time Error 1004 Mean in Excel

It is important to understand what is run time error 1004 and what causes the error before going into its solutions.

Excel run time error 1004 is a generic error that occurs when a macro encounters an issue while executing its VBA codes. The error appears with a message stating the possible cause behind the error. Common causes of Runtime Error 1004 include:

  • Cell or range references do not exist in the worksheet.
  • VBA code has unqualified object references.
  • Third-party add-ins and Excel are in conflict.
  • Worksheet is in protected mode.
  • Excel is outdated or malfunctioning.
  • Excel file is damaged or corrupted.

Part 2. How to Fix Run-time Error 1004 in VBA

Depending on the specific message you get, the solutions for run-time error 1004 can vary. Here are all the possible solutions on how to stop run time error 1004 message showing up.

Fix 1. Check Cell References and Ranges

When the VBA code in a macro refers to a cell or a range that does not exist in the active worksheet, Excel throws the run-time error 1004. For example, macro refers to a range named “bars” present in Worksheet 2, but the active sheet is Worksheet 1. It is also possible that the cell or range references have invalid names.

check cell references and ranges

You should first cross-check all the cell and range references used in the macro to ensure that the referred cells and ranges have valid names. After that, check the active worksheet to ensure that the referred cells and ranges are present in the worksheet for the macro to use them without any hindrance.

Fix 2. Qualify Object References

If the VBA code of a macro uses unqualified object references, Excel may flag run-time error 1004. A qualified object reference is the one where you mention the complete hierarchy of the object in the VBA code. This means the parent object should come first, followed by the sub-objects and, finally, the object you are referring to.

For example, a fully qualified object reference is “Application.Workbooks("WorkBook1.xlsx").Worksheets("WorkSheet1").Range("A1")” and an unqualified object reference could be “Worksheets("WorkSheet1").Range("A1")”. An unqualified object reference can lead to confusion and conflict in accessing the referred object, especially when you are working with multiple workbooks and worksheets with the same names.

fully qualified object reference to fix excel run time error 1004

Therefore, cross-check the VBA code of the macros whose execution is flagging an error. Find the unqualified object references and convert them to fully qualified object references like the one we have specified. Run the macro again to fix the issue if it is due to unqualified object references.

Fix 3. Delete GWXL97.XLA to Fix Run Time Error 1004

We have already stated that a conflict between Excel and a third-party add-in installed can lead to run time error 1004. The conflict can be due to incompatibility of versions or overlapping of functionalities. One of the common third-party add-ins that causes error 1004 in Excel is GWXL97.XLA. You must delete the add-in file and check if the error is fixed. Here are the steps to follow.

  • Right-click on the Start button and open File Explorer.
  • In the “Quick Access” section, navigate to C:\Program Files\MS Office\Office\XLSTART.
  • Look for the GWXL97.XLA file. Right-click on it and select the Delete option.

    delete GWXL97 XLA file to fix run time error 1004
  • Restart your computer and open Excel. Run the macro that was showing the error and see if it is solved.

You can restore the deleted file from Recycle Bin if the issue is not solved.

Fix 4. Create Excel Template to Fix Runtime Error 1004

Sometimes due to a corrupted or malfunctioning Excel workbook, you may encounter Microsoft Visual Basic run-time error 1004, it might. To fix this, create a new Excel workbook using the same template as that in the problematic Excel workbook. Then, replace the corrupted workbook with the new one and link it to the macro.

Here are the steps to create a new Excel template.

  • Open Microsoft Excel and go to File > New. Create a Blank Workbook.
  • Delete all the default worksheets inside the blank workbook except one worksheet.

    open new workboook
  • Format the worksheet as per the previous erroneous worksheet. Copy the data from the previous worksheet and paste into the new worksheet so that the macro can find the referred cells, ranges, and objects.
  • Go to File > Save As and select Save as type as Excel Template option.

    save excel file as excel template
  • Once the file is saved, you have to direct the macro to the new Excel file. Use “Add Type:=path\filename” code to link the macro with the new template. Replace the path and the filename with those of the new workbook.

Fix 5. Enable Trust Access to the VBA Project Object Model

Another workaround to Excel run time error 1004 is to enable the Trust access to the VBA project object model. Here’s what you can do:

  • Open the Excel application. Click on “File” in the upper left corner and select "Options."
  • Select "Trust Center" from the left sidebar. Then open Trust Center Settings:
  • Click Macro Settings and check the option of “Trust Access to the VBA project object model.” Click OK to apply changes.

    enable trust access to the vba project object model to fix run time error 1004

Fix 6. Unprotect Worksheets to Fix Run Time Error 1004

When you put the worksheet that is linked with a macro in protected mode, Excel can flag run-time error 1004 when the macro tries to modify data and add new data to the sheet. In this case, you would need to unprotect the worksheets before running the macro.

Here are the steps to unprotect an Excel workbook.

  • Locate the protected worksheet from the sheet tab.
  • Right-click on the worksheet and click the Unprotect Sheet option from the menu.

    unprotect worksheet to fix run time error 1004
  • If the worksheet is password-protected, Excel will ask you to enter the password for confirmation.
  • Run the macro to see if the error is gone. If needed, re-protect the worksheet after the macro has completed its tasks.

Fix 7. Update, Reinstall or Repair Excel

If you have downloaded VBA codes for building a macro but encounter the VBA runtime error 1004, it might be due to compatibility issues with your Excel version. To fix this, update your Microsoft Office to the latest version for optimal compatibility and to avoid runtime errors. If after the update, run time error 1004 still pops up, consider reinstalling or repairing Excel.

  • Open the Control Panel app from the Start menu.
  • Double-click on the Programs and Features option.
  • From the list of installed apps, locate Microsoft Office and right-click on it.
  • You can choose the Uninstall option to reinstall the current or the latest version.

    uninstall excel to fix VBA runtime error 1004

    If you're going to repair the current version, choose the Change option. Follow the on-screen instructions.

    repair excel to fix runtime error 1004
  • Once you have reinstalled or repaired Excel, you can run the macro on the existing Excel file and see if the error occurs again.

Part 3. The Ultimate Way to Fix Run Time Error 1004

When you have tried and tested all the methods to fix the runtime error 1004, but none of them worked, the issue must be with the Excel file. When the Excel file linked with the macro is corrupted or damaged, Excel will flag a runtime error as it cannot access the required cells or range. To address this, try repairing the corrupted Excel files with a professional file repair tool like 4DDiG File Repair.

It’s a tool designed to handle all kinds of office file corruption issue. Regardless of the extension of the damage, it utilizes both quick and advanced repair to help you restore the file to its normal state.

Here are the features of 4DDiG File Repair that make it the best file repairing tool in the market.

  • It comes with an intuitive interface and can repair corrupted Excel files with a few clicks.
  • It supports various storage devices from where you can import corrupted Excel files.
  • It supports all the different Excel file formats like XLSX and XLSM.
  • It has the fastest and the highest file repair rate.
  • It ensures secure operations and protects your files.

Here are the steps on how to fix run time error 1004 via 4DDiG File Repair.

  • Download and install 4DDiG File Repair on your computer. Launch the tool and click on the File Repair option from the left panel.

    FREE DOWNLOAD

    Secure Download

    FREE DOWNLOAD

    Secure Download

  • Click Add File(s) and import the erroneous Excel from your storage device. You can also drag and drop the file.

    fix run time error 1004 via 4ddig file repair-step 1
  • Click the Start Repair button to initiate the repairing process. 4DDiG File Repair will scan and analyze the Excel file, and then repair it accordingly.

    fix run time error 1004 via 4ddig file repair-step 2
  • Once the repair process gets over, you can have a preview of the file. Select the repaired Excel file and click the Export Selected button to save the repaired file on your storage device.

    fix run time error 1004 via 4ddig file repair-step 3

After that, you can run the VBA code for the macro on the repaired file, and this must have fixed the error now.

Final Words

When dealing with VBA codes and macros in Excel, you will likely come across the infamous run time error 1004. This error 1004 can appear with different messages based on the reason that triggers the error. We have stated all the possible solutions on how to solve 1004 error in VBA. If the Excel file is corrupted or damaged, runtime error 1004 is inevitable. We recommend the 4DDiG File Repair tool to recover any corrupted or damaged Excel files instantly with a few clicks.

William Bollson (senior editor)

William Bollson, the editor-in-chief of 4DDiG, devotes to providing the best solutions for Windows and Mac related issues, including data recovery, repair, error fixes.

(Click to rate this post)

You rated 4.5 ( participated)