[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Error with custom VBA function

Posted on 2011-04-27
7
Medium Priority
?
389 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:appc
7 Comments
 
LVL 9

Expert Comment

by:JonMny
ID: 35477790
when you get the error what is it?
0
 
LVL 28

Assisted Solution

by:omgang
omgang earned 1000 total points
ID: 35477815
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
 

Author Comment

by:appc
ID: 35477819
I get it in the spreadsheet:

#value!
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 28

Assisted Solution

by:omgang
omgang earned 1000 total points
ID: 35477821
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
 
LVL 33

Expert Comment

by:jppinto
ID: 35477840
Remove the single "End"
0
 
LVL 28

Accepted Solution

by:
omgang earned 1000 total points
ID: 35477842
<<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
 

Author Comment

by:appc
ID: 35477845
It worked. Thanks!
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question