Run XIRR Function in VBA

[ChairmanMetal]

If anyone can assist with this problem, I will be very grateful. I am unable to get the XIRR function to run from an Access report. Here are the particulars:

Dim objExcel As Excel.Application (did this, of course)

When the report is opened, this code executes without error:

​

Set objExcel = New Excel.Application

​

objExcel.RegisterXLL objExcel.Application.LibraryPath & "\ANALYSIS\ANALYS32.XLL"

​

objExcel.RegisterXLL objExcel.Application.LibraryPath & "\ANALYSIS\ATPVBAEN.XLAM"

All of these attempts produce errors:

CashIRR = objExcel.Run("XIrr", CashI, DateI) ' Using Month-Begin Dates
CashIRR = objExcel.Run("XIrr", CashI, DateI) ' Using Month-End Dates
CashIRR = objExcel.WorksheetFunction.IRR(CashI) ' Using Month-End Dates
CashIRR = objExcel.WorksheetFunction.Xirr(CashI, DateI) ' Using Month-Begin Dates
CashIRR = objExcel.WorksheetFunction.Xirr(CashI, DateI) ' Using Month-End Dates

CashI is an array of type Double amounts
DateI is an array of type Date variables, populated using the DateSerial function

All other XIRR rules about the first amount being negative, all dates being later than the first date, the number of amounts being equal to the number of dates, etc., etc., are observed in the arrays.

References are set in Access to the Excel 14.0 object library, and the VBA Extensibility Library

Excel is installed on the platform where Access is running. Analysis TookPak and Analysis TookPak VBA are registered in Excel. Macros are enabled. Library and Access database locations are trusted.

CashIRR = objExcel.WorksheetFunction.Xirr(CashI, DateI) produces error:

​

1004 - Unable to get the Xirr property of the WorksheetFunction class

CashIRR = objExcel.Run("XIrr", CashI, DateI) produces error:

​

1004 - Cannot run the macro 'XIrr'. The macro may not be available in this workbook or all macros may be disabled.

 

[gemma-the-husky]

i don't do stuff like this, but are the variables in the excel functions supposed to be named ranges on the excel sheet, or variables in your access database. I suspect it needs to be the former. maybe.

 

[ChairmanMetal]

You are right. Thanks very much.