Runtime error 1004 excel application defined or object defined error

In this ArticleVBA Error 1004 – Object does not existVBA Error 1004 – Name Already TakenVBA Error 1004 – Incorrectly Referencing an ObjectVBA Error 1004 – Object Not Found This tutorial will explain the VBA Error 1004- Application-Defined or Object-Defined Error. VBA run-time error 1004 is known as an Application-Defined or Object-Defined error which occurs while…

Return to VBA Code Examples

In this Article

  • VBA Error 1004 – Object does not exist
  • VBA Error 1004 – Name Already Taken
  • VBA Error 1004 – Incorrectly Referencing an Object
  • VBA Error 1004 – Object Not Found

This tutorial will explain the VBA Error 1004- Application-Defined or Object-Defined Error.

VBA run-time error 1004 is known as an Application-Defined or Object-Defined error which occurs while the code is running. Making coding errors (See our Error Handling Guide) is an unavoidable aspect learning VBA but knowing why an error occurs helps you to avoid making errors in future coding.

VBA Error 1004 – Object does not exist

If we are referring to an object in our code such as a Range Name that has not been defined, then this error can occur as the VBA code will be unable to find the name.

Sub CopyRange()
  Dim CopyFrom As Range
  Dim CopyTo As Range
  Set CopyFrom = Sheets(1).Range("CopyFrom")
  Set CopyTo = Sheets(1).Range("CopyTo")
  CopyFrom.Copy
CopyTo.PasteSpecial xlPasteValues
End Sub

The example above will copy the values from the named range “CopyFrom” to the named range “CopyTo” – on condition of course that these are existing named ranges!  If they do not exist, then the Error 1004 will display.

VBA Error1004 1

The simplest way to avoid this error in the example above is to create the range names in the Excel workbook, or refer to the range in the traditional row and column format eg: Range(“A1:A10”).

VBA Error 1004 – Name Already Taken

The error can also occur if you are trying to rename an object to an object that already exists – for example if we are trying to rename Sheet1 but the name you are giving the sheet is already the name of another sheet.

Sub NameWorksheet()
  ActiveSheet.Name = "Sheet2"
End Sub

If we already have a Sheet2, then the error will occur.

VBA Error1004 2

VBA Error 1004 – Incorrectly Referencing an Object

The error can also occur when you have incorrectly referenced an object in your code. For example:

Sub CopyRange()
  Dim CopyFrom As Range
  Dim CopyTo As Range
  Set CopyFrom = Range("A1:A10")
  Set CopyTo = Range("C1:C10")
  Range(CopyFrom).Copy
  Range(CopyTo).PasteSpecial xlPasteValues
End Sub

This will once again give us the Error 10004

VBA Error1004 1

Correct the code, and the error will no longer be shown.

Sub CopyRange()
  Dim CopyFrom As Range
  Dim CopyTo As Range
  Set CopyFrom = Range("A1:A10")
  Set CopyTo = Range("C1:C10")
  CopyFrom.Copy
  CopyTo.PasteSpecial xlPasteValues
End Sub

VBA Error 1004 – Object Not Found

This error can also occur when we are trying to open a workbook and the workbook is not found – the workbook in this instance being the object that is not found.

Sub OpenFile()
 Dim wb As Workbook
 Set wb = Workbooks.Open("C:DataTestFile.xlsx")
End Sub

Although the message will be different in the error box, the error is still 1004.

VBA Error 1004 FileNotFound

VBA Coding Made Easy

Stop searching for VBA code online. Learn more about AutoMacro — A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users!
vba save as

Learn More!

Источник

 Summary:

In this post, I have included the complete information about Excel runtime error 1004. Besides that I have presented some best fixes to resolve runtime error 1004 effortlessly.

To fix Runtime Error 1004 in Excel you can take initiatives like uninstalling Microsoft Work, creating a new Excel template, or deleting The “GWXL97.XLA” File. If you don’t have any idea on how to apply these methods then go through this post.

Here in this article, we are going to discuss different types of VBA runtime error 1004 in Excel along with their fixes.

What Is Runtime Error 1004 In VBA Excel?

Excel error 1004 is one such annoying runtime error that mainly encounters while working with the Excel file. Or while trying to generate a Macro in Excel document and as a result, you are unable to do anything in your workbook.

This error may cause serious trouble while you are working with Visual Basic Applications and can crash your program or your system or in some cases, it freezes for some time. This error is faced by any versions of MS Excel such as Excel 2007/2010/2013/2016/2019 as well.

To recover lost Excel data, we recommend this tool:

This software will prevent Excel workbook data such as BI data, financial reports & other analytical information from corruption and data loss. With this software you can rebuild corrupt Excel files and restore every single visual representation & dataset to its original, intact state in 3 easy steps:

  1. Download Excel File Repair Tool rated Excellent by Softpedia, Softonic & CNET.
  2. Select the corrupt Excel file (XLS, XLSX) & click Repair to initiate the repair process.
  3. Preview the repaired files and click Save File to save the files at desired location.

Error Detail:

Error Code: run-time error 1004

Description: Application or object-defined error

Screenshot Of The Error:

run-time error 1004

Don’t worry you can fix this Microsoft Visual Basic runtime error 1004, just by following the steps mentioned in this post. But before approaching the fixes section catch more information regarding runtime error 1004.

Excel VBA Run Time Error 1004 Along With The Fixes

EXCEL ERRORS

The lists of error messages associated with this Excel error 1004 are:

  1. VB: run-time error ‘1004’: Application-defined or object-defined error
  2. Excel VBA Runtime error 1004 “Select method of Range class failed”
  3. runtime error 1004 method range of object _global failed visual basic
  4. Excel macro “Run-time error ‘1004″
  5. Runtime error 1004 method open of object workbooks failed
  6. Run time error ‘1004’: Method ‘Ranger’ of Object’ Worksheet’ Failed
  7. Save As VBA run time Error 1004: Application defined or object defined error

