XL2007 promoted the XIRR function, previously part of the Analysis Toolpak, to Worksheetfunction status. This implies that using such function no longer needs the add-ins (ATP and ATP VBA) nor a reference to atpvbaen.xls
So far, so good. But when migrating an application from xl2003 to xl2007, I had to call the function explicitally:
2003: dblResult = XIRR (aValue,aDate)
2007: dblResult = Application.WorksheetFunction.XIRR(aValue,Adate)
A much more serious problem was that the function apparently no longer accepts direct input from VBA. The function performs as expected when the arguments are located in a worksheet range. When the arguments are VBA array Variants the program bombs with a 1004 error: Unable to get the XIRR property of the worksheetfunction class. Although Intellisense clearly shows this function during coding.
The MSDN page (http://msdn2.microsoft.com/en-us/library/bb224771.aspx) that describes the function does allow variants as arguments.
Did I missed something?
I included a Zipped folder that contains an Access Database (DBXIRREN - saved in 2003 compatibility mode) and a Workbook (XIRR2007). The latter contains three worksheets:
SCRATCH: Illustates the use of the XIRR function as an Excel Function and indirectly as an encapsulated VBA function. The latter as a proof of concept.
RANGE: The data is pulled from the data base into a workheetrange and then manipulated by an encapsulated XIRR
DYNAMIC: The one that should work, but doesn't. Data again is pulled from the database and passed directly to the function. This works in 2003. Why do I get the 1004 now?
The workbook does contains macros that encapsulate the XIRR function. These macros are called during Workbook_open event. You can adapt the path to the database by modifying cel $D$3 in worksheet SCRATCH.
Any clue that gives insight into the problem is most welcome.
PS: I didn't check out any other "promoted" ATP function.
Bookmarks