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's 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 Function

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

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