|
Patch reliability is unclear. Unless you have an immediate, pressing need to install a specific patch, don't do it. |
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
Jeremy,
You hit it on the head! I don’t know how I missed it. 
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:
[noparse]=FV2Way(yield,years,inflation,firstpayment)[/noparse]
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 :cheers:
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).
34877-VBA-Options
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.
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 :cheers:
Donations from Plus members keep this site going. You can identify the people who support AskWoody by the Plus badge on their avatars.
AskWoody Plus members not only get access to all of the contents of this site -- including Susan Bradley's frequently updated Patch Watch listing -- they also receive weekly AskWoody Plus Newsletters (formerly Windows Secrets Newsletter) and AskWoody Plus Alerts, emails when there are important breaking developments.
Notifications
