Working built-in function won't work within User-defined function

[roscoe]

Excel includes the builtin function SERIESSUM( x, n, m, coefficients ). All examples I have found show the coefficients as an array such as {1,1,1,1}.

I used that in the following on my spreadsheet: =SERIESSUM( C39, 1, 1, {1,1,1,1,1} ) and it works as desired.
However, I want to make a simpler version by putting this within a user defined function such as:

Function RateB(RateA)
    RateB = SERIESSUM(RateA, 1, 1, {1,1,1,1,1} )
End function

Seems easy enough...but VB throws an error that "{" is not a valid character. I've also tried it with application.seriessum but that fails also.

Ideas?

 

[Michael M]

Have you tried putting the array into a range then passing that range to the formula

Function RateB(RateA)
    RateB = SeriesSum(RateA, 1, 1,1, A1:A5 )
End Function

 

[roscoe]

I haven't...it never changes so I wanted to do it all within the user function. I can give that a try though

Nope: VB didn't like the ":"

 

[Michael M]

Haven't got Excel atm, so try...adjusting your array to suit

Function RateB(RateA)
    RateB = WorksheetFunction.SeriesSum(RateA, 1, 1, Range("A1:A5"))
End Function

 

[roscoe]

VB didn't object to that...but when I entered =RateB(c39) I got #NAME?.

Been a long time...not sure what I'm doing wrong.

 

[Michael M]

Is C39 a text entry ??

 

[Tetra201]

Try

Function RateB(RateA)
    RateB = WorksheetFunction.SeriesSum(RateA, 1, 1, Array(1, 1, 1, 1, 1))
End Function

 

[roscoe]

Is C39 a text entry ??

A cell reference to a number

 

[roscoe]

Try

Function RateB(RateA)
    RateB = WorksheetFunction.SeriesSum(RateA, 1, 1, Array(1, 1, 1, 1, 1))
End Function

No change...