This is one of the most common run-time errors. By the end of this article, you will understand why that is and how to fix it. A run-time error is the type of error that occurs during the execution of the code. VBA does not know about it prior to actually running the code. There are different variations of this error; we will provide an example of each of the types below.

EXAMPLE 1: VBA Runtime Error 1004: Method ‘Range’ of object ‘_ Global’ failed

When a range reference is not correct. It could be incorrect because it’s misspelled. It could also be incorrect because it’s trying to get a range that is at an impossible value, such as row 0 or row -2. In the example below, we’re referring to row zero and we’re using the wrong syntax. VBA recognizes that we’re trying to refer to a range but it sees that the arguments (parameters) written within the range in order to identify it are written incorrectly; there should be no commas when using Range().

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

Instead, we should write the code as follows:

Sub Range_Error()
Range(“A1”).Select
End Sub

We could also run into this problem unintentionally if we are looping a variable, such as X, and it becomes zero.

In the below example, X is equal to 5 and the loop is set to keep reducing X by 1 until it reaches -1 then it would not execute. But an error will occur when X = 0.

I set the code to write ‘Correct’ in each of the cells up until the cell we are not able to insert values into. Our code will insert ‘Correct’ in Range(“A5) to Range(“A1”) then will give the error when we ask it to insert ‘Correct’ into Range(“A0”).

Note that we didn’t define X to be a String, so VBA assumes it’s a variant. This means it can be any type of data, so Excel assumes that it’s the type of data that should be utilized in the context we’re utilizing it in. In this case, X would be considered a string. But when X is zero, Excel doesn’t know that it should be string, since “A0” doesn’t exist as a range. So it treats the zero as a number, which triggers this error, because it’s syntax related, meaning what’s written inside Range() is not written correctly from a syntax point of view.

Sub Range_Error()
X = 5
Do Until X = -1
Range("A" & X) = "Correct"
X = X - 1
Loop
End Sub

To correct the problem, we need to stop our code from running the loop once X reaches 0.

Sub Range_Error()
X = 5
Do Until X = 0
Range("A" & X) = "Correct"
X = X - 1
Loop
End Sub

EXAMPLE 2: VBA Run Time Error 1004: That Name is already taken. Try a different One

This error is shown when we are trying to give the same name to a worksheet which has already been given to another worksheet in the same workbook. Given that we already have a sheet named “Sheet1”, we create a new sheet, we click on it so that it becomes the active sheet, and we try to name it “Sheet1” by running the below code. We get an error immediately once we reach the sheet name changing line.

Sub Error_Name_Taken()
ThisWorkbook.Sheets("Sheet2").Activate
ActiveSheet.Name = "Sheet1"
End Sub

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

This error occurs when we attempt to activate a range through VBA in another worksheet (not the currently active one) without activating that worksheet first.

Sub Error_Select_Failed()
ThisWorkbook.Sheets("Sheet2").Activate
ThisWorkbook.Sheets("Sheet1").Range("A1").Select
End Sub

To correct the issue, we would need to first activate “Sheet1” first before attempting to select a range inside of it. Adding a line to active the worksheet resolves the issue.

Sub Error_Select_Failed()
ThisWorkbook.Sheets("Sheet2").Activate
ThisWorkbook.Sheets("Sheet1").Activate
ThisWorkbook.Sheets("Sheet1").Range("A1").Select
End Sub

EXAMPLE 4: VBA Runtime Error 1004: Method ‘Open’ of object ‘Workbooks’ failed

This error occurs when we use VBA to open a workbook that cannot be opened. The reason that Excel is unable to open the workbook could be because it is already open, or the workbook is being used by another program at that moment. A special case exists when you attempt to open a file in read-only mode while the file is corrupt. This would give an error because Excel can open a corrupt file only if it is allowed to repair it first, which it does by writing to it. So, if you open a corrupt file in read-only mode, you are essentially preventing it from repairing the file and hence it would fail to open.

EXAMPLE 5: VBA Runtime Error 1004: file format is not valid

This error, like the one in example 5, is related to attempting to open a file. In this situation, however, we are trying to open a file that isn’t an Excel file; the file doesn’t have any of the Excel extensions ( .xlsx, .xls, .xlsb, .xlsm, etc.)

Sub error_workbook_open()
Workbooks.Open "C:\Users\mmost\Dropbox\Daniel Troha\Run-time error 1004.docx"
End Sub

The code is attempting to open a word document file with extension .docx. Using ‘Workbooks.Open’ can only be used for files that have Excel extensions. To open documents of different extensions, we will need to take a completely different approach. For example, the below code opens a word document.

Sub error_word_doc_open()
Dim wordapp
Dim strFileName As String
strFileName = "C:\Users\mmost\Dropbox \Else Without If.docx"
Set wordapp = CreateObject("word.Application")
wordapp.Documents.Open strFile
wordapp.Visible = True
End Sub

EXAMPLE 6: VBA Runtime Error 1004: Sorry We Couldn’t Find:

This error occurs whenever we are trying to open a file that does not exist at the specified path. When faced with this error, it’s important to check three aspects of the file path; file location, file name, and file extension. Any of these parameters could be wrong, so check each of them carefully.

Sub error_workbook_open()
Workbooks.Open "C:\Users\mmost\Dropbox\Another Workbook.xls"
End Sub

EXAMPLE 7: VBA Runtime Error 1004: Application-defined or Object-defined error

This error encompasses a wide range of possibilities. The error is triggered due to violating one of the rules that are used to handle the object you’re working with.

For example, in the following code, I try to select a cell that exists on row -1 and column 1. There is no such cell as the minimum row number allowed is 1, thus, VBA determines that I violated this rule and throws this error.

Note that this is different from Example 1 in that the syntax I am using here is perfectly correct, but I am violating particular rules that once violated, trigger an error. The wide range of these rules make this one of the most common errors in VBA. To resolve it, you will want to know more about the object you’re working with (in this case it’s a cell in the worksheet) and understand the rules and limitations that exist on the various values that you can use with the object.

Sub error_object_defined()
ThisWorkbook.Sheets(1).Cells(-1, 1).Select
End Sub

To wrap it up, here’s one example of where a syntax error calling a function causes this error to occur.