Array as Argument in User-Defined Functions
 

An alternative to Harlan's suggestion would be to put the x value
first, and then use a ParamArray. For more on ParamArray see the Sub
statement in XL VBA help. One compiled but otherwise untested sample:

Option Explicit

Function Fx(ByVal x As Double, ParamArray Coeff() As Variant)
Dim i As Integer, Power As Integer
Power = 0
For i = LBound(Coeff) To UBound(Coeff)
Fx = Fx + Coeff(i) * x ^ Power
Power = Power + 1
Next i
End Function

And, for all those tempted to point out that I could have just as
easily used i instead of Power, yes, it may be possible. However, I am
not sure if the lower bound of a paramarray is guaranteed to be zero.
Using my own variable works around the uncertainty.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
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

Which would be a lot handier for my application than having to set up the code as

Function Polynomial(a,b,c,X_Value)
Polynomial=a + b*X_value + c*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?