Let’s discuss each of them one by one…!

#1 – VBA Run Time Error 1004: That Name is already taken. Try a different One

This VBA Run Time Error 1004 in Excel mainly occurs at the time of renaming the sheet.

If a worksheet with the same name already exists but still you are assigning that name to some other worksheet. In that case, VBA will throw the run time error 1004 along with the message: “The Name is Already Taken. Try a different one.”

VBA Run Time Error 1004 in Excel 1

Solution: You can fix this error code by renaming your Excel sheet.

#2 – VBA Run Time Error 1004: Method “Range” of object’ _ Global’ failed

This VBA error code mainly occurs when someone tries to access the object range with wrong spelling or which doesn’t exist in the worksheet.

Suppose, you have named the cells range as “Headings,” but if you incorrectly mention the named range then obviously you will get the Run Time Error 1004: Method “Range” of object’ _ Global’ failed error.

VBA Run Time Error 1004 in Excel 2

Solution: So before running the code properly check the name of the range.

# 3 – VBA Run Time Error 1004: Select Method of Range class failed

This error code occurs when someone tries to choose the cells from a non-active sheet.

 Let’s understand with this an example:

Suppose you have selected cells from A1 to A5 from the Sheet1 worksheet. Whereas, your present active worksheet is Sheet2.

At that time it’s obvious to encounter Run Time Error 1004: Select Method of Range class failed.

VBA Run Time Error 1004 in Excel 3

Solution: To fix this, you need to activate the worksheet before selecting cells of it.

#4 – VBA Runtime Error 1004 method open of object workbooks failed

This specific run time Error 1004 arises when someone tries to open an Excel workbook having the same workbook name that is already open.

In that case, it’s quite common to encounter VBA Runtime Error 1004 method open of object workbooks failed.

VBA Run Time Error 1004 in Excel 4

Solution: Well to fix this, first of all close the already opened documents having a similar name.

#5 – VBA Runtime Error 1004 Method Sorry We Couldn’t Find:

The main reason behind the occurrence of this VBA error in Excel is due to renaming, shifting, or deletion of the mentioned path.

The reason behind this can be the wrong assigned path or file name with extension.

When your assigned code fails to fetch a file within your mentioned folder path. Then you will definitely get the runtime Error 1004 method. Sorry, and We couldn’t find it.

VBA Run Time Error 1004 in Excel 5

Solution: make a proper check across the given path or file name.

#6 – VBA Runtime Error 1004 Activate method range class failed

Behind this error, the reason can be activating the cells range without activating the Excel worksheet.

This specific error is quite very similar to the one which we have already discussed above i.e Run Time Error 1004: Select Method of Range class failed.

VBA Run Time Error 1004 in Excel 6

Solution: To fix this, you need to activate your excel sheet first and then activate the sheet cells. However, it is not possible to activate the cell of a sheet without activating the worksheet.

Why This Visual Basic Runtime Error 1004 Occurs?

Follow the reasons behind getting the run time error 1004:

  1. Due to corruption in the desktop icon for MS Excel.
  2. Conflict with other programs while opening VBA Excel file.
  3. When filtered data is copied and then pasted into MS Office Excel workbook.
  4. Due to application or object-defined error.
  5. A range value is set programmatically with a collection of large strings.

Well, these are common reasons behind getting the VBA runtime error 1004, now know how to fix it. Here we have described both the manual as well as automatic solution to fix the run time error 1004 in Excel 2016 and 2013. In case you are not able to fix the error manually then make use of the automatic MS Excel Repair Tool to fix the error automatically.

Fix Runtime Error 1004

Follow the steps given below to fix Excel run time error 1004 :

1: Uninstall Microsoft Work

2: Create New Excel Template

3: Delete The “GWXL97.XLA” File

Method 1: Uninstall Microsoft Work

1. Go to the Task Manager and stop the entire running programs.

2. Then go to Start menu > and select Control Panel.

run time error 1004 (1)

3. Next, in the Control Panel select Add or Remove Program.

run time error 1004 (2)

4. Here, you will get the list of programs that are currently installed on your PC, and then from the list select Microsoft Work.

run time error 1004

5. And click on uninstall to remove it from the PC.

It is also important to scan your system for viruses or malware, as this corrupts the files and important documents. You can make use of the best antivirus program to remove malware and also get rid of the runtime error 1004.

Method 2: Create New Excel Template

Another very simple method to fix Excel runtime error 1004 is by putting a new Excel worksheet file within a template. Instead of copying or duplicating the existing worksheet.

Here is the complete step on how to perform this task.

1.Start your Excel application.

2. Make a fresh new Excel workbook, after then delete the entire sheets present on it leaving only a single one.

3. Now format the workbook as per your need or like the way you want to design in your default template.

4. Excel 2003 user: Tap to the File>Save As option

SAVE EXCEL FILE

OR Excel 2007 or latest versions: Tap to the Microsoft Office button after then hit the Save As option.

SAVE EXCEL FILE 1

5. Now in the field of File name, assign name for your template.

6. On the side of Save as type there is a small arrow key, make a tap on it. From the opened drop menu

  • Excel 2003 users have to choose the Excel Template (.xlt)

Create New Excel Template 1

  • And Excel 2007 or later version have to choose the Excel Template (.xltx)

Create New Excel Template 2

7. Tap to the Save.

8. After the successful creation of the template, now you can programmatically insert it by making use of the following code:
Add Type:=pathfilename

Remarks: 

From the above code, you have to replace the pathfilename with the complete path including the file name. For assigning the exact location of the sheet template you have just created.

Method 3: Delete The “GWXL97.XLA” File

