 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Programming / March 2006Call PRICE function via VBA
I'm having problems calling the Excel Price (bond) function within VBA. I'm trying to build a manual yield function that needs to call excel' PRICE function. But i get #VALUE Application.Price(...) does not work. Application.WorksheetFunction.Price(...) does not work either. _below_is_my_code:_ Function YIELDMANUAL(vSettlement, vMaturity, vCoupon, vPrice vRedemption, iFrequency) Dim vGuess As Variant Dim vGap As Variant vGuess = vCoupon.Value 'set Guess rate to coupon vGap = Application.WorksheetFunction.Price(vSettlement, vMaturity vCoupon, vGuess, vRedemption, iFrequency) - vPrice '---------- If vGap > 0 Then Do vGuess = vGuess + 0.000001 vGap = Application.WorksheetFunction.Price(vSettlement, vMaturity vCoupon, vGuess, vRedemption, iFrequency) - vPrice Loop While vGap > 0 Do vGuess = vGuess - 0.0000001 vGap = Application.WorksheetFunction.Price(vSettlement, vMaturity vCoupon, vGuess, vRedemption, iFrequency) - vPrice Loop While vGap < 0 Do vGuess = vGuess + 0.00000001 vGap = Application.WorksheetFunction.Price(vSettlement, vMaturity vCoupon, vGuess, vRedemption, iFrequency) - vPrice Loop While vGap > 0 Do vGuess = vGuess - 0.000000001 vGap = Application.WorksheetFunction.Price(vSettlement, vMaturity vCoupon, vGuess, vRedemption, iFrequency) - vPrice Loop While vGap < 0 Do vGuess = vGuess + 0.0000000001 vGap = Application.WorksheetFunction.Price(vSettlement, vMaturity vCoupon, vGuess, vRedemption, iFrequency) - vPrice Loop While vGap > 0 Do vGuess = vGuess - 0.00000000001 vGap = Application.WorksheetFunction.Price(vSettlement, vMaturity vCoupon, vGuess, vRedemption, iFrequency) - vPrice Loop While vGap < 0 '---------- ElseIf vGap < 0 Then Do vGuess = vGuess - 0.000001 vGap = Application.WorksheetFunction.Price(vSettlement, vMaturity vCoupon, vGuess, vRedemption, iFrequency) - vPrice Loop While vGap < 0 Do vGuess = vGuess + 0.0000001 vGap = Application.WorksheetFunction.Price(vSettlement, vMaturity vCoupon, vGuess, vRedemption, iFrequency) - vPrice Loop While vGap > 0 Do vGuess = vGuess - 0.00000001 vGap = Application.WorksheetFunction.Price(vSettlement, vMaturity vCoupon, vGuess, vRedemption, iFrequency) - vPrice Loop While vGap < 0 Do vGuess = vGuess + 0.000000001 vGap = Application.WorksheetFunction.Price(vSettlement, vMaturity vCoupon, vGuess, vRedemption, iFrequency) - vPrice Loop While vGap > 0 Do vGuess = vGuess - 0.0000000001 vGap = Application.WorksheetFunction.Price(vSettlement, vMaturity vCoupon, vGuess, vRedemption, iFrequency) - vPrice Loop While vGap < 0 Do vGuess = vGuess + 0.00000000001 vGap = Application.WorksheetFunction.Price(vSettlement, vMaturity vCoupon, vGuess, vRedemption, iFrequency) - vPrice Loop While vGap > 0 '---------- End If YIELDMANUAL = vGuess End Functio Hi Jomni, I am just wondering if, in Tools Add Ins, you have ticked off both Analysis ToolPack, and Analysis TooPack - VBA ... HTH Cheers Carim Yes, the add-in is installed. I can also call the PRICE function in the worksheet cell. Must be something wrong in my code Jomni, May be I did not express myself clearly enough ... In addition to the standard addin, there is another addin, dedicated to VBA named "Analysis TooPack - VBA " which needs to be activated for VBA to be operational ... HTH Carim I have that installed as well :confused jomni - 1. Load Analysis Tookpak - VBA 2. In your VBA project, use Tools | References to create a reference to the ToolPak file. 3. In your project, call it like a VBA function (without Application and without Application.WorksheetFunction). - Mike www.mikemiddleton.com Mike, Great! That surely fixed my problem. I soon found out that my code i quite cumbersome so I chaged it to use the *Newton-Raphson* method. The resulting code is shorter and with less iteratons. :cool: Function YIELD_MANUAL(vSettlement, vMaturity, vCoupon, vPrice vRedemption, iFrequency) Dim vGuess As Variant Dim vGap As Variant Dim vDerivative As Variant 'Set vGuess to coupon vGuess = vCoupon 'I used For Next so that it stops after 99 tries (but it shouldn't b that long in theory) For i = 1 To 99 'vPrice - Price (vGuess) - vPrice vGap = vPrice - Price(vSettlement, vMaturity, vCoupon, vGuess vRedemption, iFrequency) '(Price (vGuess) - Price (vGuess + .001))/ .001 vDerivative = (Price(vSettlement, vMaturity, vCoupon, vGuess vRedemption, iFrequency) _ - Price(vSettlement, vMaturity, vCoupon, vGuess + 0.001 vRedemption, iFrequency)) / 0.001 'This is the Newton-Raphson formula vGuess = vGuess - (vGap / vDerivative) 'If resulting vGap is already 0, then end the function 'If vGap = 0 Then GoTo 10 Next i 10 YIELD_MANUAL = vGuess End Functio > For i = 1 To 99 As an alternative to a fixed number of loops, here's one idea: Do vGap = vPrice - Price(vSettlement, vMaturity, vCoupon, vGuess, vRedemption, iFrequency) vDerivative = Price(vSettlement, vMaturity, vCoupon, vGuess, vRedemption, iFrequency) vDerivative = (vDerivative - Price(vSettlement, vMaturity, vCoupon, vGuess + 0.001, vRedemption, iFrequency)) / 0.001 vGuess = vGuess - (vGap / vDerivative) Loop While Abs(vGap) > 0.0000000000001 YIELD_MANUAL = vGuess  SignatureHTH. :>) Dana DeLouis Windows XP, Office 2003 > Mike, > [quoted text clipped - 33 lines] > > End Function |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.