Excel array functions in worksheets are useful. E.g. for calculating the average of the sin values of an array A1:A5 I enter the following which works nicely:
Now I would like to create a function in VBA that does the job, something like:
Function MyFunction(r As Variant) As Variant ' r is the range, e.g. A1:A5 as above MyFunction = WorksheetFunction.Average(Sin(r)) End Function
This function should result in a single value, but it does not work (no matter if I enter it as a normal or array formula in an Excel cell). I thought the problem may be that the VBA sin function is different from the worksheet sin function, but replacing sin by WorksheetFunction.Sin also does not work.
Of course one could find out the number of values, write a loop that stores
sin(r[i=1..n]) values in a temporary array variable and then calculate the average, but I am looking for a more elegent and efficient solution.