25

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?

Community
  • 1
  • 1
Neil
  • 686
  • 1
  • 9
  • 27
  • If you insert `Debug.Print currentCell.Value` just before `vDateList(0, n) = WorksheetFunction.RoundDown(currentCell.Value, 0)`, what do you get? Is it a valid number? – Siddharth Rout May 17 '12 at 11:54
  • 3
    Also change `WorksheetFunction.RoundDown(currentCell.Value, 0)` to `Application.WorksheetFunction.RoundDown(currentCell.Value, 0)` – Siddharth Rout May 17 '12 at 11:55
  • Thanks! I gave that a go. I get a list of dates (effectively numbers - they show as a date unless I multiply by 1 to get the underlying Excel number corresponding to the date). These definitely do all go through fine if I step through the code but don't if I let it run on it's own (when it pulls back the exact same values), so it's probably something about WorksheetFunction rather than my bad data. I'm now working on using "On Error..." - it seems to error just the first time, so a kludge might be to first do an arbitrary WorksheetFunction call with On Resume Next and then it turn off after. – Neil May 17 '12 at 14:56
  • Did you try my second suggestion? – Siddharth Rout May 17 '12 at 14:58

3 Answers3

30

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.

André Chalella
  • 13,788
  • 10
  • 54
  • 62
  • Just for reference, the same error occurs when calling WorksheetFunction.Ln with negative values. – Krøllebølle Jun 17 '14 at 07:00
  • or when asking for an item at position 0 in a 1-based Excel collection such as sheet.Pictures(0) – Cel Aug 06 '14 at 08:51
  • It also occurs when the range you are trying to transpose isn't "in one piece". So you can't transpose ranges that are fractured – Da Spotz Oct 25 '16 at 13:42
  • Saw this when using WorkSheetFunction.Min(), got error when I passed a string range, but it worked when passing a range object. Thanks! – s2t2 Nov 30 '18 at 17:07
  • 1
    Or if passing a string argument where a numeric is expected – Murrah Mar 30 '20 at 01:00
1

I got the "Unable to get * property of WorksheetFunction Class" error using Transpose, MMult,MDterm, and MInverse functions.

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.

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.

James
  • 11
  • 3
0

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

Have a look at the Range... it makes no sense. Amended the range from "AC8:C" to "AC8:AC" and it will work perfectly

mojo3340
  • 534
  • 1
  • 6
  • 27