Error with custom VBA function

I get no error when I debug the function in VB but I get an error when I use the function...

Can someone help me?

Here is the code:

Function Amortization(Original As Double, Amort As Double, Months As Double, Rate As Double)
   
    Dim Previous As Double
    Dim Total As Double
    Dim i As Integer
   
    Previous = 0
   
    For i = 1 To Months
                       
                        Total = (Original - Amort * (i - 1)) / ((1 + Rate) ^ i) + Previous
       
        Previous = Total
   
    Next i
   
    End
   
        Amortization = Total
   
End Function
appcAsked:
Who is Participating?
 
omgangConnect With a Mentor IT ManagerCommented:
<<I get it in the spreadsheet:

#value! >>

Since you did not have a return type specified for the function it defaults to Variant.  The default value for a Variant data type is Null.  The End statement is preventing your return value assignment from executing so the Function value is always Null.  Hence the #value!
.....I think.
OM Gang
0
 
JonMnyCommented:
when you get the error what is it?
0
 
omgangConnect With a Mentor IT ManagerCommented:
Your function does have any return value specified, e.g. it doesn't return anything.  Additionally, you have an End statement after the For..Next loop so the Amortization = Total statement never gets executed.  I don't receive any errors when running the function.
OM Gang
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
appcAuthor Commented:
I get it in the spreadsheet:

#value!
0
 
omgangConnect With a Mentor IT ManagerCommented:
Try this revised function.  Does it do what you want?
OM Gang


Function Amortization(Original As Double, Amort As Double, Months As Double, Rate As Double) As Double
   
    Dim Previous As Double
    Dim Total As Double
    Dim i As Integer
   
    Previous = 0
   
    For i = 1 To Months
                       
                        Total = (Original - Amort * (i - 1)) / ((1 + Rate) ^ i) + Previous
       
        Previous = Total
   
    Next i
   
    'End
   
        Amortization = Total
   
End Function
0
 
jppintoCommented:
Remove the single "End"
0
 
appcAuthor Commented:
It worked. Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.