Follow another manual method to fix Excel Runtime Error 1004:

1. Right-click on the start menu.

2. Then select the Explore option.

Excel Runtime Error 1004

3. Then open the following directory – C:Program FilesMSOfficeOfficeXLSTART

Excel Runtime Error 1004 (1)

4. Here you need to delete “GWXL97.XLA” file

Excel Runtime Error 1004 (2)

5. And open the Excel after closing the explorer

You would find that the program is running fine without a runtime error. But if you are still facing the error then make use of the automatic MS Excel Repair Tool, to fix the error easily.

Automatic Solution: MS Excel Repair Tool

MS Excel Repair Tool is a professional recommended solution to easily repair both .xls and .xlsx file. It supports several files in one repair cycle. It is a unique tool that can repair multiple corrupted Excel files at one time and also recover everything included charts, cell comments, worksheet properties, and other data. This can recover the corrupt Excel file to a new blank file. It is extremely easy to use and supports both Windows as well as Mac operating systems.

* Free version of the product only previews recoverable data.

Steps to Utilize MS Excel Repair Tool:

Conclusion:

Hope this article helps you to repair the runtime error 1004 in Excel and recovers Excel file data. In this article, we have provided a manual as well as automatic solution to get rid of Excel run-time error 1004. You can make use of any solution according to your desire.

Good Luck!!!

Priyanka is an entrepreneur & content marketing expert. She writes tech blogs and has expertise in MS Office, Excel, and other tech subjects. Her distinctive art of presenting tech information in the easy-to-understand language is very impressive. When not writing, she loves unplanned travels.

Источник

Four ways to fix runtime error 1004 in Excel:

Workable Solutions Step-by-step Troubleshooting
Fix 1. Delete the GWXL97.XLA Files Fix the Excel error 1004 is to find and delete the error file. Go to C:Program FilesMS OfficeOfficeXLSTART…Full steps
Fix 2. Check the Trust Access to the VBA Project Object Model Enable a VBA project trust option in Excel Trust Center to fix Excel error 1004. Open a blank Excel file…Full steps
Fix 3. Create Another Excel Template Start a new Excel workbook and make sure there is only one worksheet in it. Format the workbook first…Full steps
Fix 4. Repair Corrupted Excel File Repair corrupted Excel files with a file recovery tool. EaseUS file repair tool fixes severely corrupted XLS and XLSX files and retrieves everything from Excel…Full steps

Microsoft Visual Basic for Applications (VBA) is developed to help users write programs for the Windows operating system. It runs as an internal programming language in Microsoft Office, such as Word, Excel, and PowerPoint.

Some users have reported that when running VBA in an Excel chart or trying to generate a Macro in Excel documents, an error message popped up saying: Runtime error 1004. And then they find themselves cannot access the Excel files. If you have the same encounter as these users, this post is the right place for you. You can find both the reasons and the corresponding solutions of this error code on this page.

How to Fix Excel Error 104

Runtime Error Details

The error message contains more information than the error code 1004. Generally, follow the error code, you can see a brief description. The most repeated error messages are listed below:

  1. Runtime error 1004: Application or object-defined error.
  2. Runtime error 1004: Method Ranger of Object Worksheet failed.
  3. Runtime error 1004: Copy Method of Worksheet Class failed.

The Reason Why You See Runtime Error 1004 in Excel

If you want to know how to fix runtime error 1004 in Excel properly, you need to understand what leads to this issue. The following are the most prominent reasons.

  • Macro Name Error

The Macro you are running is copying the original worksheet to a workbook with a defined name that you did not save and close before running the Macro.

  • File Conflict

When opening the VBA Excel file, it gets conflicted with other programs.

  • Too Many Legend Entries

The Excel chart contains more legend entries than space available to display the legend entries on the chart.

  • Excel File Corruption

Your .xls files got corrupted, infected, or damaged.

Although many reasons would cause this Excel error 1004 problem, luckily, some valid methods can help users re-access the files. Let’s check them one by one.

Fix 1. Delete the GWXL97.XLA Files to Fix Runtime Error 1004 in Excel

The easiest method to fix the Excel error 1004 is to find and delete the error file.

Step 1. Go to C:Program FilesMS OfficeOfficeXLSTART.

Step 2. Find GWXL97.XLA file and delete it.

Step 3. Reopen your Excel file and check if the problem is solved.

Fix 2. Check the Trust Access to the VBA Project Object Model

Another solution you can try is to enable a VBA project trust option in Excel Trust Center. Follow the detailed steps and have a try.

Step 1. Open a blank Excel file and click «Files» on the upper left.

Step 2. Click Option and go to Trust Center.

Enter Excel Option

Step 3. Find and enter the Trust Center Settings.

Enter Trust Center Settings

Step 4. Under Macro Settings, tick the option of «Trust access to the VBA project object model.»

Trust Access to the VBA Project

Now you can check your Excel file.

Fix 3. Create Another Excel Template to Fix Runtime Error 1004 in Excel

This method could be a little bit complicated, but it’s useful and worth trying.

Step 1. Please start a new Excel workbook and make sure there is only one worksheet in it.

Step 2. Format the workbook first and then put the data you need onto it.

Step 3. Tap File > Save As, first enter the file name, and click the unfold arrow in Save as Type column.

Excel Save As

Excel 2003: Choose Excel 97-2003 Template.

Excel 2007 or Later: Choose Excel Template.

Choose the Right Template

Step 4. Click «Save» to confirm.

Now you can insert it programmatically by using the following code: Add Type:=pathfilename. The file name is the one you set when you create the new Excel template.

Fix 4. Repair Corrupted Excel Files Due to Error 1004 

If all the above solutions can’t help you out, then there is one possibility that the Excel file you want to open is damaged. To fix a damaged Excel file, you can rely on file repair software. EaseUS Data Recovery Wizard is a great choice.

