VBA 错误 1004 - 范围类的选择方法失败

声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow 原文地址: http://stackoverflow.com/questions/10949893/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me): StackOverFlow

提示:将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间:2020-09-08 13:17:29  来源:igfitidea点击:

VBA error 1004 - select method of range class failed

vb.netexcel-vbaexcel-2010powerpoint-vbavba

提问by Onekuo

First time poster, so if there is any formatting, or guidelines I failed to adhere to, please let me know so that I can fix it.

第一次海报,所以如果有任何格式或我未能遵守的指导方针,请告诉我,以便我可以修复它。

So I am basically asking the user for the file directory of the excel file, then I setup some variables (originally set at public as project variables, since these were being used and changed in other places). I have also added the lines to set these variables to nothing (just in case, I do not think that it should matter). I then set these variables to the excel file, workbook, and sheets that I want to access.

所以我基本上是向用户询问 excel 文件的文件目录,然后我设置了一些变量(最初在 public 设置为项目变量,因为这些在其他地方被使用和更改)。我还添加了将这些变量设置为空的行(以防万一,我认为这无关紧要)。然后我将这些变量设置为我想要访问的 excel 文件、工作簿和工作表。

Dim filepath as String
filePath = CStr(fileDialog)              'ask file dir, set to string
Dim sourceXL As Variant                  'these three were orig project variables
Dim sourceBook As Variant
Dim sourceSheet As Variant
Dim sourceSheetSum As Variant

Set sourceXL = Nothing                    'set to nothing in case...?
Set sourceBook = Nothing
Set sourceSheet = Nothing
Set sourceSheetSum = Nothing

Set sourceXL = Excel.Application          'set to the paths needed
Set sourceBook = sourceXL.Workbooks.Open(filePath)
Set sourceSheet = sourceBook.Sheets("Measurements")
Set sourceSheetSum = sourceBook.Sheets("Analysis Summary")

Dim measName As Variant                    'create variable to access later
Dim partName As Variant

sourceSheetSum.Range("C3").Select           'THIS IS THE PROBLEM LINE

measName = sourceSheetSum.Range(Selection, Selection.End(xlDown)).Value
sourceSheetSum.Range("D3").Select
partName = sourceSheetSum.Range(Selection, Selection.End(xlDown)).Value

So I created two different sheet variables 'sourceSheets' and 'sourceSheetsSum', the code works if i use 'sourceSheets', but error 1004 occurs if i use 'sourceSheetsSum'. I have also tried the code with the variable 'sourceSheet' removed completely, in case that was overriding 'sourceSheetSum' for some reason.

因此,我创建了两个不同的工作表变量“sourceSheets”和“sourceSheetsSum”,如果我使用“sourceSheets”,则代码有效,但如果我使用“sourceSheetsSum”,则会出现错误 1004。我还尝试了完全删除变量 'sourceSheet' 的代码,以防由于某种原因覆盖了 'sourceSheetSum'。

I am fairly confident that the excel workbook and sheets exist and are being called correctly, since I ran a quick bit of code to loop through all the sheets in the workbook and output the names, shown below.

我相当确信 excel 工作簿和工作表存在并且被正确调用,因为我运行了一些代码来循环遍历工作簿中的所有工作表并输出名称,如下所示。

For j = 1 To sourceBook.Sheets.Count
Debug.Print (Sheets(j).name)
Next j

With the debug output of

随着调试输出

Measurements
Analysis Summary
Analysis Settings

测量
分析摘要
分析设置

So, does anyone have any ideas what this error could mean, or how I can possibly go about finding more about what the error actually is?

那么,有没有人知道这个错误可能意味着什么,或者我如何才能找到更多关于错误实际上是什么的想法?

EDIT: So I decided to add a bit to the listing of the sheet names, not sure if it will help at all.

编辑:所以我决定在工作表名称列表中添加一点,不确定它是否有帮助。

For j = 1 To sourceBook.Sheets.Count
    listSheet(j) = Sheets(j).name
Next j    
Debug.Print (listSheet(2))    
Set sourceSheetSum = sourceBook.Sheets(listSheet(2))

The debug prints Analysis Summary, so I know that the sheet exists in the workbook, and there should not be any issues with a 'typo' in the names.
The code still has the same error at the same line though.

