Excel error 1004 "Unable to get .... property of WorksheetFunction class" appearing inconsistently
excel, excel-2010, vba
Solution
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.
Problem
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?