# Thread: Value from custom function

1. ## Value from custom function

I have written a custom function that uses a combination of FV and PV . When I run it in excel it first returns a Value and when I clcik the cell again it shows the correct answer.

This is the function can anyone suggest what I am doing incorrectly.

Function FV2Way(yield, Years, Inflation, FirstPayment)
ActiveCell = FV(yield, Years, 0, PV((1 + yield) / (1 + Inflation) - 1, Years, FirstPayment, 0, 1))
End Function

thanks

peter

2. Peter,

I'm having a problem following what you're doing especially with out sample data and expected results.
What I'd try first is breaking down the calculations, e.g. do PV first then plug it into FV that will allow you to check the intermediate results either through break points or message boxes and also make it easier to compare the calculations to the function definitions for PF & FV in help. HTH

3. Maybe the "ActiveCell=" statement needs to have an active cell to work.

cheers, Paul

4. Part of the problem may be that this is not the typical way that a function works!

A function should be written in such a way as to return a value which can then be assigned to a cell value e.g. =myFunction(arguments). Modifying cells is not generally recommended within functions (I'm not even sure that it works!) and if you have this function used in a number of cells, do you really want it changing the active cell which may be somewhere unrelated to the function!!

5. ## The Following User Says Thank You to jeremybarker For This Useful Post:

mitchbvi (2013-09-08)

6. Jeremy,

You hit it on the head! I don't know how I missed it.
Code:
```Function FV2Way(yield, Years, Inflation, FirstPayment) as Double
FV2Way = FV(yield, Years, 0, PV((1 + yield) / (1 + Inflation) - 1, Years, FirstPayment, 0, 1))
End Function```
As Jeremy said you have the function pass back the value it calculates by assigning that value to the function name.
Thus, in say cell G5 you would have the formula:
=FV2Way(yield,years,inflation,firstpayment)
Of course replacing the parameter names w/either constants or cell references or even formulas of their own.
Everytime something referenced in the G5 formula changes the function is called and the value of G5 changes accordingly. HTH

7. ## The Following User Says Thank You to RetiredGeek For This Useful Post:

mitchbvi (2013-09-08)

8. Hi Rg and Jeremy

Thanks so much that works perfectly. The formula worked in a cell and as I was using it a lot I thought a custom function would be the way to go. RG, excuse a dumb question why as Double?

thanks again

Peter

9. Peter,

No such thing as a dumb question!

I'm a programmer and have been doing and teaching it for 40+ years now {and still learning}. So now you know where I'm coming from let me explain why Double. It is good programming practice to Declare all your variables (in the case of VBA that means the DIM command) and arguments in your functions/sub routines. In this case the as double declares (dims) the return value (answer) of the function and allows for large numbers with decimal places. If you don't do this VBA will use a type of Variant which will work but is much less efficient and also will not allow for the VBA compiler to catch errors at the compilation stage which may then show up later in the execution stage as #Value or #N/A errors.

I make it a practice to always include Option Explicit at the top of all my modules. (BTW - there is an option in the VBE to make this happen automatically).
VBA Options.JPG
This forces you to declare (dim) all of your variables which can take the guess work out of things when you are trying to find out (debug) why things are not working properly. So if I re-did your code the way I would do it it would look like this.
Code:
```Option Explicit

Function FV2Way(dYyield as Double, dYears as Double, dInflation as Double, _
dFirstPayment as Double) as Double

FV2Way = FV(dYield, dYears, 0, PV((1 + dYield) / (1 + dInflation) - 1, dYears, dFirstPayment, 0, 1))

End Function```
Note: I used double for all arguments here because if you lookup the FV & PV functions that is what they expect so why not start off giving them what they want so they don't have to convert the argument values. Also notice the variable naming convention with the first (lower case) letter indicating the type of variable. This is for ease of reading code and understanding what type of variable is being used. Not so important in a small function like this but you can easily have one that spans quite a number of lines of code and then you don' have to look back to see how it was declared.

I hope I didn't bore you with the explanation but you did ask!
HTH

10. ## The Following User Says Thank You to RetiredGeek For This Useful Post:

mitchbvi (2013-09-08)

11. Hi RG

When I was working through college I was hardwiring plug boards on IBM 601's but nothing serious since then in the programming filed. So I greatly appreciate your time in explaining to me what I should probably have read up on before. Trying to teach your self is a slow process greatly aided by folks like you who take the trouble to help us out. I have used VBA a little more in Access and do use Option Explicit there (been told before I guess). A useful lesson thanks again.

Peter

12. Hello RG

I saw an argument some time back professing that if not dealing with whole numbers, there was no advantage to using single over double when declaring a number. Do you have any thoughts on this?

13. Don,

That all depends on the size of the numbers involved. You can easily have a number that is too large to fit into a Single in which case you will get errors or just a truncated number which is not good in either case. I used Doubles because that is what the Function Definitions published by MS called for.
Here's the MSDN Article with the details.
Here's another one. HTH

14. Numbers in cells will be passed as Double so if you're coding a UDF it makes sense to use Double.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•