With this tool, click the «Repair» button and wait for it to fix all the corrupted documents for you.

  • Repair various corrupted files, including repairing Word, Excel, and PDF document 
  • Fix unreadable contents in Word efficiently
  • Repair corrupted PDF files, extract the text, comments, labels, graphics, etc. 
  • Compatible with Microsoft Office 2019, 2016, 2013, 2010, & previous versions.

Download the software and follow the detailed steps below to fix corrupted Excel files.

Step 1. Launch EaseUS Data Recovery Wizard, and then scan disk with corrupted documents. This software enables you to fix damaged Word, Excel, PPT, and PDF files in same steps. 

select the disk with corrupted documents

Step 2. EaseUS data recovery and repair tool will scan for all lost and corrupted files. You can find the target files by file type or type the file name in the search box. 

find corrupted documents

Step 3. EaseUS Data Recovery Wizard can repair your damaged documents automatically. After file preview, you can click «Recover» to save the repaired Word, Excel, and PDF document files to a safe location.

repair corrrupt documents

The Bottom Line

After reading, you must have a thorough understanding of how to fix Runtime error 1004. If you can make sure that the Excel file you want to open is valid, then the first three methods would help you out.

Once you got a damaged Excel file, a professional file recovery tool is a wiser choice. EaseUS file repair software is highly recommended by many users & IT professionals to help you repair Word, Excel, PowerPoint, and PDF files. 

Источник

VBA 1004 Error is a runtime error in VBA. It is also known as application-defined or object-defined error. Why is that? Because we have a limited number of columns in Excel. When our code gives the command to go out of range, we get a 1004 Error. There are other situations when we get this error when we refer to a range that does not exist in the sheet.

VBA Error 1004 in Excel

VBA 1004 Error is a run time error in VBA and occurs while running the code. Errors are part and parcel of the coding, especially when writing for the first time. Therefore, you may come across many errors in VBA. However, this is common for everybody, and there is no big deal about it.

However, knowing the error of why it is coming makes you avoid those mistakes in the coming future.

In this article, we will discuss one of the important errors in Excel, “VBA 1004 Error”.

Table of contents
  • VBA Error 1004 in Excel
    • Top 6 Excel VBA 1004 Runtime Errors
      • #1 – VBA Run Time Error 1004: That Name is already taken. Try a different One:
      • #2 – VBA Run Time Error 1004: Method “Range” of object’ _ Global’ failed:
      • # 3 – VBA Run Time Error 1004: Select Method of Range class failed:
      • #4 – VBA Runtime Error 1004 method open of object workbooks failed:
      • #5 – VBA Runtime Error 1004 method Sorry We couldn’t Find:
      • #6 – VBA Runtime Error 1004 Activate method range class failed:
    • Recommended Articles

VBA 1004 Error

You are free to use this image on your website, templates, etc., Please provide us with an attribution linkArticle Link to be Hyperlinked
For eg:
Source: VBA 1004 Error (wallstreetmojo.com)

Top 6 Excel VBA 1004 Runtime Errors

You can download this VBA 1004 Error Template here – VBA 1004 Error Template

#1 – VBA Run Time Error 1004: That Name is already taken. Try a different One:

This error occurs while renaming the sheet.

If the worksheet’s name already exists. If you try to assign the same name to another sheet, VBA throws a “Run-time error ‘1004.’ ”

Look at the below code.

Code:

Sub Error1004_Example()

 Worksheets("Sheet2").Name = "Sheet1"

End Sub

VBA 1004 Error Example 1

We are trying to rename sheet 2 as sheet 1. But, we already have a sheet named “Sheet1”.

VBA 1004 Error Example 1-2

If we run this code using the F5 key or manually, we will get “Run-time error ‘1004’ “: “That name is already taken. Try a different one.”

VBA 1004 Error Example 1-1

So, try renaming the sheet accordingly.

#2 – VBA Run Time Error 1004: Method “Range” of object’ _ Global’ failed:

It usually occurs when we try to access the named range in excelName range in Excel is a name given to a range for the future reference. To name a range, first select the range of data and then insert a table to the range, then put a name to the range from the name box on the left-hand side of the window.read more with a spelling mistake or that does not exist you are referring to.

For this, we have named the range of cells “Headings,” as shown in the below image.

VBA 1004 Error Example 2

Now, by using the RANGE object, we can access this range.

Code:

Sub Error1004_Example()

  Range("Headings").Select

End Sub

VBA 1004 Error Example 2-1

If you run this code by pressing the F5 key, then this code will select the named range.

VBA 1004 Error Example 2-2

But, if we mention the named range wrongly, we will get “Run-time error ‘1004.’ “Method “Range” of object’ _ Global” failed.

Code:

Sub Error1004_Example()

  Range("Headngs").Select

End Sub

VBA 1004 Error Example 2-3

Run this code manually or use the F5 key and see the result.

VBA 1004 Error Example 2-4

# 3 – VBA Run Time Error 1004: Select Method of Range class failed:

It usually occurs when we try to select the cells other than the active sheet without making the sheet select or active.

 Look at the below code.

Code:

Sub Error1004_Example()

  Worksheets("Sheet1").Range("A1:A5").Select

End Sub

VBA 1004 Error Example 3

The above code says to select the cells A1 to A5 in the worksheet “Sheet1”. However, to experiment, my present active sheet is “Sheet2”, not “Sheet1”.

We will run this code using the F5 key or manually to see what happens.

VBA 1004 Error Example 3-1

We got “Run-time error ‘1004.’ “Select method of Range class failed” as without activating the sheet; we try to select the cells of that sheet. So first, we need to activate the sheet before we select the cells. Below is the correct code.

#4 – VBA Runtime Error 1004 method open of object workbooks failed:

