What is 1004 error? 

VBA 1004 Error, also known as object-defined or application-defined, is a runtime error in VBA, usually, if the specified range does not exist in the worksheet or if the Application or the object if not appropriately defined. Error 1004 can cause a severe problem and even can crash your VBA macro code, or in some cases, it freezes the system for a while. This error is encountered by all Ms Excel versions, unlike Excel 2007, 2010, 2013, 2016, and even 2019.

VBA 1004 Error is an error we encounter even if there is some corruption in the Mas Excel desktop icon or any conflict in the file concerning other programs while opening the VBA Excel file. Sometimes when you copy the filtered data and paste it to another worksheet of Ms Office Excel, the 1004 Error is thrown.

Why Error 1004 occurs?

Errors are part and parcel of any programming language. They are usually unintentional but creates loads of troubles. Runtime errors can occur anywhere in your code, even if you are a pro coder. In VBA, the Runtime Error 1004 encounters due to several reasons. Few of the common reason for the occurrence of Runtime Error 1004 are explained below:

  1. Run-time error ‘1004’: Method ‘Range’ of object ‘_ Global’ failed
VBA runtime error 1004

In Excel, we have a fixed number of rows and columns. This error occurs if the user refers to an invalid range that is out of the Excel’s list. Because it revolves around range or objects, so it displays the error message that the “Range” of object’ _ Global’ method has failed.

Example: Demonstrating the VBA code where the range variable does not exist in our worksheet.

Code:             

VBA runtime error 1004

Press the F5 key to run the code. When you run the above code, you will get the following output.

Output

VBA runtime error 1004
  • Run-time error ‘1004’: That Name is already taken. Try a different One.
VBA runtime error 1004

Many times in confusion, we use the same name, which is already given to another Excel sheet in the same workbook. When the compiler reads the code and tries to refer to the sheet, but the same name creates ambiguity. Thus, it throws a runtime Error 1004, stating that the name is already taken, try with a different name.

For example, in the below code in our Excel workbook, we have three worksheets named as ‘Sheet1’, ‘Sheet2’, and ‘Sheet3’. Now, if we try to rename our worksheet ‘Sheet1’ with ‘Sheet2’ and run the code, the VBA compiler will throw a run time error.

Example: Demonstrating the VBA code where we assign an existing name to our worksheet.

Code:

VBA runtime error 1004

Press the F5 key to run the code. When you run the above code, you will get the following output.

Output

VBA runtime error 1004
  • Run-time error ‘1004’: Select method of Range class failed
VBA runtime error 1004

Sometimes this error occurs if we specify the range in some another worksheet. But we have not activated the same worksheet whose range we have selected. This error would directly mention that VBA could not access the chosen range property of Range class.

Example: Demonstrating the VBA code where the range variable does not exist in our worksheet.

Code:

VBA runtime error 1004

Press the F5 key to run the code. When you will run the above code, you will get the following output.

Output

VBA runtime error 1004
  • Run-time error ‘1004’- Method ‘Open’ of object ‘Workbooks’ failed or Microsoft Excel cannot access the file.
VBA runtime error 1004

VBA code sometimes throws the Error ‘1004’ if we try to open a workbook that is already open, or another program is already using the same file which the user is referring to. 

Example: Demonstrate the VBA code where the specified file is already opened or has the same name as the other workbook which has already been opened. 

Code:

VBA runtime error 1004

Press the F5 key to run the code. When you run the above code, you will get the following output.

Output

VBA runtime error 1004
  • Run-time error ‘1004’: Sorry, we couldn’t find file_name. Is it possible it was moved, renamed, or deleted?
VBA runtime error 1004

When the user tries to open an Excel worksheet or refer to a directory that does not exist because the file has been moved, deleted, or renamed from the specified path, in that case, VBA throws an Error 1004, stating that the compiler could not find the specified file.   Sometimes, this error could also be displayed if you have misspelled the path or have typed an incorrect file name.

For example: In the below code, we have specified the file address in the path variable. But we know that the defined file path does not exist as there is some typing error.

Example: Demonstrating the VBA code where the specified path does not exist in our worksheet.

Code:

VBA runtime error 1004

Press the F5 key to run the code. When you run the above code, you will get the following output.

Output

VBA runtime error 1004
  • Run-Time error ‘1004’: Activate method of Range class failed.

This error occurs when we activate the range cells without activating the Excel sheet. Thus, the activate method for range class fails as the compiler can not look for the worksheet.

For example, In the below code, we have activated the Range method. But it will throw an error as we have not specified the worksheet.

Example: Demonstrating the VBA code where the worksheet is not activated though we have accessed the Range activate method.

Code:  

VBA runtime error 1004

Press the F5 key to run the code. When you run the above code, you will get the following output.

Output

VBA runtime error 1004

How to Fix the Error 1004?

  1. The range object should be valid. The row and column counting should range from 1 to 104856, and columns range from A to XFD.  
  2. Always have a note regarding your exiting Sheet name, and whenever you want to rename a worksheet, make sure it does not get repeated.
  3. Never type the path or file address directly. Always try to copy it and paste the source file.
  4. Before activating the range class, always measure that you have activated the Active worksheet prior.
  5. Sometimes there are some issues with you with the .xlsx file, or because of corrupted file also this error is thrown. In those cases, the professional recommendation solution is Ms Excel Repair Tool, which is extremely helpful to repair .xls and .xlsx file. It is a unique repairing tool which helps to fix multiple corrupted files. Ms Excel Repair Tool is capable enough to fix and repair charts, cell comments, workbook, worksheet features at once. One of the reasons for recommending this tool is its ease and supportive property, which can run both on Mac as well as Windows operation System.

Pin It on Pinterest

Share This