I'm trying to include the Excel MDuration function into VBA code in an Access Module.

[hamish4444]

I'm trying to include the Excel MDuration function into VBA code in an Access Module. Is this possible?

My goal is to calculate the Modified Duration of a bond using the key inputs and have the duration calculation sent to a table.

The code works up until the line "Duration = "=MDURATION(SettlementDate,MaturityDate,Coupon,Yie ld,Frequency)"

Where I'm I going wrong or is this simply not possible?

My code is as follows.

 
Public Function CalcDuration()
 
Dim SettlementDate As Date
Dim MaturityDate As Date
Dim Coupon As Double
Dim Yield As Double
Dim Duration As Double
Dim Frequency As Double
 
 
SettlementDate = InputBox("Enter Settlement Date ", "Enter Value")
MaturityDate = InputBox("Enter Maturity Date ", "Enter Value")
Coupon = InputBox("Enter Coupon ", "Enter Value")
Yield = InputBox("Enter Yield ", "Enter Value")
Frequency = InputBox("Enter Frequency ", "Enter Value")
Duration = "=MDURATION(SettlementDate,MaturityDate,Coupon,Yield,Frequency)"
 
 
CurrentDb.Execute "INSERT INTO Duration" _
    & "(SettlementDate, Duration) VALUES " _
    & "(#" & SettlementDate & "#, " & Duration & ")"
 
 
End Function
 
 

[jforbes]

I don't do what your attempting to do, but this seems to work:

Duration = Excel.WorksheetFunction.MDuration(SettlementDate,MaturityDate,Coupon,Yield,Frequency)

I have a feeling that it might be better to create an instance of Excel in a Variable, use that variable instead of calling Excel directly, and then set the Variable to Nothing so that Excel gets closed correctly.

[ADezii]

The following Code is fully operational, I am just not sure that it is producing the desired results. That's your area of expertise (LOL).

Dim dteSettlementDate As Date
Dim dteMaturityDate As Date
Dim sngCouponRate As Single
Dim sngAnnualYield As Single
Dim bytFrequency As Byte
 
dteSettlementDate = #4/1/2005#
dteMaturityDate = #3/31/2025#
sngCouponRate = 0.08
sngAnnualYield = 0.04
bytFrequency = 4
 
 
Dim appExcel As Excel.Application
 
Set appExcel = New Excel.Application
 
Debug.Print "Duration (Yrs.): " & appExcel.WorksheetFunction.MDuration(dteSettlementDate, dteMaturityDate, sngCouponRate, sngAnnualYield, bytFrequency)
 
appExcel.Quit
Set appExcel = Nothing

Duration (Yrs.): 11.948498418378

[hamish4444]

Thanks for the help #jforbes. It doesn't seem to work for me. It's saying "Run-time error '424': Object required".

[hamish4444]

Thanks for the help #ADezii. Also having issues here: it says "Compile error: User-defined type not defined" and it stops at the "Dim appexcel As Excel.Application" line.

I think I've found a solution that seems to be working. See below.

[hamish4444]

 
Public Function CalcDuration()
 
    Dim x1 As Object
    Set x1 = CreateObject("Excel.Application")
 
    Dim SettlementDate As Date
    Dim MaturityDate As Date
    Dim Coupon As Double
    Dim Yield As Double
    Dim Duration As Double
    Dim Frequency As Double
 
 
    SettlementDate = InputBox("Enter Settlement Date ", "Enter Value")
    MaturityDate = InputBox("Enter Maturity Date ", "Enter Value")
    Coupon = InputBox("Enter Coupon ", "Enter Value")
    Yield = InputBox("Enter Yield ", "Enter Value")
    Frequency = InputBox("Enter Frequency ", "Enter Value")
    Duration = x1.MDURATION(SettlementDate, MaturityDate, Coupon, Yield, Frequency)
 
 
    CurrentDb.Execute "INSERT INTO Duration" _
        & "(SettlementDate, Duration) VALUES " _
        & "(#" & SettlementDate & "#, " & Duration & ")"
 
 
 
End Function
 
 

[ADezii]

"Compile error: User-defined type not defined" and it stops at the "Dim appexcel As Excel.Application"

The reason for this Error is because I use Early Binding while you use Late Binding. If you set a Reference to the Microsoft Excel XX.X Object Library, this Error will not appear.

[hamish4444]

Ah ok great, big thanks #ADezii

[ADezii]

You are quite welcome.