It usually occurs when you try to open the workbook, which has the same name as the other workbook we have already opened.

Look at the below code.

Code:

Sub Error1004_Example()

   Dim wb As Workbook
    Set wb = Workbooks.Open("FileName.xls", ReadOnly:=True, CorruptLoad:=xlExtractData)

End Sub

Example 3-2

It will throw the below error.

Example 3-3

#5 – VBA Runtime Error 1004 method Sorry We couldn’t Find:

This error occurs when you try to open the file which does not exist in the mentioned path. For example, it could move, be renamed, or deleted from the mentioned path. One reason for this is the wrong type of path or file name with the excel extensionExcel extensions represent the file format. It helps the user to save different types of excel files in various formats. For instance, .xlsx is used for simple data, and XLSM is used to store the VBA code.read more.

Now, take a look at the below code.

Code:

Sub Error1004_Example()

   Workbooks.Open Filename:="E:Excel FilesInfographicsABC.xlsx"

End Sub

Example 4

This code says to open the file “ABC.xlsx” in the mentioned folder path.

We know there is no file in the mentioned folder path. So, we will get the: Run-time error ‘1004’ method when no file exists in the mentioned folder. Sorry, and We couldn’t find it.

Example 4-1

#6 – VBA Runtime Error 1004 Activate method range class failed:

This error occurs mainly due to activating the range of cells without activating the worksheet.

Look at the below code.

Code:

Sub Error1004_Example()

  Worksheets("Sheet1").Range("A1:A5").Activate

End Sub

Example 5

This error is similar to the one we have seen in Run-time error’ 1004′: Select method of Range class failed.

If we run manually or use the F5 key, we will get the below error.

Example 5-1

Because without activating the sheet, we cannot activate the cells in it. So first, activate the sheet and then activate the cells of that sheet.

Recommended Articles

This article has been a guide to VBA 1004 Error. Here, we discuss the top 6 types of 1004 run-time errors in VBA and how to fix them, along with examples and downloadable templates. Below are some useful articles related to VBA: –

  • VBA LEN Function
  • VBA Type Mismatch Error
  • VBA Sub Procedures
  • VBA Match Function
Источник

Contents

  • What Is the Runtime Error 1004 In Excel?
  • Why Am I Getting the Excel Runtime Error 1004?
  • How to Fix the Runtime Error 1004 in Excel on Windows 10
    • The following solutions have been proven to work:
    • Fix 1: Run an Antimalware Scan
    • Fix 2: Allow Access to the VBA Project Object Model
    • Fix 3: Create a New Excel Template
    • Fix 4: Uninstall Microsoft Works
    • Fix 5: Remove GWXL97.XLA
  • Conclusion

What causes the Runtime Error 1004 message on Microsoft Excel?

Are you searching for how to fix ‘Cannot run the macro’ in Excel? Then you’ve come to the right place. The message is one of many that may appear as a result of Runtime Error 1004. Other variations of the Excel Error 1004 include:

  • Application-defined or object-defined error: Runtime Error 1004
  • Method ‘Ranger’ of Object Worksheet Failed
  • Copy method of worksheet class failed
  • Programmatic access to Visual Basic Project is not trusted
  • Excel macro “Run-time error 1004″
  • Method open of object workbooks failed: Runtime Error 1004
  • Select method of Range class failed: Excel VBA Runtime Error 1004
  • Application-defined or object-defined error: Save As VBA Run Time Error 1004
  • Method range of object _global failed visual basic: Runtime Error 1004

What Is the Runtime Error 1004 In Excel?

Microsoft Excel is a useful and highly demanded spreadsheet that makes data computation easy. It is used by both individuals and businesses all over the world and comes in XLS and XLSX format.

However, while working on an Excel file or trying to generate a Macro, many users have been aggravated by the Runtime Error 1004. It prevents you from making any changes in your Excel workbook, makes you unable to use certain resource libraries, and freezes or crashes the program or even your system. It causes annoying performance problems while working with Visual Basic applications.

The Runtime Error 1004 can occur on any version of Excel: from Excel 2003 to Excel 2019. It mostly appears when you try running Microsoft Visual Basic for Applications Macro. The problem is due to a security procedure that is meant to lock Microsoft VBA from Excel. The Runtime Error 1004 likely won’t occur if you grant security access. The error could also be due to program corruption as a result of malware infection.

Why Am I Getting the Excel Runtime Error 1004?

The Runtime Error 1004 can appear as a result of:

  1. Corrupt MS Excel desktop shortcut.
  2. Copying and pasting filtered data into an MS Office Excel workbook.
  3. Program conflict while opening the VBA Excel file.
  4. Programmatically setting a range of values with a collection of large string.
  5. Application or object-defined error.

Whatever the reason may be, the issue is easy to get rid of. Keep reading to learn how to fix the Runtime Error 1004 when you make changes in Excel.

How to Fix the Runtime Error 1004 in Excel on Windows 10

The following solutions have been proven to work:

  1. Run an antimalware scan
  2. Allow Access to the VBA Project Object Model
  3. Create a new Excel template
  4. Uninstall Microsoft Works
  5. Remove GWXL97.XLA

By the time you have tried one or a few of the above fixes, the error will be resolved. We will now provide you with detailed steps to carry out the solutions.

Fix 1: Run an Antimalware Scan

This is the first fix you should try once you get the Runtime Error 1004. If your system is infected with viruses and other types of malware, you will run into unpleasant and unexpected issues. Your system and application will malfunction. Moreover, important files and documents get corrupted.

Run a full system scan with a strong antivirus program. We recommend you use Auslogics Anti-Malware. The tool has precision tools to detect and eliminate even the most elusive malicious elements that your existing antivirus program may miss. Auslogics is a Microsoft partner. They are a certified Microsoft Silver Application developer. Auslogics products have been tested and trusted by experts and millions of users worldwide.