调试打印分析摘要,所以我知道该工作表存在于工作簿中,并且名称中的“错字”不应该有任何问题。
尽管如此,代码在同一行仍然有相同的错误。

deusxmach1na: I think you wanted me to change

deusxmach1na:我想你想让我改变

Dim sourceXL As Variant                  
Dim sourceBook As Variant
Dim sourceSheet As Variant
Dim sourceSheetSum As Variant

Set sourceSheet = sourceBook.Sheets("Measurements")

To

Dim sourceXL As Excel.Application
Dim sourceBook As Excel.Workbook
Dim sourceSheet As Worksheet
Dim sourceSheetSum As Worksheet

Set sourceSheet = sourceBook.Worksheets("Measurements")

But this does not change the error, I remember I had it similar to that, and then changed it since I read that variant is like a catch all, not actually that solid on what variant is.

但这并没有改变错误,我记得我有过类似的错误,然后改变了它,因为我读到那个变体就像一个包罗万象的东西,实际上并不是那么可靠。

回答by Jon Crowell

You have to select the sheet before you can select the range.

您必须先选择工作表,然后才能选择范围。

I've simplified the example to isolate the problem. Try this:

我已经简化了示例以隔离问题。尝试这个:

Option Explicit


Sub RangeError()

    Dim sourceBook As Workbook
    Dim sourceSheet As Worksheet
    Dim sourceSheetSum As Worksheet

    Set sourceBook = ActiveWorkbook
    Set sourceSheet = sourceBook.Sheets("Sheet1")
    Set sourceSheetSum = sourceBook.Sheets("Sheet2")

    sourceSheetSum.Select

    sourceSheetSum.Range("C3").Select           'THIS IS THE PROBLEM LINE

End Sub

Replace Sheet1 and Sheet2 with your sheet names.

用您的工作表名称替换 Sheet1 和 Sheet2。

IMPORTANT NOTE:Using Variants is dangerous and can lead to difficult-to-kill bugs. Use them only if you have a very specific reason for doing so.

重要说明:使用变体是危险的,可能会导致难以杀死的错误。仅当您有非常具体的原因时才使用它们。

回答by assylias

You can't select a range without having first selected the sheet it is in. Try to select the sheet first and see if you still get the problem:

在没有先选择它所在的工作表的情况下,您无法选择一个范围。尝试先选择工作表,看看是否仍然遇到问题:

sourceSheetSum.Select
sourceSheetSum.Range("C3").Select

回答by Siddharth Rout

assylias and Head of Catering have already given your the reason why the error is occurring.

assylias 和餐饮主管已经向您说明了发生错误的原因。

Now regarding what you are doing, from what I understand, you don't need to use Selectat all

现在关于你在做什么,从我的理解,你不需要使用Select在所有

I guess you are doing this from VBA PowerPoint? If yes, then your code be rewritten as

我猜你是从 VBA PowerPoint 中这样做的?如果是,那么您的代码将被重写为

Dim sourceXL As Object, sourceBook As Object
Dim sourceSheet As Object, sourceSheetSum As Object
Dim lRow As Long
Dim measName As Variant, partName As Variant
Dim filepath As String

filepath = CStr(FileDialog)

'~~> Establish an EXCEL application object
On Error Resume Next
Set sourceXL = GetObject(, "Excel.Application")

'~~> If not found then create new instance
If Err.Number <> 0 Then
    Set sourceXL = CreateObject("Excel.Application")
End If
Err.Clear
On Error GoTo 0

Set sourceBook = sourceXL.Workbooks.Open(filepath)
Set sourceSheet = sourceBook.Sheets("Measurements")
Set sourceSheetSum = sourceBook.Sheets("Analysis Summary")

lRow = sourceSheetSum.Range("C" & sourceSheetSum.Rows.Count).End(xlUp).Row
measName = sourceSheetSum.Range("C3:C" & lRow)

lRow = sourceSheetSum.Range("D" & sourceSheetSum.Rows.Count).End(xlUp).Row
partName = sourceSheetSum.Range("D3:D" & lRow)

回答by Pete

Removing the range select before the copy worked for me. Thanks for the posts.

在副本对我有用之前删除范围选择。谢谢你的帖子。