Results 1 to 11 of 11
  1. #1
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    Vail, Colorado, USA
    Posts
    194
    Thanks
    36
    Thanked 0 Times in 0 Posts

    Exclamation 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. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    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
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,191
    Thanks
    48
    Thanked 986 Times in 916 Posts
    Maybe the "ActiveCell=" statement needs to have an active cell to work.

    cheers, Paul

  4. #4
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Redditch, Worcestershire, England
    Posts
    233
    Thanks
    0
    Thanked 22 Times in 21 Posts
    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. #5
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    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
    Last edited by RetiredGeek; 2013-09-08 at 10:17.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

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

    mitchbvi (2013-09-08)

  8. #6
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    Vail, Colorado, USA
    Posts
    194
    Thanks
    36
    Thanked 0 Times in 0 Posts
    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. #7
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    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
    Last edited by RetiredGeek; 2013-09-08 at 14:18.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

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

    mitchbvi (2013-09-08)

  11. #8
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    Vail, Colorado, USA
    Posts
    194
    Thanks
    36
    Thanked 0 Times in 0 Posts
    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. #9
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post
    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?
    Regards
    Don

  13. #10
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    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
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  14. #11
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Numbers in cells will be passed as Double so if you're coding a UDF it makes sense to use Double.
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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