Fix 2: Allow Access to the VBA Project Object Model

The procedure for this solution is easy. Follow the steps below:

  1. Launch Excel and click on the File tab.
  2. Click on Options.
  3. Click on Trust Center. It is the last option in the left pane.
  4. Click the Trust Center Settings button in the right pane.
  5. In the new page that opens, locate Macro Settings in the left pane and click on it.
  6. Mark the checkbox for ‘Trust access to the VBA project object model’ under the Developer Macro Settings section in the right pane.
  7. Click the OK button.

Fix 3: Create a New Excel Template

Rather than copy or duplicate your existing worksheet, you can put your new Excel Worksheet file within a template. This will help fix the Runtime Error 1004. Here’s how to get it done:

  1. Launch Excel.
  2. Press the Ctrl + N combination on your keyboard to open a new workbook. Make sure to leave only one sheet on the workbook. Delete the rest.
  3. Modify the workbook to suit your needs.
  4. Save the workbook by pressing Ctrl + S on your keyboard. Or if you use Excel 2003, click on the File tab and click Save as. For Excel 2007 and newer versions, click the Microsoft Office icon in the top-left corner of your screen and then click on Save as.
  5. Choose a location to save the file on your computer and enter a name for the file.
  6. Click the drop-down arrow beside ‘Save as type’ and select Excel Template (.xlt) if you are using Excel 2003. If you are using Excel 2007 and later versions, select Excel Template (.xltx) instead.
  7. After saving the template, use the following code to insert it:

Add Type:=pathfilename

Make sure to replace “pathfilename” with the actual path and file name of the template.

Fix 4: Uninstall Microsoft Works

Here’s the procedure to follow:

  1. Right-click on the Start button to open the Power User menu. Select Task Manager from the list. Alternatively, press the Ctrl + Alt + Del combination on your keyboard and then click on Task Manager.
  2. Once in Task Manager, click on each of the running apps and click the End Task button.
  3. Close the Task Manager window.
  4. Open the Run utility by pressing the Windows + R keyboard combination. Type “appwiz.cpl” (no quotes) into the text field and click the OK button or press Enter on your keyboard.
  5. Locate Microsoft Works in the list of apps and right-click on it. Then, click on Uninstall from the context menu. Confirm the action if prompted.

Fix 5: Remove GWXL97.XLA

Here’s how to delete the GWXL97.XLA file:

  1. Press the Windows + E combination on your keyboard to open File Explorer.
  2. Navigate this path: C: Users > User > Name > AppData > Local> MicrosoftExcel.
  3. Open the XLStart folder.
  4. Locate and delete the GWXL97.aXLA file.

Conclusion

We hope the solutions provided above have helped you to successfully fix the Runtime Error 1004 in Microsoft Excel and recover the data on your file. There are many issues that can lead to the runtime error. However, by the time you get to the end of this guide, you will be just fine.

Don’t hesitate to leave your questions or comments in the section below. We love hearing from you.

Do you like this post? 🙂

Please rate and share it and subscribe to our newsletter!


5 votes,


average: 5.00 out of
5

loadingLoading…

Источник

To eliminate Runtime errors in Excel, simply create a new template.

by Vladimir Popescu

Being an artist his entire life while also playing handball at a professional level, Vladimir has also developed a passion for all things computer-related. With an innate fascination… read more


Updated on August 10, 2022

Reviewed by
Vlad Turiceanu

Vlad Turiceanu

Passionate about technology, Windows, and everything that has a power button, he spent most of his time developing new skills and learning more about the tech world. Coming… read more

  • Runtime errors are common Windows-specific issues that occur while the program is running.
  • Extensions, plug-ins, and even other applications can cause Runtime errors in a particular program.
  • Excel makes no exception, with many users reporting Excel Runtime error 1004 on Windows.
  • Try your hand at these quick fixes and avoid losing your data over this anomaly.

excel-runtime-error-1004

Excel runtime error 1004 typically comes up when you are working with a corrupt document. You also get this error if you open a VBA Excel file when your Excel is having a conflict with other programs.

The Excel runtime error 1004 could show up due to a variety of other underlying issues. The most common error messages include the following:

  • VB: run-time error 1004: Application-defined or object-defined error
  • Excel VBA Runtime error 1004 Select method of Range class failed
  • runtime error 1004 method range of object _global failed visual basic
  • Excel macro Run-time error 1004
  • Runtime error 1004 method open of object workbooks failed
  • Run time error 1004: Method Ranger of Object Worksheet Failed
  • Save As VBA run time Error 1004: Application-defined or object-defined error

If you get any of these errors, then you can fix the error using the solutions here.

Quick Tip:

Before doing anything else, you can simply fix all the broken files like Excel documents with a PC repairing software like Kernel Repair for Excel.

Excel data is accurately restored, including table, chart, formula, chart sheet, picture, cell comment, sort, filter, fields, forms, and reports.

Kernel Repair Excel

Advanced methods are used to restore and save the original data structure and features.

What is Run Time error 1004 in Excel?

The runtime error is specific to apps that integrate Microsoft Visual Basic for Application (also known as VBA) script to run repetitive tasks.

All Microsoft Office applications use this Windows-specific programming, but the most prevalent issues arise in Excel, especially Excel 2007.

Many users report having issues when creating macros. A macro is a feature in Excel that records keystrokes and clicks in order to help you automate repetitive tasks.

Oftentimes, this task gets in conflict with the VBA which leads to the 1004 error. Another common cause is a corrupt Excel application or a corrupt XLS file.

Other possible causes that have been linked to this error include having a number of legend entries that exceed the space available, and a file conflict between Excel and other apps.

How do I fix runtime error 1004 in Excel?

