I am pulling the data from qryIRR into a Report which is sorted by Investment Name. I have the text box "Result" in the grouping footer and put the control source of text box 'Result" as "RunXIRR()". Then I have the following code in a Module. When it gets to the line - Result = ObjExcel.Run("XIRR", ArrAmounts, ArrDates) I get an error message saying that "The Macro XIRR cannot be found".
Please help me fix this
Public Function RunXIRR()
Dim ArrDates() As Long
Dim ArrAmounts() As Long
Dim Result As Double
Dim XRecordCount As Long
Dim intCounter As Long
Dim ObjExcel As Object
Set ObjExcel = CreateObject("Excel.Application")
Dim rst As ADODB.Recordset
ObjExcel.RegisterXLL ("C:\Program Files\Microsoft Office\Office\Library\ANALYSIS\ANALYS32.XLL")
Set db = CurrentDb
Set rst = New ADODB.Recordset
rst.Open "SELECT * from qryIRR", CurrentProject.Connection, adOpenStatic
XRecordCount = rst.RecordCount
If XRecordCount > 1 Then
ReDim ArrDates(1 To XRecordCount) As Long
ReDim ArrAmounts(1 To XRecordCount) As Long
For intCounter = 1 To XRecordCount
ArrDates(intCounter) = CLng(CDate(rst!TransactionDate))
ArrAmounts(intCounter) = rst!NetCashFlow
rst.MoveNext
Next intCounter
Result = ObjExcel.Run("XIRR", ArrAmounts, ArrDates)
End If
ObjExcel.Quit
Set ObjExcel = Nothing
End Function