AccrIntM: "Object doesn't support...."

[PeteCresswell]

I'm getting this error when I try to invoke ACCRINTM in VBA from MS
Access.

The approach works for DAYS360, but fails for AccrIntM.

In fact, the autofill dropdown for gExcelApp.WorkSheetFunction...
includes Days360, but does not include AccrIntM or AccrInt.

The complete error message:

"Run-time error '438': Object doesn't support this property or
method."

Snip from VBA Code:
--------------------------------------------
16050 With gExcelApp
16051 .RegisterXLL .Application.LibraryPath & "\ANALYSIS
\ANALYS32.XLL"
16052 .AddIns("Analysis ToolPak").Installed = True
16053 myResult = gExcelApp.WorksheetFunction.accrintm
(myPaymentDateLast, mySettlementDate, myCouponRate, myParAmount,
myBasis)
16954 ACCRINTM_Excel = myResult
16059 End With
--------------------------------------------

Wrong library?


All the VBA Code (probably wretched excess....)
------------------------------------------
Public Function ACCRINTM_Excel( _
ByVal theBasis As Long, _
ByVal theCouponRate As Double, _
ByVal theParAmount As Double, _
ByVal thePaymentDateLast As Variant, _
ByVal theSettlementDate As Variant _
) As Double
16000 DebugStackPush mModuleName & ": ACCRINTM_Excel"
16001 On Error GoTo ACCRINTM_Excel_err

' PURPOSE: To compute the accrued interest for a fixed-rate
security
' ACCEPTS: - tblSecurity.DayCountBasisBloombergID of the security
' - Security's annual coupon rate
' - Par amount of the transaction
' - Settlement date of the transaction
' RETURNS: Dollar amount of the accrued interest
'
' NOTES: 1) The usage/fields DIFFER from the Excel help item.
' To wit: - We are feeding it LastPayment/Settle dates,
' but the help item uses Issue/Maturity dates
'
'
' SAMPLE:
' -----------------------------------------
'
' ?ACCRINTM_Excel(0,5,50000,#12/01/2008#,#04/29/2009#)
' $1,027.78

16003 Dim myResult As Variant
Dim mySettlementDate As Variant
Dim myPaymentDateLast As Variant
Dim myParAmount As Double
Dim myCouponRate As Double
Dim myBasis As Long
Dim errorCount As Long

16010 If Not IsDate(thePaymentDateLast) Then
16011 errorCount = errorCount + 1
16012 BugAlert True, "Illegal LastPayment Date='" &
thePaymentDateLast & "'."
16019 End If

16020 If Not IsDate(theSettlementDate) Then
16021 errorCount = errorCount + 1
16022 BugAlert True, "Illegal Settlement Date='" &
theSettlementDate & "'."
16029 End If

16030 If errorCount = 0 Then
16031 myBasis = theBasis
16032 myCouponRate = theCouponRate
16033 myParAmount = theParAmount
16034 myPaymentDateLast = thePaymentDateLast
16039 mySettlementDate = theSettlementDate

16040 If Excel_Start(gExcelApp) = True Then
16050 With gExcelApp
16051 .RegisterXLL .Application.LibraryPath & "\ANALYSIS
\ANALYS32.XLL"
16052 .AddIns("Analysis ToolPak").Installed = True
16053 myResult = gExcelApp.WorksheetFunction.accrintm
(myPaymentDateLast, mySettlementDate, myCouponRate, myParAmount,
myBasis)
16954 ACCRINTM_Excel = myResult
16059 End With
16990 Else
16991 ACCRINTM_Excel = "na"
16992 End If
16999 End If

ACCRINTM_Excel_xit:
DebugStackPop
On Error Resume Next
Exit Function

ACCRINTM_Excel_err:
BugAlert True, "Basis='" & theBasis & ", CouponRate='" &
theCouponRate & "', ParAmount='" & theParAmount & ",
PaymentDateLast='" & thePaymentDateLast & ", SettlementDate='" &
theSettlementDate & "'."
Resume ACCRINTM_Excel_xit
End Function
------------------------------------------

 

[PeteCresswell]

FWIW, it came down to this:

AccrInt does not exist for this:
myResult = gExcelApp.WorksheetFunction.AccrIntM(myPaymentDateLast,
mySettlementDate, myCouponRate, myParAmount, myBasis)

But this does the job:
myResult = gExcelApp.Run("AccrIntM", myPaymentDateLast,
mySettlementDate,
myCouponRate, myParAmount, myBasis)