Excel VBA Runtime Error 1004

When running VBA code, it’s common to encounter runtime error 1004, called “error 1004.”

Some error messages displayed by VBA to define the error are self-explanatory, but others are cryptic, making it difficult to tell the problem causing the error.

In this tutorial, we will discuss eight common reasons you might encounter the VBA runtime error 1004 and provide some solutions to resolve it.

What is the VBA Runtime Error 1004?

Runtime errors are those errors that occur when you run the VBA code.

Usually, a runtime error makes the VBA code stop, and the user is shown a dialog box. This box reveals the error number along with a description of the error.

VBA Runtime Error 1004 is a common runtime error that occurs when working with Microsoft Excel macros and, more broadly, with VBA in the Microsoft Office suite. This error is usually associated with the way the program interacts with objects, data, or properties within Excel.

The exact text of the error message can vary based on the specific issue encountered, but it typically reads something like:

"Run-time error '1004': Application-defined or object-defined error"

Here’s what this error generally means:

  • Application-defined: This means the error was triggered by the Microsoft Excel application itself. This can be due to issues like incompatible Excel versions, file corruption, or resource constraints.
  • Object-defined: This means the error was triggered by a specific object within the Excel application, usually due to a misreference or misuse in the VBA code.

There can be several causes for this error, such as:

  • Incorrect Referencing: This occurs when a specific worksheet, cell, or range that the VBA code is trying to access does not exist or is not correctly referenced.
  • Workbook or Worksheet Issues: Trying to manipulate a workbook or worksheet that’s closed, not yet opened, or does not exist.
  • Protection: Attempting to modify a protected worksheet or a locked cell.
  • Method or Property Errors: Using a method or property that isn’t allowed or doesn’t apply to the object being referenced.
  • Copy-Paste Issues: Trying to use copy-and-paste methods in VBA, especially if ranges aren’t defined or are overlapping.
  • Data Validation: Trying to set or modify data that does not fit the validation rules set for a particular cell or range.
  • External References: Problems related to linking or referencing external sources, especially if they’re inaccessible.

To resolve the error, it’s essential to identify the specific line causing the issue and understand the context in which the error arises. Debugging tools within the VBA editor, such as breakpoints and the Immediate Window, can be handy in narrowing down and addressing the cause of the error.

Now let’s looks at some example where VBA throws the Runtime Error 1004, and how to to fix it.

Reason #1: VBA Code Refers to Range that Doesn’t Exist

 We want to use an Excel VBA code to add the number of sales reps in cell B5.

Dataset for VBA

If we run the below code, we will get the error message “Run-time error ‘1004’: Application-defined or object-defined error.”

Sub AddNumbers()
    Dim a As Integer, b As Integer, c As Integer
    a = Cells(2, 2).Value
    b = Cells(3, 2).Value
    c = Cells(4, 2).Value
    Cells(0, 2).Value = WorksheetFunction.Sum(a, b, c)
End Sub

The runtime 1004 error in this example occurs because the sixth line of the code mistakenly refers to a non-existent cell, Cells (0,2). In Excel, row numbers start from 1, so Cells(0, 2) is not a valid reference.

Line causing the VBA Runtime Error 1004

How to Fix?

When working with ranges, ensure the specified range is valid and exists in the worksheet. Invalid references will result in a 1004 error.

Double-check cell references in the code before executing it. In this example, the correct cell reference in the sixth line of the code should be Cells (5,2).

Also read: VBA Type Mismatch Error (Error 13)

Reason #2: VBA Code Refers to a Misspelled or Non-existent Named Range 

Suppose you have a worksheet containing a range called “Expenses.”

If you execute the following code, you will get the error message “Run-time error ‘1004’: Expecting Object to be Local”

Sub SelectNamedRange()
Sheets("Sheet1").Activate
Range("Expnses").Select
End Sub

The error 1004 occurs because the name of the named range is misspelled in the third line of the code.

VBA Runtime Error 1004 cause of misspelled named range

Note: This error can also happen if the code refers to a named range that doesn’t exist in the target worksheet. 

How to Fix?

Double-check the references to named ranges in the code before executing it. In this example, we must correct the misspelled name.

Reason #3: VBA Code Attempts to Rename a Worksheet to Name Already Taken

Imagine a workbook with two worksheets, “Sheet1” and “Sheet2.”

The code below will result in the error message “Run-time error ‘1004’: That name is already taken. Try a different one” because it tries to rename “Sheet1” to “Sheet2”, which is already in use.

Sub RenameSheet()
Worksheets("Sheet1").Name = "Sheet2"
End Sub

How to Fix?

The error message is self-explanatory. Rename the worksheet to a name that is not already in use.

Reason #4: VBA Code Attempts to Select Range on a Worksheet that is Not Active

Imagine you have a workbook containing two worksheets: “Sheet1” and “Sheet2”. Currently, “Sheet2” is the active worksheet.

The code below will result in the error message “Run-time error ‘1004’: Select method of Range class failed” because it tries to select a range on “Sheet1” that is not the active sheet when running the code.

Sub SelectRange()
    Worksheets("Sheet1").Range("A1:B5").Select
End Sub

How to Fix?

You can first select the target worksheet in the Excel window to activate it before executing the code.

Or, better still, insert a line in the code that activates the target worksheet. In our example, we have inserted a line that activates “Sheet1” before range selection:

