VBA functions in MS Excel

Question

I'm new to VBA, and I have a question, i.e I have a mathematical function 1 + 2x¹ + 3x² + 4x³ + ... +10x⁹ and I need to resolve it into two ways:

I can use raising operations(analog pow in Pascal) and IF statement;
without rising operations and IF statement...

I have tried this one:

Public Function test(x)


test = 1 + 2*x^1 + 3*x^2 + 4*x^3 + 5*x^4 + 6*x^5 + 7*x^6 + 8*x^7 + 9*x^8 + 10*x^9


End Function

but I think it returns the wrong answer - 2441406 with calling =test(5)

So can anyone give any advice, or help with my problem?

If you want the series of x raised to the power of 0 to 9 then the answer is correct. Your slightly iffy prose of describing what you want makes me confused (by 10x9 do you mean 10 times x to the power of 9 or what?). Also, forgive me but I am not sure what you mean by IF statament; 2 - without rising operations and IF statement. — Cor_Blimey
– Cor_Blimey, Commented Nov 4, 2014 at 22:18
If 2441406 is the wrong answer, then what is the right answer? — DeanOC
– DeanOC, Commented Nov 4, 2014 at 22:19
I see your edited question but my queries remain. Your function is returning the series of, for i = 0 to 9, Sum ( (i + 1) * x ^ i ). If that is not what you want then perhaps you need to define the algebraic series you want more clearly. — Cor_Blimey
– Cor_Blimey, Commented Nov 4, 2014 at 22:25
@ArtursGasjulis what you posted isn't valid vba so...there should be a * multiplication operator between the two operands. E.g. 10 * x ^ 9. I don;t really understand your posted code because it gives a syntax error. — Cor_Blimey
– Cor_Blimey, Commented Nov 4, 2014 at 22:28

tigeravatar · Accepted Answer · 2014-11-04 22:44:06Z

4

If you can't use VBA for this, there is a formula solution. Assuming your variable x is in cell A1, you would use this formula in another cell (I used B1):

=SUMPRODUCT(ROW($1:$10)*A1^(ROW($1:$10)-1))

When A1 = 5, it returned 23803711 as expected.

answered Nov 4, 2014 at 22:44

tigeravatar

26.7k5 gold badges32 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Alex · Accepted Answer · 2014-11-05 15:42:42Z

3

You will need * as the multiper:

Public Function test(x)
    test = _
         1 _
      + 2 * (x ^ 1) _
      + 3 * (x ^ 2) _
      + 4 * (x ^ 3) _
      + 5 * (x ^ 4) _
      + 6 * (x ^ 5) _
      + 7 * (x ^ 6) _
      + 8 * (x ^ 7) _
      + 9 * (x ^ 8) _
      + 10 * (x ^ 9)
End Function

edited Nov 5, 2014 at 15:42

answered Nov 4, 2014 at 22:26

Alex

1,6321 gold badge12 silver badges30 bronze badges

12 Comments

Cor_Blimey Over a year ago

indeed it does have the wrong syntax, so I am interested as to how it gave any answer let alone 2441406 :). It would give a syntax error without your correction.

Alex Over a year ago

There Cor_Blimey explained he did the equation somewhere else and give the incorrect answer. So I think there are 2 questions here indeed: 1) Why is the code not working, 2) If the code works is the equation correct. The correct answer seems to be 23803711 by the way

Alex Over a year ago

@guitarthrower thanks for the editing, yet the brackets are not needed , computer is smart to do multiply before addition =D

guitarthrower Over a year ago

@Alex I agree they are not needed, but given the confusion of the OP and the request they may help clarify what is happening first in the formula.

xmojmr Over a year ago

@guitarthrower what is the 2 in the test2 supposed to mean? Will it even compile?

|

Collectives™ on Stack Overflow

VBA functions in MS Excel

2 Answers 2

Comments

12 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

12 Comments

Your Answer

Sign up or log in

Post as a guest

Related