Vba – Use of array formulas within VBA

vbaworksheet-function

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:

{=AVERAGE(SIN(A1:A5))}

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.

Best Answer

The issue here is that the Sin function expects a single value as a parameter, not an array

Note that VBA function Sin is not the same as worksheet function Sin (and Sin is not a member of the WorkSheetFunction object, so WorksheetFunction.Average(WorksheetFunction.Sin(r)) won't work either)

If you change your udf to

Function MyFunction(r As Variant) As Variant     ' r is the range, e.g. A1:A5 as above  
    MyFunction = WorksheetFunction.Average(r)  
End Function

you will get the average of the specified range, which shows udf's can handle array data.

If you are looking for an efficient method, my advise is to stick with native functions whnever you can (array or otherwise), as a call to a udf, any udf, is much slower than native functions.

Related Question