Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Call 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 -- jomn ----------------------------------------------------------------------- jomni's Profile: http://www.excelforum.com/member.php...nfo&userid=774 View this thread: http://www.excelforum.com/showthread.php?threadid=52467 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Function for price qoutation help | Excel Worksheet Functions | |||
PRICE function, Pls help | Excel Worksheet Functions | |||
Reverse PRICE function | Excel Worksheet Functions | |||
Price Function | Excel Worksheet Functions | |||
Price Function Error? | Excel Worksheet Functions |