EDUCBA

Home VBA VBA Resources VBA Error Handling Functions VBA 1004 Error

VBA 1004 Error

Madhuri Thakur
Article byMadhuri Thakur

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.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

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

As explained about this error, this error occurs when we refer to an incorrect named range value in VBA. This can happen if we make a spelling mistake of the named range of to refer a range that doesn’t even exist. To demonstrate this let us make a named range first. I have the following data here.

VBA Error 1004 Example 1

  • Let us name this table header as DATA.

VBA Error 1004 Example 1-1

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

VBA Error 1004 Example 1-2

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

Code:

Sub Sample()

End Sub

VBA Error 1004 Example 1-3

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

Code:

Sub Sample()

Range("Data").Select

End Sub

VBA Error 1004 Example 1-4

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

Result of Example 1-4

  • Now we misspell the spelling for the header name.

Code:

Sub Sample()

Range("Dataa").Select

End Sub

VBA Error 1004 Example 1-5

  • Run the code again to see the result.

Result of Example 1-6

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.

VBA Error 1004 Example 2

  • 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

VBA Error 1004 Example 2-2

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

Code:

Sub Sample1()

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

End Sub

VBA Error 1004 Example 2-3

  • Run the above code and see the result.

Result of Example 2

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

VBA Error 1004 Example 3-2

  • Declare two variables A and B as an integer.

Code:

Sub Sample2()

Dim A As Integer
Dim B As Integer

End Sub

VBA Error 1004 Example 3-3

  • 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

VBA Error 1004 Example 3-4

Code:

Sub Sample2()

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

End Sub

VBA Error 1004 Example 3-5

  • Run the code to see the result obtained.

Result of Example 3

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

VBA Error 1004 Example 4-2

  • Declare a variable as the workbook.

Code:

Sub Sample3()

Dim A As Workbook

End Sub

VBA Error 1004 Example 4-3

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

VBA Error 1004 Example 4-4

Run the above code to see the result.

Result of Example 4

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

Example 5-1

  • Try Open any workbook with the following code,

Code:

Sub Sample4()

Workbooks.Open Filename:="C:\EDUCBA Content\April\VBA OR Function.xlsm"

End Sub

Example 5-2

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

Result of Example 5-3

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