vba Excel 错误 1004“无法获取 .... WorksheetFunction 类的属性”出现不一致
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10635048/
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
Excel error 1004 "Unable to get .... property of WorksheetFunction class" appearing inconsistently
提问by Neil
I have a VBA function within a spreadsheet which operates on another spreadsheet that is opened in an earlier stage of my macro. The macro used to work fine but just recently has started causing a 1004error ("Unable to get RoundDown property of the WorksheetFunction class") when it runs.
我在电子表格中有一个 VBA 函数，该函数在我的宏的早期阶段打开的另一个电子表格上运行。该宏过去运行良好，但最近在运行时开始导致1004错误（“无法获取 WorksheetFunction 类的 RoundDown 属性”）。
I believe I understand what the error would be caused by (a problem running RoundDown) but I cannot see why it is getting triggered in my macro and the odd part is that when I go into Debug mode and step through the code in the VBE the error does not recur (despite nothing obviously changing).
我相信我明白错误是由（运行 RoundDown 的问题）引起的，但我不明白为什么它会在我的宏中被触发，奇怪的是，当我进入调试模式并逐步执行 VBE 中的代码时错误不会再次发生（尽管没有明显变化）。
Does anyone have a similar experience of this sort of error occuring inconsistently and know what I could do to resolve it?
有没有人对这种不一致发生的错误有类似的经历，并且知道我可以做些什么来解决它？
I'm reasonably VBA/Excel-savvy, but any suggestions on further steps to diagnose it would be appreciated. I am wondering if there is some issue with the opened spreadsheet not being ready but I cannot see how.
我相当精通 VBA/Excel，但任何有关诊断它的进一步步骤的建议将不胜感激。我想知道打开的电子表格没有准备好是否有问题，但我看不出是怎么回事。
The code is here. The error occurs on the line marked with a comment.
代码在这里。错误发生在标有注释的行上。
Public Function GetDatesA(sWorkbookname As String, sSheetname As String, sCell As String) As Variant
Dim vDateList() As Variant
Dim currentCell As Range
Dim n As Long
Set currentCell = Workbooks(sWorkbookname).Worksheets(sSheetname).Range(sCell)
n = 0
Do
If Trim(currentCell.Value) = "" Then
Exit Do
Else
ReDim Preserve vDateList(0 To 1, 0 To n)
vDateList(0, n) = WorksheetFunction.RoundDown(currentCell.Value, 0) 'error occcurs on this line
vDateList(1, n) = currentCell.Column
'Debug.Print currentCell.Value
End If
Set currentCell = currentCell.Offset(0, 1)
n = n + 1
Loop While currentCell.Column < XL_LAST_COLUMN
GetDatesA = vDateList
End Function
Other details are:
其他详情如下：
Excel version: 2010
File being opened resides locally on my C: drive; my macro is in a spreadsheet on the network
File format for both files is .xls (i.e. Excel 2003) - I don't have the option of changing this
Windows 7 (not that I think it would be relevant)
Excel版本：2010
正在打开的文件位于我本地的 C: 驱动器上；我的宏在网络上的电子表格中
两个文件的文件格式都是 .xls（即 Excel 2003） - 我没有更改此选项的选项
Windows 7（不是我认为它会相关）
Two points I've tried already are:
我已经尝试过的两点是：
Substitute a different worksheet function (e.g. Min(currentCell)) and that also causes the same problem
Having the file open already seems to stop the problem - I wonder if there is some way that the workbook which is being opened (rather than my main workbook with the macro in it) is not enabled for macros and this is interfering. But even if this is the cause I'm not sure how to get around it!
替换不同的工作表函数（例如 Min(currentCell)），这也会导致同样的问题
打开文件似乎已经解决了这个问题 - 我想知道是否有某种方式没有为宏启用正在打开的工作簿（而不是我的带有宏的主工作簿），这会造成干扰。但即使这是原因，我也不知道如何解决它！
Any ideas?
有任何想法吗？
回答by André Chalella
This error occurs often when any argument passed to the worksheet function is not of the correct type or simply doesn't make sense.
当传递给工作表函数的任何参数的类型不正确或根本没有意义时，通常会发生此错误。
For example, I've had this problem when calling WorksheetFunction.Asinwith an argument bigger than 1. In your case, I'd guess currentCell.Valueis a non-numeric value or one not according to your region settings regarding numbers.
例如，当我WorksheetFunction.Asin使用大于 1 的参数进行调用时，我遇到了这个问题。在你的情况下，我猜currentCell.Value是一个非数字值，或者不是你的区域设置的数字值。
Yes, the error message is really misguiding.
是的，错误消息确实具有误导性。
回答by James
I got the "Unable to get *property of WorksheetFunction Class" error using Transpose, MMult,MDterm, and MInverse functions.
使用 Transpose、MMult、MDterm 和 MInverse 函数时，出现“无法获取*WorksheetFunction 类的属性”错误。
I was able to get my code to run by putting "Option Base 1" in the Declarations (before the actual code) section of the particular Module in the Editer.
我能够通过将“Option Base 1”放在编辑器中特定模块的声明（在实际代码之前）部分来运行我的代码。
Excel assumes "Option Base 0" which will add an extra row and column of empty cells. This will cause the error to occur and isn't immediately obvious to see.
Excel 假定“Option Base 0”将添加额外的一行和一列空单元格。这将导致错误发生并且不会立即明显看到。
回答by user1
I have come accross this before, and for me it was becase the criteria range made no sense, as Andre said above.
我以前遇到过这个问题，对我来说，这是因为标准范围没有意义，正如安德烈在上面所说的那样。
See example formula below:
.Cells(11, i).Formula = Application.WorksheetFunction.CountIfs(Sheets("Sheet1").Range("AC8:C" & n), "S")
请参阅下面的示例公式：
.Cells(11, i).Formula = Application.WorksheetFunction.CountIfs(Sheets("Sheet1").Range("AC8:C" & n), "S")
Have a look at the Range... it makes no sense. Amended the range from "AC8:C"to "AC8:AC"and it will work perfectly
看看Range……没有意义。修改了从"AC8:C"到的范围"AC8:AC"，它将完美地工作
