Excel VBA Error 1004 Application-Defined or Object-Defined Error: Ranges and Sheets

Fix it now

1004 is Excel refusing the call, not VBA failing. Microsoft describes the message as what VBA shows when an error raised by a host application has no VBA-defined number – which is why one number covers a dozen faults. Read the failing line, then find out what it was pointing at.

1. Click Debug. The highlighted line is the one Excel refused; read it before changing anything.
2. Open the Immediate window with Ctrl+G and ask what the macro was actually working on: ?ActiveWorkbook.Name, then ?ActiveSheet.Name.
3. Confirm the sheet exists, spelled exactly: ?ThisWorkbook.Worksheets(“Data”).Name. Error 9, subscript out of range, here means the name is wrong.
4. If the line builds an address from variables, print the address before the failing line. A column index has to be between 1 and 16384, or A to XFD, and a row index between 1 and 1048576 – Microsoft names those bounds as the cause of this error in automation.
5. Qualify the line: reach the range through an explicit worksheet object rather than a bare Range or Cells.
6. If the line writes, inserts, deletes or formats, check protection with ?ActiveSheet.ProtectContents. True is your answer.
7. Set Error Trapping to Break on All Errors in the editor’s options, so nothing further up swallows the failure.

Read Err.Source as well as Err.Number. Microsoft points at it directly: it carries the programmatic id of the application or object that raised the error, which tells you which side refused the call.

If the macro runs from a cold start with a different workbook active, you are done. The next section explains why one number covers so much and how to narrow it fast.

Why it happens

VBA and Excel are two pieces of software talking across a boundary. VBA owns a small set of numbered errors: 9 is “Subscript out of range”, 13 is “Type mismatch”, 6 is “Overflow”. Everything else is Excel answering back. Microsoft’s own description of the message is precise: it is displayed when an error raised with the Raise method or the Error statement does not correspond to an error VBA defines – so it may be an error you defined, or one defined by an object, including host applications like Excel. The number tells you the object model rejected the call. It does not tell you why.

The second source of confusion is implicit binding. Range(“A1”) does not mean a fixed cell. It means A1 on whichever worksheet is active at the instant that line runs, in whichever workbook is active, and Sheets(“Data”) means the sheet in the active workbook rather than the one the code lives in. A macro that works with its own workbook in front of you fails the moment it is called from elsewhere.

When the caller is another application driving Excel, the same refusal arrives as an HRESULT. 0x800A03EC is that face of it, and Microsoft’s documented example is worth memorising because it is so easily checked: a column index outside 1 to 16384, or a row index outside 1 to 1048576. If your code assembles addresses from variables, test that first. A separate code, 0x80010001 (RPC_E_CALL_REJECTED), is what Microsoft points at for Excel being mid-edit or blocked by a dialog, so if that is your symptom you are looking at the wrong number.

Dim wb As Workbook
Dim ws As Worksheet

Set wb = ThisWorkbook
Set ws = wb.Worksheets("Data")

ws.Range("A1").Value = 1
ws.Range(ws.Cells(2, 1), ws.Cells(100, 5)).ClearContents

Full reference

Reading the failing line

The bounds worth having in your head

Microsoft publishes those as the fix for this error in automation, and they catch a specific and common bug: a loop counter that starts at zero, or a variable that was never assigned and therefore holds zero. There is no row zero and no column zero, so the first iteration fails and every subsequent theory about the workbook is wasted effort.

Which numbers are published and which are not

• 9, 13 and 6 are VBA’s own, with published messages: Subscript out of range, Type mismatch, Overflow.
• The message “Application-defined or object-defined error” is documented by Microsoft; the number 1004 is not in Microsoft’s list of trappable errors.
• 0x800A03EC is documented in Microsoft’s Excel automation troubleshooting, with the row and column bounds as its cause.
• 0x800AC472 has no Microsoft-published meaning at all. Treat it as Excel refusing the call and diagnose it the same way.
• 0x80010001, RPC_E_CALL_REJECTED, is the one Microsoft names for Excel being mid-edit or blocked by a dialog.

When it only fails in production

Almost always because the environment differs rather than the code. A different workbook is active, a sheet was renamed on their copy, a named range exists only in your file, the file opened read-only from a mail attachment, or protection is applied on the copy in circulation. Reproduce it by opening the macro’s workbook alongside a second, unrelated workbook, making the second one active, and running the macro. Half of these faults appear immediately.

Getting the debugger to tell the truth

• Comment out any On Error Resume Next on the path. It moves the failure to a later line where the state is already wrong.
• Set Error Trapping to Break on All Errors while you diagnose.
• Print the workbook name, the sheet name and the address on the line above the failure, rather than inferring them.
• Run it twice without closing Excel, to catch state the first run left behind.

Every code this article covers

Confirm the fix worked

1. Run the macro from a cold start of Excel with a different workbook active; it should still complete.
2. Step through with F8 once to the end, watching the Immediate window output.
3. Confirm no Select or Activate remains on the code path that failed.
4. Run it twice in succession without closing Excel, to catch state left behind by the first run.
5. Run it against the file the person who reported it was using, not only your own test file.

Questions people ask about this