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