Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

share|improve this question
    
Take a look at the Range.FormulaArray property. That might help. msdn.microsoft.com/en-us/library/bb208529(v=office.12).aspx –  Rhys Gibson Sep 5 '11 at 23:12
    
Thanks a lot for the fast help. However 'Worksheets("Sheet1").Range("E1:E3").FormulaArray = "=Sum(A1:C3)"' works nicely as sub, but not as function. I am looking for a function that can be used also by other users like a normal function with a single cell (or array) result in the cell(s) that it is typed in. Maybe I should also mention that the formula is just a simplified example for a longer one that is used many times per worksheet (otherwise I'd just type it in Excel w/o VBA). –  Martin Sep 6 '11 at 20:51

1 Answer 1

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.