Excel VBA Runtime Error 1004 - How to Fix! (2025)

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.

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.

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-existentNamed 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").ActivateRange("Expnses").SelectEnd Sub

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

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").SelectEnd 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").SelectEnd 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?”

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).SelectEnd 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").SelectEnd 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 WordSet objWord = CreateObject("Word.Application")objWord.Visible = True 'Open the Word fileobjWord.documents.Open ("C:\Excel Tutorials\Excel VBA Hyperlinks.docx") 'Change the path and file name to your specificationsEnd 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").ActivateEnd SubFix

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").ActivateRange("A1:B5").ActivateEnd 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 deletedWorkbooks.Open Filename:="C:\Downloads\Example.xlsx" ' Check for errors If Err.Number = 1004 ThenMsgBox "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:

• What is VBA in Excel?
• Microsoft Excel Terminology (Glossary)
• VBA Runtime Error 91
• Using Application.EnableEvents in VBA in Excel
• SetFocus in Excel VBA
• Macro vs. VBA – What’s the Difference?
• Count Rows using VBA in Excel
• VBA to Remove Duplicates in Excel
• Create New Workbook Using VBA in Excel
• How to Change Cell Color Using VBA in Excel
• VBA ByRef Argument Type Mismatch