Sub SelectRange()
    Sheets("Sheet1").Activate
    Range("A1:B5").Select
End Sub

Reason #5: The VBA Code Attempts to Open a File that is Moved, Renamed, or Deleted

We had a workbook called “Example” in the “Excel Tutorials” folder on the C drive, but it has been moved, deleted, or renamed.

If we run the following code to open the file:

Sub OpenWorkbook()
    Workbooks.Open Filename:="C:\Excel Tutorials\Example.xlsx"
End Sub

The code executes and displays the error message “Sorry, we couldn’t find C: \Excel Tutorials\Example. xlsx. Is it possible it was moved, renamed, or deleted?”

VBA Runtime Error 1004 file moved

How to Fix?

The error message is self-explanatory. Double-check that the file referred to in the code exists in the target folder and is not renamed.

Reason #6: Syntax Error in VBA Code

The example code below will result in error 1004, and VBA will display the error message “Run-time error ‘1004’: Method ‘Range’ of object ‘_Global’ failed.”

Sub Range_Error()
Range(1, 1).Select
End Sub

The example code results in error 1004 because it has a syntax error. The ‘Range’ object expects cell references to be specified as strings, not numeric values. 

How to Fix?

To fix the error, you should feed the ‘Range’ object with cell references in the form of strings enclosed in double quotes. Here’s the corrected code:

Sub Range_Error()
    Range("A1").Select
End Sub

Reason #7: VBA Code Attempts to Incorrectly Open File That is Not an Excel File

The following example code results in error 1004, and VBA displays the error message “Run-time error ‘1004’: Excel VBA Hyperlinks.docx: file format is not valid.”

Sub OpenWordFile()
    Workbooks.Open Filename:="C:\Excel Tutorials\Excel VBA Hyperlinks.docx"
End Sub

The error 1004 happens because the code uses the “Open” method of the “Workbooks” object to try and open a Word file.

You can only use the “Open” method of the “Workbooks” object to open Excel files. 

How to Fix?

To open a Word file in Excel, use the code below, which you can adjust to your needs.

Sub OpenWordFile()

    Dim objWord As Object

     'Create a new instance of Microsoft Word

    Set objWord = CreateObject("Word.Application")

    objWord.Visible = True

     'Open the Word file

    objWord.documents.Open ("C:\Excel Tutorials\Excel VBA Hyperlinks.docx") 'Change the path and file name to your specifications

End Sub

Reason #8: VBA Code Attempts to Activate Range on a Worksheet that is Not Active

Suppose you have a workbook with two worksheets: “Sheet1” and “Sheet2”. Currently, “Sheet2” is active.

The code below will result in the error message “Run-time error ‘1004’: Activate method of Range class failed” because it tries to activate a range on “Sheet1” that is inactive.

Sub ActivateRange()
  Worksheets("Sheet1").Range("A1:B5").Activate
End Sub
Fix

You can first select the target worksheet in the Excel window to activate it before executing the code.

Or, better still, insert a line in the code that activates the target worksheet. In our example, we have inserted a line that activates “Sheet1” before range activation:

Sub SelectRange()

    Sheets("Sheet1").Activate

    Range("A1:B5").Activate

End Sub
Also read: Subscript Out of Range Error in VBA

Dealing with Error 1004 Through Error Handling

Error handling allows you to gracefully handle errors that may occur during the execution of your code. 

To use error handling to handle a runtime error 1004 in VBA, you can use the ‘On Error’ statement.

Here’s an example of how to use error handling to handle error 1004:

Sub ErrorHandlingExample()

    On Error Resume Next ' Turn on error handling

     ' Your code here that may cause error 1004

     ' For example, trying to open a file that has been moved, renamed, or deleted

    Workbooks.Open Filename:="C:\Downloads\Example.xlsx"

     ' Check for errors

    If Err.Number = 1004 Then

       MsgBox "Runtime Error 1004: Workbook not found."
         ' Exit the sub:
    
    Exit Sub

    End If

    On Error GoTo 0 ' Turn off error handling (optional)

End Sub

Here’s how the code works:

  1. ‘On Error Resume Next’: This statement turns on error handling, allowing VBA to continue executing code even if an error occurs.
  2. The code that might cause the 1004 error is placed inside the error handling block.
  3. After attempting the problematic operation, you check if ‘Err.Number’ equals 1004, indicating runtime error 1004.
  4. If the error is 1004, you can display a user-friendly message using the ‘MsgBox’ function and exit the sub-routine.
  5. ‘On Error GoTo 0’ is used to turn off error handling. This statement is optional but recommended to avoid unexpected error handling in subsequent code.

You can expand on this to handle other specific error numbers or to take different actions based on the nature of the error.

Remember, robust error handling involves not only notifying the user or the developer of an error but also, when possible, providing ways to recover from errors or ensuring that the application can continue running safely.

In this tutorial, we discussed eight common reasons for encountering runtime error 1004 in your code and provided solutions. We hope you found the tutorial helpful.

Other Excel VBA articles you may also like:

I am a huge fan of Microsoft Excel and love sharing my knowledge through articles and tutorials. I work as a business analyst and use Microsoft Excel extensively in my daily tasks. My aim is to help you unleash the full potential of Excel and become a data-slaying wizard yourself.

Leave a Comment