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

[korokke]

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!

 

[Tom Ogilvy]

Duration is a function provided by the analysis toolpak addin. So you don't
call it using application or worksheetfunction.

You can use

BondDuration = _
Application.Run("ATPVBAEN.XLA!DURATION", _
SettlementDate, MaturityDate, _
CouponRate / 100, Yield / 100, Frequency, 4)

As an example, using the values in the Excel help for the duration function

Sub checkit()
Dim SettlementDate, MaturityDate, _
CouponRate, Yield, Frequency
SettlementDate = DateValue("January 1, 2008")
MaturityDate = DateValue("January 1, 2016")
CouponRate = 8
Yield = 9#
Frequency = 2
BondDuration = _
Application.Run("ATPVBAEN.XLA!DURATION", _
SettlementDate, MaturityDate, _
CouponRate / 100, Yield / 100, Frequency, 4)
Debug.Print BondDuration
End Sub

returns
5.99377495554519

which agrees with the advertised results in the help.

 

[korokke]

Thanks everyone!