Sự thật về Excel – Cách Sửa Lỗi Runtime Error 1004 Trong Microsoft

Sự thật về Excel – Cách Sửa Lỗi Runtime Error 1004 Trong Microsoft là conpect trong nội dung hôm nay của chúng tôi. Theo dõi nội dung để biết đầy đủ nhé.


VBA and VB.Net Tutorials, Education and Programming Services

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.

Đang xem: Excel

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).SelectEnd Sub


Instead, we should write the code as follows:

Sub Range_Error()Range(“A1”).SelectEnd SubWe 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 = 5Do Until X = -1Range(“A” & X) = “Correct”X = X – 1LoopEnd Sub


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

Sub Range_Error()X = 5Do Until X = 0Range(“A” & X) = “Correct”X = X – 1LoopEnd 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.

Xem thêm: tai game bubble shooter

Sub Error_Name_Taken()ThisWorkbook.Sheets(“Sheet2”).ActivateActiveSheet.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”).ActivateThisWorkbook.Sheets(“Sheet1”).Range(“A1”).SelectEnd 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 wordappDim strFileName As StringstrFileName = “C:UsersmmostDropbox Else Without If.docx”Set wordapp = CreateObject(“word.Application”)wordapp.Documents.Open strFilewordapp.Visible = TrueEnd 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:UsersmmostDropboxAnother 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.

Xem thêm: 1️⃣ Bảng Ngọc Bổ Trợ, Cách Chơi Miss Fortune Mua 9, Cách Chơi Miss Fortune Mùa 9

Sub error_object_defined()ThisWorkbook.Sheets(1).Cells(-1, 1).SelectEnd Sub


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