Array as Argument in User-Defined Functions
 

"?B?TWFyayBTY2hyZWliZXI=?=" wrote...
I am trying to write a user-defined function that calculates a complex
function, based on a given set of coefficients. For simplicity, let's
say the equation is y=a + bx + cx^2. I would like to set up the user-
defined function so that the argument for the coefficients can be input
as a range. Thinking something like this:

Function Polynomial(Coefficients,X_value)
Polynomial=Coefficient(1,0) + Coefficient(2,0)*X_value + _
Coefficient(3,0)*X_value^2
End Function
...
The actual function is more complex, and contains some logic testing
requirements. So I am trying to avoid writing a function that requires 15
input arguments. Can you offer some insight into a classy method to
accomplish this?


Function foo(bar As Variant, x As Double) As Variant
Dim k As Long

On Error GoTo ErrorHandler

For k = 1 To NeededNumberOfArguments
foo = foo + bar(k) * x ^ (k - 1)
Next k

ErrorHandler:
If Err.Number < 0 Then foo = CVErr(xlErrValue)
Err.Clear

End Function

--
To top-post is human, to bottom-post and snip is sublime.