Vba ошибка 1004 при открытии файла

This tutorial will explain the VBA Error 1004- Application-Defined or Object-Defined Error.

VBA run-time error 1004 is known as an Application-Defined or Object-Defined error which occurs while the code is running. Making coding errors (See our Error Handling Guide) is an unavoidable aspect learning VBA but knowing why an error occurs helps you to avoid making errors in future coding.

VBA Error 1004 – Object does not exist

If we are referring to an object in our code such as a Range Name that has not been defined, then this error can occur as the VBA code will be unable to find the name.

Sub CopyRange()
  Dim CopyFrom As Range
  Dim CopyTo As Range
  Set CopyFrom = Sheets(1).Range("CopyFrom")
  Set CopyTo = Sheets(1).Range("CopyTo")
  CopyFrom.Copy
CopyTo.PasteSpecial xlPasteValues
End Sub

The example above will copy the values from the named range “CopyFrom” to the named range “CopyTo” – on condition of course that these are existing named ranges!  If they do not exist, then the Error 1004 will display.

The simplest way to avoid this error in the example above is to create the range names in the Excel workbook, or refer to the range in the traditional row and column format eg: Range(“A1:A10”).

VBA Error 1004 – Name Already Taken

The error can also occur if you are trying to rename an object to an object that already exists – for example if we are trying to rename Sheet1 but the name you are giving the sheet is already the name of another sheet.

Sub NameWorksheet()
  ActiveSheet.Name = "Sheet2"
End Sub

If we already have a Sheet2, then the error will occur.

VBA Error 1004 – Incorrectly Referencing an Object

The error can also occur when you have incorrectly referenced an object in your code. For example:

Sub CopyRange()
  Dim CopyFrom As Range
  Dim CopyTo As Range
  Set CopyFrom = Range("A1:A10")
  Set CopyTo = Range("C1:C10")
  Range(CopyFrom).Copy
  Range(CopyTo).PasteSpecial xlPasteValues
End Sub

This will once again give us the Error 10004

Correct the code, and the error will no longer be shown.

Sub CopyRange()
  Dim CopyFrom As Range
  Dim CopyTo As Range
  Set CopyFrom = Range("A1:A10")
  Set CopyTo = Range("C1:C10")
  CopyFrom.Copy
  CopyTo.PasteSpecial xlPasteValues
End Sub

VBA Error 1004 – Object Not Found

This error can also occur when we are trying to open a workbook and the workbook is not found – the workbook in this instance being the object that is not found.

Sub OpenFile()
 Dim wb As Workbook
 Set wb = Workbooks.Open("C:DataTestFile.xlsx")
End Sub

Although the message will be different in the error box, the error is still 1004.

Excel VBA Error 1004

VBA 1004 Error is an error we encounter while we execute a code in VBA it is also known as VBA Runtime error. While we work in VBA or in any other programming language or even in our daily work we encounter different kinds of errors. Sometimes even we miss a single character in the code which causes the whole code not to work or maybe the entire code is wrong.

Errors are definitely a part of the code we write. It may be unintentional but they exist. No matter how pro we are in coding, runtime error can occur anywhere. As explained above VBA 1004 Error is an error which occurs during the runtime of the code in excel. It is also called an application defined or object defined error.

There are different types of reasons we get VBA Runtime Error 1004 in excel, let us learn a few of them.

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

This error occurs when the range value we refer to VBA is incorrect. It is also called as Method “Range” of object’ _ Global’ failed.

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

We give the same name to a worksheet which is already taken by another worksheet.

• VBA Runtime Error 1004: Unable to get the select property of Range class:

This is an error when we select a range in another worksheet without activating the worksheet we are referring to.

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

This error occurs when we try to open a workbook which is already open or the file is used by another program already.

• VBA Runtime Error 1004: Sorry We Couldn’t Find:

We get this error when we try to open a worksheet which doesn’t exist.

As we have learned there can be various reasons we get a runtime error. Runtime error can occur at any line of code. We need to learn how to learn to handle these errors and it is called VBA Error Handling.

Example of VBA Runtime Error 1004 in Excel

Now as I have described different types of error which can occur during runtime of any VBA code now let us learn them how they appear with examples.

You can download this VBA 1004 Error Excel Template here – VBA 1004 Error Excel Template

