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