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

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

--
jomn
-----------------------------------------------------------------------
jomni's Profile: http://www.excelforum.com/member.php...nfo&userid=774
View this thread: http://www.excelforum.com/showthread.php?threadid=52467

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

--
jomn
-----------------------------------------------------------------------
jomni's Profile: http://www.excelforum.com/member.php...nfo&userid=774
View this thread: http://www.excelforum.com/showthread.php?threadid=52467

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