CODEWITHSUNDEEP
vba
mary
mary

Reputation: 11

Accumulate number of VBA function is not working

I am trying to use VBA function to add up a number's all power result together.

For example if I have 6 as revenue, and have first=1 and last=5, diff=5-1=4, then the result should be like total=6+6^2+6^3+6^4.

Below is my VBA code and not sure why it is not working and always give me result as 0:

Function AC(last, first, revenue)
    diff = last - firs
    For i = 1 To diff
        Count = revenue ^ i
        Total = Total + Count
    Next i
End Function

Thanks if anyone can help me

Upvotes: 1

Views: 115

Answers (3)

A.S.H
A.S.H

Reputation: 29332

Here's a loop-less implementation:

Function AC(last, first, revenue)
    If revenue = 1 then AC = last - first: Exit Function
    AC = revenue*((revenue^(last - first) - 1) / (revenue-1))
End Function

Upvotes: 0

Slai
Slai

Reputation: 22876

With array formula AC = [sum(6 ^ row(1:4))] ( # is short for As Double) :

Function AC#(last#, first#, revenue#)
    AC = Evaluate("sum(" & revenue & " ^ row(1:" & last - first & "))")
End Function

With Excel Formula:

=SUMPRODUCT(revenue ^ ROW(INDIRECT("1:" & last - first)))

Upvotes: 0

Scott Craner
Scott Craner

Reputation: 152495

You need to assign a value to the Function for it to return a value. You also have a spelling error on diff = last - firs :

Function AC(last, first, revenue)

diff = last - first
For i = 1 To diff
    Count = revenue ^ i
    Total = Total + Count
Next i
AC = Total
End Function

To be safe one should also always declare the variables, and we can do the addition right to the function:

Option Explicit

Function AC(ByVal last As Long, ByVal first As Long, ByVal revenue As Double) As Double
Dim diff As Long
Dim i as long
Dim Count as Double
diff = last - first
For i = 1 To diff
    Count = revenue ^ i
    AC = AC + Count
Next i

End Function

Upvotes: 2

Related Questions