Calling the "Duration" function in excel to be used in VBA?

[juju]

Hi Everyone,

I am very new to writing macros and have come acrossed this problem when I am trying to finish off my assignment. We are not allowed to use UserForms, so I've selected to use inputboxes. My problem lies in calling out the duration function from excel..whenever that line of code is reached, an error message saying "Object doesnt support this property of method" comes out. Can you please help me?

Option Explicit

Sub ValidateInputs()
Dim SettlementDate As Variant
Dim MaturityDate As Variant
Dim CouponRate As Double
Dim Yield As Double
Dim CheckDate As Boolean
Dim test As Boolean
Dim Frequency As Integer
Dim BondDuration As Double
Start:
Do
'Get the settlement date of the bond
SettlementDate = Application.InputBox("Please enter the date when you acquired " & _
" the bond in the following format, 'YYYY,MM,DD', e.g 2006,12,30", _
"Settlement date of the bond", , , , , 2)
Debug.Print SettlementDate
If SettlementDate = IsDate(SettlementDate) Then
test = True
Debug.Print SettlementDate
Else
MsgBox "Please enter the settlement date in an appropriate format", vbCritical, "Warning"
test = False
End If
Loop Until test
Do
'Get the maturity date of the bond
MaturityDate = Application.InputBox("Please enter the maturity date of the bond in " & _
"the following format, 'YYYY,MM,DD', e.g 2006,12,30", _
"Maturity date of the bond", , , , , 2)
Debug.Print MaturityDate
If MaturityDate = IsDate(MaturityDate) Then
test = True
Debug.Print MaturityDate
Else
MsgBox "Please enter maturity date in an appropriate format, e.g '2005,12,30'", _
vbCritical, "Warning"
test = False
End If
Loop Until test
Do
' Check if maturity date is later than settlement date
If DateDiff("d", SettlementDate, MaturityDate) <= 0 Then
test = False
MsgBox "Maturity date must be later than the Settlement Date", vbCritical, "Warning"
Debug.Print DateDiff("d", SettlementDate, MaturityDate)
GoTo Start
Else
test = True
Debug.Print DateDiff("d", SettlementDate, MaturityDate)
End If
Loop Until test
Do
'Get the coupon rate of the bond
CouponRate = Application.InputBox("Please enter the coupon rate of the bond in its " & _
"per annual percentage term, e.g enter 8 if the coupon rate is 8%", _
"Coupon Rate of the bond", , , , , 2)
If CouponRate > 0 Then
test = True
Debug.Print CouponRate
Else
MsgBox "Coupon Rate needs to be positive'", vbCritical, "Warning"
test = False
End If
Loop Until test
Do
'Get the annual yield of the bond
Yield = Application.InputBox("Please enter the annual yield of the bond in its per " & _
"annual percentage term, e.g enter 8 if the coupon rate is 8%", _
"Annual yield of the bond", , , , , 1)
If Yield > 0 Then
test = True
Debug.Print Yield
Else
MsgBox "Yield needs to be positive'", vbCritical, "Warning"
test = False
End If
Loop Until test
Do
'Get the frequency of coupon payments per year
Frequency = Application.InputBox("Please enter the frequency of the coupon payments", _
"Frequency of the coupon payments", , , , , 1)
If Frequency > 0 And 0 Or 1 Or 2 Or 4 Then
test = True
Debug.Print Frequency
Else
MsgBox "Frequency of coupon payments needs to be 0 or 1 or 2 or 4", vbCritical, "Warning"
test = False
End If
Loop Until test
' Calls the duration function in-build in Excel to calculate the duration of the bond
' Basis is set in European format since this program is designed for use in Australia
BondDuration = Application.Duration(SettlementDate, MaturityDate, CouponRate, Yield, Frequency, 4)
MsgBox "BondDuration", vbOKOnly, "Bond Duration"
Debug.Print BondDuration '(SettlementDate, MaturityDate, CouponRate, Yield, Frequency, 4)
End Sub

Thank you!

[mdmackillop]

Hi,
Welcome to VBAX.
Duration is not listed as one of the worksheet functions available to VBA
Regards
MD

[Andy Pope]

But you can use it if you load the ATP VBA addin.

' need to use the Analysis pak VBA addin
' include reference to ATPVBAEN.XLA via VBE Tools > References
BondDuration = [atpvbaen.xls].Duration(SettlementDate, MaturityDate, CouponRate, Yield, Frequency, 4)

[juju]

Thank you!