VBA 1004 Error

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.

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 Content\April\VBA 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