How do I convert the SERIESSUM function to VB6?

Question

On the Daily Dose of Excel website, written by the late Frank Kabel, there are some formulae which can stand in for ATP functions. Not being an Excel guru, I'm struggling with converting one (so far!) to VB6. (Why I'm doing this I may relate once the NDA runs out.)

The problem I'm having is with the code standing in for SERIESSUM, namely,

=SUMPRODUCT(coefficients,x^(n+m*(ROW(INDIRECT("1:"&ROWS(coefficients)))-1)))

Now the SUMPRODUCT and ROWS functions I've been able to render fairly simply with

Public Function SUMPRODUCT(a1 As Variant, a2 As Variant) As Double
    Dim dRes As Double
    Dim dVal As Double
    Dim i As Long
    If LBound(a1) = LBound(a2) And UBound(a1) = UBound(a2) Then
        For i = LBound(a1) To UBound(a1)
            dVal = a1(i) * a2(i)
            dRes = dRes + dVal
        Next
    End If
    SUMPRODUCT = dRes
End Function

Public Function ROWS(a1 As Variant)
    ROWS = UBound(a1) - LBound(a1) + 1
End Function

What I don't 'get' yet is

• how x^(n+m*(ROW(INDIRECT("1:"&ROWS(coefficients)))-1)) evaluates to an array
• and what that array might contain

Any Excel gurus out there?

Answer 1

ROW(INDIRECT("1:"&ROWS(coefficients)))-1

If coefficients has 5 rows, this will return the array {1,2,3,4,5}. The rest of the progression is

{1m, 2m, 3m, 4m, 5m)

{n+1m, n+2m, n+3m, n+4m, n+5m)

{x^n+1m, x^n+2m, x^n+3m, x^n+4m, x^n+5m)

That resulting array gets 'series summed' against coeffecients.

You can see the progression in Excel's formula bar by using Ctrl+= on highlighted parts of the formulas. There is a limit on how many characters you can display in the formula bar, so if coefficients has a lot of rows, you may get the error "formula too long"

In the formula bar, select ROW(INDIRECT("1:"&ROWS(coefficients)))-1 and press Ctrl+=. Then select another portion of your formula, making sure you match opening and closing parentheses, and hit Ctrl+=. You can iterate this until you have the whole formula calculated. When you're done, be sure to ESCAPE out of the cell so you don't lose your original formula.

See also Episode 474 here.