The good news is that this error has been around for a while and there are several proven methods that will help you fix it.

1. Uninstall Microsoft Works to fix Runtime error 1004

  1. Press the CTRL + ALT + DEL combination and click on Task Manager.
  2. End all the programs that are currently open.
  3. Next, press the Windows key + R to open the Run utility.
  4. Here, enter appwiz.cpl and click the OK button.excel-runtime-error-1004-uninstall-microsoft-works
  5. From the list of programs installed on your computer, find Microsoft Works. Right-click on it and hit Uninstall.

2. Delete GWXL97.XLA to fix Run Time error 1004

  1. Start by opening File Explorer on your computer. You can do this by pressing the Windows key + E.
  2. Next, click on the address bar, type in the following path, and hit Enter: C:Usersuser nameAppDataLocalMicrosoftExcel
    excel-runtime-error-1004-find-the-excel-folder
  3. Here, open the XLStart folder.
  4. Finally, find the GWXL97.XLA file and delete it.

3. How to fix Runtime error 1004 by creating a new template

A great and easy method to fix runtime error 1004 is to use a new Excel template.

Some PC issues are hard to tackle, especially when it comes to corrupted repositories or missing Windows files. If you are having troubles fixing an error, your system may be partially broken.
We recommend installing Restoro, a tool that will scan your machine and identify what the fault is.
Click here to download and start repairing.

You need to transfer the template from the Excel file that had issues to the new one, and the runtime error will be gone.

While we’re at it, you might want to learn more about the Excel application and its best uses.

If so, take a closer look at our Excel page to get your hands on countless informative recommendations and error fixes.

1. Launch Microsoft Excel on your computer.

2. Next, create a new Microsoft Excel workbook by pressing the CTRL + N combination or selecting Blank workbook from the first screen.

excel new workbook

3. After making the workbook delete all the sheets on the workbook except one.

4. Format the remaining sheet.

5. Then go to File > Save As.

6. Save the file with the Excel Template (.xltx or .xlt) file format.

7. When the document is saved successfully, you can insert the template with the help of this code:Add Type:=pathfilename

  • Replace filename with the actual name of the document.
Read more about this topic

  • Microsoft Excel is Waiting for Another Application to Complete OLE Action
  • Fix: Most of The Features Have Been Disabled [Word, Excel]
  • FIX: Excel file could not be accessed
  • FIX: Excel File isn’t Sending & Attaching to Outlook Email

Knowing that there are many causes of the runtime error 1004 in Excel, only one solution may work.

With the help of this guide, you can now fix the Microsoft Excel runtime error 1004 and recover your file data.

In case you need further assistance, we have a dedicated Hub for Troubleshooting Runtime Errors that is full of articles like the one below.

newsletter icon

Newsletter

Источник
  • Remove From My Forums

  • Question

  • I try copy column 14 in x  to the first column in the new added sheet (MySheet) of y

    Sub VBA_Copy_Columns_Workbook()

     
            Dim x As Workbook
        Dim y As Workbook

      
        Set x = Workbooks.Open(«C:TempTest.xlsx»)
        Set y = ThisWorkbook

           
     x.Worksheets(«Excel_Destination»).Activate

    Get error:  Excel VBA run-time error 1004 : Application-defined or object-defined error

    either run Treat Data:

    Range(«N:N»).Select
    With Selection
       Selection.NumberFormat = «0»
       .Value = .Value
    End With

    or

    Copy Data

     Application.Intersect(Range(Cells(2, 14), Cells(ActiveSheet.Rows.Count, 14)), ActiveSheet.UsedRange).Copy

    y.Activate
     Worksheets.Add(After:=Worksheets(1)).Name = «MySheet»
    y.Sheets(«MySheet»).Activate
    Cells(ActiveSheet.Rows.Count, 1).End(xlUp).Offset(1, 0).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False

        »»’Process Completed
        MsgBox «Task Completed»

       
    End Sub

    Thanks!

    • Edited by

      Monday, April 10, 2017 6:16 PM

Answers

  • Excellent! yes, Column(3) is inserted in this procedure:

    Also, I get VERY long sheet: 5 digit number 11789  row. I guess it because :

    u.Range(«C7:C» & u.Cells(u.Rows.Count, «B»).End(xlUp).Row).Formula = «=VLOOKUP(B7,MySheet!A:B,2,FALSE)»

    B column go down forever. Any Active cell for u.Rows.Count I can use?

    That’s okay, Thanks so much!

    • Marked as answer by
      Daphne12
      Monday, April 17, 2017 4:39 PM
    • Edited by
      Daphne12
      Monday, April 17, 2017 4:45 PM

Источник

Microsoft Excel is one of the most popular spreadsheets used across the globe for both individual and business purposes. It is the one-stop destination for storing, organizing, and manipulating data in an organized way. MS Excel comes mainly in two extensions i.e. XLS and XLSX format. However, apart from its incredible popularity, runtime errors are a common nuisance for many Windows users – and one of the most common ones is the Runtime Error 1004.

Runtime Error 1004 in Microsoft Excel

In this guide, we are going to discuss this common Runtime error 1004 and some of the best fixes to resolve it effortlessly.

What is Runtime Error 1004 in Excel?

Runtime error 1004 is an error code relating to Microsoft Visual Basic that has been known to disturb Microsoft Excel users. This error is faced by any versions of MS Excel such as Excel 2007, 2010, 2013, 2016, 2019 as well. No version of Microsoft Excel is safe from the menace of Runtime Error 1004. In some cases, you may see the runtime error 1004 in Excel after opening a worksheet. Such types of runtime errors usually occur due to a problematic add-in.

Runtime Error 1004 in Excel

This error is mainly encountered by users while they are working on an excel file or trying to generate a Macro in the excel document. It can cause serious trouble while working with Visual Basic Applications and can completely crash a program, or even the entire system; sometimes it may freeze the system prohibiting the users to do anything on their system.

