EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login
Home VBA VBA Resources VBA Error Handling Functions VBA 1004 Error
Secondary Sidebar
VBA Error Handling Functions
  • VBA Error
    • VBA IsError
    • VBA On Error
    • VBA IFError
    • VBA 1004 Error
    • VBA Subscript out of Range
    • VBA OverFlow Error
    • VBA Error Handling
    • VBA On Error Resume Next
    • VBA On Error Goto
    • VBA On Error GoTo 0
    • VBA Type Mismatch

VBA 1004 Error

By Madhuri ThakurMadhuri 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.

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

All in One Financial Analyst Bundle(250+ Courses, 40+ Projects)
Financial ModelingInvestment BankingUS GAAPCFA-Level 1 & 2
Equity ResearchM & A ModelingPrivate Equity ModelingForex Trading
Price
View Courses
250+ Online Courses | 40+ Projects | 1000+ Hours | Verifiable Certificates | Lifetime Access
4.9 (86,580 ratings)
  • 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.

Watch our Demo Courses and Videos

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

  • 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

  • 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

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
0 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign In
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

ISO 10004:2018 & ISO 9001:2015 Certified

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Watch our Demo Courses and Videos

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

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Watch our Demo Courses and Videos

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

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more