损坏的 Excel VBA 宏运行时错误“1004”：对象“_Global”的方法“范围”失败
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25370674/
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
Broken Excel VBA Macro Run-time error '1004': Method 'Range' of object '_Global' failed
提问by B-Rell
I have a macro, called "Hide Completed", that has worked since April. The macro is supposed to put the items in order by completion date then hide the rows that have data in the date completed field. For some reason, it failed last week and I haven't been able to determine what went wrong. An identical macro is running on another tab (different names for tables and field names), and it works fine. I haven't found any threads on Stack Overflow (or other site) that are close enough to my problem to be of assistance. Here's the Macro:
我有一个名为“隐藏已完成”的宏，自 4 月以来一直有效。该宏应该按完成日期排列项目，然后隐藏在完成日期字段中包含数据的行。出于某种原因，上周它失败了，我无法确定出了什么问题。一个相同的宏正在另一个选项卡上运行（表和字段名称的不同名称），它工作正常。我还没有在 Stack Overflow（或其他网站）上找到任何与我的问题足够接近以提供帮助的线程。这是宏：
Sub hideCompleted()
'
' showCompleted Macro
'
Application.ScreenUpdating = False
'
Range("A1").Select
ActiveSheet.ListObjects("Table2").Range.AutoFilter Field:=1
' sortDataByDate Macro
'
ActiveWorkbook.Worksheets("Project List").ListObjects("Table2").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Project List").ListObjects("Table2").Sort.SortFields.Add _
Key:=Range("Table2[[#All],[Complete" & Chr(10) & "Date]]"), SortOn:=xlSortOnValues, _
Order:=xlAscending, DataOption:=xlSortTextAsNumbers
With ActiveWorkbook.Worksheets("Project List").ListObjects("Table1").Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
'
' hideCompleted Macro
'
'
Range("A2").Select
ActiveSheet.ListObjects("Table2").Range.AutoFilter Field:=1, Criteria1:="="
Application.ScreenUpdating = True
End Sub
Excel is calling attention to: Run-time error '1004': Method 'Range' of object '_Global' failed.
Excel 提醒注意：运行时错误“1004”：对象“_Global”的方法“范围”失败。
But the highlighted code in the debugger is:
但是调试器中突出显示的代码是：
I have checked the number of characters in individual cells to see if I'm over the 911 character limit (especially cells in the comment column - column F). That isn't the case. I'm also attaching an image of the excel worksheet to give an idea how it is used. Any help is appreciated.
我检查了单个单元格中的字符数，看看我是否超过了 911 个字符的限制（尤其是注释列中的单元格 - 列 F）。事实并非如此。我还附上了 Excel 工作表的图像，以了解它的使用方式。任何帮助表示赞赏。
回答by David Zemens
If your problem is what I think it is, then many other answers will resolve it.
如果您的问题是我认为的问题，那么许多其他答案将解决它。
The parameter you've provided for the Keyargument is not fully qualified, so it is implicitly doing:
您为Key参数提供的参数不是完全限定的，因此它隐式地执行：
ActiveWorkbook.Worksheets("Project List").ListObjects("Table2").Sort.SortFields.Add _
Key:=ActiveSheet.Range("Table2[[#All],[Complete" & Chr(10) & "Date]]"), SortOn:=xlSortOnValues, _
Order:=xlAscending, DataOption:=xlSortTextAsNumbers
Of course this will likely fail if the ActiveSheetis any sheet other than "Project List"at run-time.
当然，如果ActiveSheet是"Project List"运行时以外的任何工作表，这可能会失败。
Resolve it by qualifying the Range object to the appropriate worksheet:
通过将 Range 对象限定为适当的工作表来解决它：
Dim wsProjectList as Worksheet
Set wsProjectList = ActiveWorkbook.Worksheets("Project List")
wsProjectList.ListObjects("Table2").Sort.SortFields.Add _
Key:=wsProjectList.Range("Table2[[#All],[Complete" & Chr(10) & "Date]]"), _
SortOn:=xlSortOnValues, _
Order:=xlAscending, DataOption:=xlSortTextAsNumbers
回答by Mr. Mascaro
The answer is that the referenced field on the line with the error has been changed. If you update the field name in this formula to the correct one then the code will execute with no problems.
答案是错误行上的引用字段已更改。如果您将此公式中的字段名称更新为正确的名称，则代码将毫无问题地执行。
But, in the future it's probably safer to refer to table columns by their index number rather than the field name unless you're 100% sure that it will not change.
但是，将来通过索引号而不是字段名来引用表列可能更安全，除非您 100% 确定它不会改变。
