none
Unable to obtain .... property from WorksheetFunction Class Error RRS feed

  • Question

  • Hello everyone:

    I'm running an application with a class and a public Function inside this class. This Function returns the average of the items of a Collection object.  So it should be simple to obtain. Let's see the code:

    With Application.WorksheetFunction
        Set CTM1 = DPOB.Item(NAño)
        TME = .Average(CTM1)
        Set CTM1 = Nothing
    End With

    Lets see it:

    CTM1 is a Collection Object

    DPOB is a Scripting.Dictionary Object

    TME is the name of the Property and its declared as double

    But, when I'm trying to get the Average property of the Collection items, appears an error called 1004.. lets see the screen:

    As you can see, Collection object is a Collection of Integers, so, Where its the problem? There is no regional problems, or or whatever stuff....


    Manuel Cavero

    Thursday, November 8, 2018 3:58 PM

Answers

  • The WF Average function doesn't work with collection objects, only arrays (a Range object of multiple cells directly returns an array), or values as individual arguments (there's a relatively small limit of arg's).

    Either adapt your collection to an array, or roll your own average function.

    FWIW, although worksheet functions are extremely efficient in cell formulas they can be relatively slow called with code.


    Thursday, November 8, 2018 4:58 PM
    Moderator

All replies

  • The WF Average function doesn't work with collection objects, only arrays (a Range object of multiple cells directly returns an array), or values as individual arguments (there's a relatively small limit of arg's).

    Either adapt your collection to an array, or roll your own average function.

    FWIW, although worksheet functions are extremely efficient in cell formulas they can be relatively slow called with code.


    Thursday, November 8, 2018 4:58 PM
    Moderator
  • Hi Peter:

    Neither WF Average works this way, nor WF.StDes either.....

    Thanks a lot for your answer!!!!


    Manuel Cavero

    Thursday, November 8, 2018 6:48 PM
  • Hello again Peter:

    I see that WF has another limitation on the number of elements in a matrix, around 20K, IE: the use of a WF Min function works up to approximately 20K elements, above that number the function does not work either

    Have a nice day


    Manuel Cavero

    Monday, November 19, 2018 6:59 AM