Use a Custom Excel Function in Access

[Titusman]

I am in the process of creating a database that can compute interest on an amount in my database. I have a custom worksheet function that can do this in Excel, but I can't seem to get it to work in Access. Can anyone give me an idea as to how to call a custom worksheet function such as this in an Access report? Any help at all will be appreciated.

Thanks

 

[grnzbra]

What does the function look like in Excel?

 

[MichaelRed]

but -- why 'custom'? Excel includes a 'perfect' intrinsic function (IPmt &/Or PV) which works quite well, and can be used in Ms. A. simply be adding a reference to the Excel Lib of choice. In fact, all (or at least the large majority) of Excel function are available to Ms. A. (or any vb(a) language program) simply by adding the reference (tools-->References).

But again (and again) WHY use a custom routine? what are you NOT telling 'us' about how you calculate the interest?

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[Titusman]

What I do is collect taxes. We have to use the interest rates as published by the IRS. When we bill for a tax, the billing will occur in a particular period through say 30 days from now. If the billing occurred in 1998, every interest rate that was in effect from that period until now must be applied. This custom function uses the rates as stored in a worksheet. The function works great in Excel and I thought that it would provide similar results for this program.

Does this clarify?

The function is:

Option Explicit

Function CalcID(ByVal BeginDate, ByVal EndDate, ByVal TaxDue) As Currency
Dim i%, TotalRow%, y, x%, BeginRow%, wkIntRates As Worksheet
Set wkIntRates = Worksheets("Interest_Rates&quot
If BeginDate = "" Or EndDate = "" Or TaxDue = 0 Then
CalcID = 0
Exit Function
End If
With wkIntRates
Do
i = i + 1
If BeginDate >= .Cells(i, 1) And BeginDate <= .Cells(i, 2) Then BeginRow = i
Loop Until EndDate >= .Cells(i, 1) And EndDate <= .Cells(i, 2)
TotalRow = i
ReDim cArray(BeginRow To TotalRow, 3) As Date
For i = BeginRow To TotalRow
For x = 1 To 3
If x = 3 Then
cArray(i, x) = .Cells(i, x).Value / 365
Else
cArray(i, x) = .Cells(i, x).Value
End If
Next
Next
End With
For y = BeginDate + 1 To EndDate
For i = BeginRow To TotalRow
Select Case y
Case cArray(i, 1) To cArray(i, 2)
Do
CalcID = CalcID + (TaxDue * (cArray(i, 3)))
y = y + 1
If y > EndDate Then Exit Do
Loop Until y > cArray(i, 2)
End Select
Next
Next
Set wkIntRates = Nothing
End Function