Types of error message

The error messages that are most associated with this runtime error are as follows:

  • VB: run-time error ‘1004’: Application-defined or object-defined error
  • Excel VBA Runtime error 1004 “Select method of Range class failed”
  • runtime error 1004 method range of object _global failed visual basic
  • Excel macro “Run-time error ‘1004?
  • Runtime error 1004 method open of object workbooks failed
  • Run-Time error ‘1004’: Method ‘Ranger’ of Object’ Worksheet’ Failed
  • “Method in Key up Object Program APPLICATION Failed.”
  • Run-time error ‘1004’: Activate method of Worksheet class failed

If you encounter any of these above errors, then you can fix the error using our guide.

What are the causes?

The Error 1004 is a general code related to MS Excel but is not specific to one exact cause. Hence, in this case, the exact reason why this error might pop up will vary from case to case and circumstance to circumstance. From configuration issues to software problems, below we listed a synopsis of common reasons for runtime error 1004 in excel:

  • MS Excel Desktop Icon might be corrupted
  • VBA Excel File is clashing with other application
  • Due to application or object specified error
  • Due to missing dependent file
  • Due to Virus, Trojan or malware
  • Due to Invalid Registry Keys and so on.

These were a few of the most common reasons behind getting the runtime error 1004 in MS Excel; now let us understand the different fixes.

Here we have detailed both manual and automatic solutions to fix the Runtime Error 1004. You can follow any one of the next methods to resolve the issue.

  1. Create a new Excel template
  2. Launch Excel in Safe Mode
  3. Run a Virus Scan
  4. For VB: run-time error ‘1004’, resize legend entries

Let’s look at each of these methods in detail.

1] Create a new Excel template

In some cases, fixing this issue can be as simple as inserting a new worksheet from a template instead of creating a copy of an existing worksheet. Here is what you need to do:

1] Open MS Excel on your system

2] Press ‘CTRL + N’ to create a new Microsoft Excel worksheet or simply select a ‘Blank workbook’ from the first screen.

Runtime Error 1004

3] Once done delete all the sheets on the workbook except one.

4] Now, format the workbook, which has been left. Also, note, this workbook can be modified to suit your individual need.

5] In the end, go to ‘File > Save As’ to save the new worksheet with the Excel Template (.xltx or .xlt) file format.

6] Once you have successfully created the template, you can insert it programmatically by using the following line of code:

Sheets.Add Type:=pathfilename

Please note – Do not forget to replace the new filename with the actual name of the document.

2] Launch Excel in Safe Mode

If Excel is showing you the runtime error 1004 after opening an Excel file, there might be a problematic add-in causing the issue. The affected users received the error message given below:

Run-time error ‘1004’: Activate method of Worksheet class failed

To check if the problem is caused by an add-in or not, launch Excel in Safe Mode. If Excel opens successfully in the Safe Mode, the problem was occurring due to an add-in. Now, you have to identify the problematic add-in. To do that, follow the steps provided below:

Disable addins in Excel

  1. In Safe Mode, go to “File > Options > Add-Ins.”
  2. Select COM Add-ins in the drop-down on the right side and click Go.
  3. In Safe Mode, some add-ins are disabled. You have to enable the disabled add-ins one by one. Enable one of the disabled add-ins and restart Excel in normal mode. See if the error occurs.

If the error occurs, repeat the above steps again. After you find the problematic add-in, consider removing it from Excel.

3] Run a Virus Scan

It is very important to scan your computer system for malware and viruses as these can corrupt files and important documents and show the runtime error 1004 in MS Excel. Sometimes using a good antivirus program helps a lot.

Related: Run-time error 1004, Cannot run the Macro in Excel.

4] For VB: run-time error ‘1004’, resize legend entries

If you come across a Runtime Error 1004 when running a Microsoft Visual Basic for Applications (VBA) macro, then you can use this method to work-around.

Generally, you get this error when you try to run a VBA macro that uses the LegendEntries method to make changes to legend entries in a Microsoft Excel chart. That time, you may receive the following error message:

Run-time error ‘1004’: Application or object-defined error

This error occurs when the Excel chart contains more legend entries than there is space available to display the legend entries on the Excel chart. When this behavior occurs, Microsoft Excel may truncate the legend entries.

To work around this behavior, create a macro that reduces the font size of the Excel chart legend text before your VBA macro makes changes to the chart legend and then restore the font size of the chart legend so that it is similar to the following macro example.

Sub ResizeLegendEntries()
With Worksheets("Sheet1").ChartObjects(1).Activate
      ' Store the current font size
      fntSZ = ActiveChart.Legend.Font.Size
'Temporarily change the font size.
      ActiveChart.Legend.Font.Size = 2
'Place your LegendEntries macro code here to make
         'the changes that you want to the chart legend.
' Restore the font size.
      ActiveChart.Legend.Font.Size = fntSZ
   End With
End Sub

We hope this article helps you fix the runtime error 1004 in Microsoft Excel. This guide gives you manual as well as automatic solution to get rid of this error; you can make use of any solution based on your need.

How do I fix a runtime error?

Runtime errors occur at the time of running a program. The runtime errors usually freeze or crash the affected program or software. To fix a runtime error, you can use general fixes, like installing the latest Microsoft Visual C++ Redistributables, running the SFC and DISM scans, troubleshooting in a Clean Boot state, etc.

Read next: Arrows keys not working in Microsoft Excel.

Источник

Понравилась статья? Поделить с друзьями:
  • Runtime error 1 0 cannot import isdone dll
  • Runtime error 002 dosbox
  • Runtime broker как исправить
  • Runtime broker unknown hard error
  • Runtime broker exe системная ошибка