problem with "frequency" function

[ck_123]

try to develop an user define function,but faced problem with "frequency" function,it return invalid value error.

Please Login or Register  to view this content.

but if i change UDF = Evaluate(" FREQUENCY()") to Evaluate(" SUM()"), it work well and sum all the value within array.

so i believe there is a problem with array within the frequency function,because when i am using formula "=FREQUENCY(OFFSET(A10,0,1,3,3),50)" it return the result i want, i found that "Linest" function having the same problem.

[MrShorty]

I think it is a data type mismatch problem. You have dimensioned UDF as a double, which contains one value. The Frequency function returns an array (2 or more values). VBA does not know how to assign an array to a double. The Linest function also returns an array, but the sum function returns a double, which is why the sum function works, but the other two do not.

When I have done this (with the linest function, not the frequency function, though it should work the same), I will dimension a temporary variable as a variant (which can hold an array) and assign the result of the frequency function to this temporary variant. Then I can decide which element (or elements) I want assigned to the function for returning to the spreadsheet/calling routine.

Please Login or Register  to view this content.

[ck_123]

I think it is a data type mismatch problem. You have dimensioned UDF as a double, which contains one value. The Frequency function returns an array (2 or more values). VBA does not know how to assign an array to a double. The Linest function also returns an array, but the sum function returns a double, which is why the sum function works, but the other two do not.

When I have done this (with the linest function, not the frequency function, though it should work the same), I will dimension a temporary variable as a variant (which can hold an array) and assign the result of the frequency function to this temporary variant. Then I can decide which element (or elements) I want assigned to the function for returning to the spreadsheet/calling routine.

Please Login or Register  to view this content.

thank you,i had dimensioned UDF as a variant and it is working,can you write full part for the part "code to determine what value to return to udf" if i want UDF return any figure from frequency

[MrShorty]

I'm not sure that I can write it, because I do not know how you are deciding which element(s) to return.

If you simply want to return all of the results, skip that part and simply assign UDF=temp. This means that UDF will also contain an array, so the calling procedure/spreadsheet needs to be prepared for that. For example, if this is called from a spreadsheet, you need to have multiple cells selected and enter the function as an array function with ctrl-shift-enter. You may also need to test whether temp and UDF are vertical or horizontal arrays so you know how to orient the calling array.

If you want to select an individual value to return, then you decide which value to return. Perhaps an IF..Then..else statement or a select case statement or whatever works for determining i. Then you simply assign that value to udf.

[ck_123]

ok i got it,thank