VBA Runtime Error 1004 – Example #1

• Let us name this table header as DATA.

• Go to the Developer tab click on Visual Basic to Open VB Editor.

• Declare the sub-function to start writing the code.

Code:

Sub Sample()

End Sub

• Call the header we named by the following code written below.

Code:

Sub Sample()

Range("Data").Select

End Sub

• When we run the code we can see in the excel that it has been selected as we have called the header correctly.

• Now we misspell the spelling for the header name.

Code:

Sub Sample()

Range("Dataa").Select

End Sub

• Run the code again to see the result.

We get excel VBA Runtime Error 1004 because we have misspelled the range name.

VBA Runtime Error 1004 – Example #2

We get this error when we try to rename a worksheet with a name which is already taken. For example, I have renamed sheet 1 as “Anand” and I will try to rename sheet 2 as same then see the result.

• Go to the Developer tab click on Visual Basic to Open VB Editor.
• Declare a sub-function to start writing the code.

Code:

Sub Sample1()

End Sub

• Try to rename sheet 2 as Anand by the following code below,

Code:

Sub Sample1()

Worksheets("Sheet2").Name = "Anand"

End Sub

• Run the above code and see the result.

When I try to rename a sheet with the name which is already taken I get an Error.

VBA Runtime Error 1004 – Example #3

I will try to add the value from sheet 2 to a variable in sheet 3. But I will not activate the sheet 2 and see what happens.

• Go to the Developer tab click on Visual Basic to Open VB Editor.
• Declare a sub-function to start writing the code.

Code:

Sub Sample2()

End Sub

• Declare two variables A and B as an integer.

Code:

Sub Sample2()

Dim A As Integer
Dim B As Integer

End Sub

• In Variable B store the value of A in addition to cell A1 of sheet 2.

Code:

Sub Sample2()

Dim A As Integer
Dim B As Integer
B = A + Worksheets("Sheet2").Range("A1").Select

End Sub

• Let us suppose the code works and use msgbox function to display the value of B.

Code:

Sub Sample2()

Dim A As Integer
Dim B As Integer
B = A + Worksheets("Sheet2").Range("A1").Select
MsgBox B

End Sub

• Run the code to see the result obtained.

We get this Error because we have not activated sheet 2 but we are trying to use a value of sheet 2.

VBA Runtime Error 1004 – Example #4

We encounter this runtime error when we have already the same name of workbook open but we try to open it again.

For this example, I have already renamed my workbook as VBA 1004 Error.xlsm and I will try to open it again which is already open and see if I get VBA 1004 Error.

• Go to the Developer tab click on Visual Basic to Open VB Editor.
• Declare a sub-function to start writing the code.

Code:

Sub Sample3()

End Sub

• Declare a variable as the workbook.

Code:

Sub Sample3()

Dim A As Workbook

End Sub

Try to open the workbook we have currently already open with the following code.

Code:

Sub Sample3()

Dim A As Workbook

Set wb = Workbooks.Open("\VBA 1004 Error.xlsm", ReadOnly:=True, CorruptLoad:=xlExtractData)

End Sub

Run the above code to see the result.

We get this error because we have already opened the same workbook already.

VBA Runtime Error 1004 – Example #5

We get this error when we try to open a workbook which doesn’t exist. This is somewhat similar to the above error we get as VBA cannot find the workbook.

• Go to the Developer tab click on Visual Basic to Open VB Editor.
• Declare a sub-function to start writing the code.

Code:

Sub Sample4()

End Sub

• Try Open any workbook with the following code,

Code:

Sub Sample4()

Workbooks.Open Filename:="C:EDUCBA ContentAprilVBA OR Function.xlsm"

End Sub

• I have already deleted the sheet from the location.
• Run the code to see the result.

As the sheet doesn’t exist at the given location we get this error.

Things to Remember

• Always check for spelling mistakes.
• Do not rename multiple worksheets with the same name.
• Before calling any other reference to be sure to activate the respective worksheet.
• Before trying to open any other worksheet ensure the path provided is correct.

Recommended Articles

This has been a guide to VBA 1004 Error. Here we discussed Excel VBA Runtime Error 1004 along with practical examples and downloadable excel template. You can also go through our other suggested articles –

1. VBA LBound
2. VBA While Loop
3. VBA IsError
4. VBA CLng