Excel错误1004“无法获取.... WorksheetFunction类的属性”出现不一致 [英] Excel error 1004 "Unable to get .... property of WorksheetFunction class" appearing inconsistently

问题描述

我在电子表格中有一个VBA函数，它在另一个在我的宏的早期阶段打开的电子表格中运行。用于正常工作的宏，但最近刚开始导致 1004 错误（无法获取WorksheetFunction类的RoundDown属性）。

我相信我明白错误是由于运行RoundDown而导致的，但是我看不出为什么会在我的宏中触发，而奇怪的是当我进入调试模式和VBE代码中的错误不会重现（尽管没有任何明显的改变）。

有没有人有类似的经验错误发生不一致，知道我可以做什么来解决它？

我是合理的VBA / Excel精明，但是关于进一步诊断的步骤的任何建议它将不胜感激我想知道打开的电子表格是否存在一些问题，但我看不到如何。

代码在这里。错误发生在标有注释的行上。

 公共函数GetDatesA（sWorkbookname As String，sSheetname As String，sCell As String ）As Variant 
 
 Dim vDateList（）As Variant 
 Dim currentCell As Range 
 Dim n As Long 
 
设置currentCell =工作簿（sWorkbookname）.Worksheets （sSheetname）.Range（sCell）
 
n = 0 
 
 Do 
如果修剪（currentCell.Value）=然后
退出执行
 Else 
 ReDim保存vDateList（0到1，0到n）
 vDateList（0，n）= WorksheetFunction.RoundDown（currentCell.Value，0）'错误发生在这一行
 vDateList（1，n）= currentCell.Column 
'Debug.Print currentCell.Value 
 End If 
设置currentCell = currentCell.Offset（0，1）
n = n + 1 
循环while currentCell.Column< XL_LAST_COLUMN 
 
 GetDatesA = vDateList 
 
结束功能
  

其他详细信息是：

• Excel版本：2010



• p>正在打开的文件本地驻留在我的C：驱动器上;我的宏在网络上的电子表格中




• 这两个文件的文件格式为.xls（即Excel 2003） - 我没有更改的选项这个




• Windows 7（不是我认为这是相关的）

我已经尝试过两点：

• 替换不同的工作表函数（例如Min （currentCell）），这也导致相同的问题




• 打开文件已经似乎阻止了这个问题 - 我想知道是否有一些工作簿正在打开（而不是我的主要工作簿中的宏）没有启用宏，这是干扰。但是即使这是因为我不知道如何解决它！

任何想法？ p>

解决方案

当传递给工作表函数的任何参数不是正确的类型或根本没有意义时，会发生此错误。 / p>

例如，当调用 WorksheetFunction.Asin 时，我有这个问题，参数大于1.在你的情况下，我猜想 currentCell.Value 是一个非数字值，不符合您所在地区有关数字的设置。

是的，错误消息真的是误导。

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 1004 error ("Unable to get RoundDown property of the WorksheetFunction class") when it runs.

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

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.

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)

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!

Any ideas?

解决方案

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.Asin with an argument bigger than 1. In your case, I'd guess currentCell.Value is a non-numeric value or one not according to your region settings regarding numbers.

Yes, the error message is really misguiding.

这篇关于Excel错误1004“无法获取.... WorksheetFunction类的属性”出现不一致的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！