asked on

Cannot find the XIRR function in the list of WorksheetFunction on VB.NET project

I'm trying to call the XIRR function on my VB.NET project but it's not listed in the available options. I have made a reference to Microsoft Excel 11.0 Object Library but it seems like I need a newer version or some other reference. If you look at my code below I can access the IRR function but not XIRR. Thanks for your help!

Imports Microsoft.Office.Interop
 
  Protected Sub btnSubmit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSubmit.Click
 
Dim oXL As New Excel.Application
 
Dim result As Double = oXL.WorksheetFunction.Irr(Cash, 0.1)
 
End Sub

Open in new window

Dave

To access XIRR you need to enable the Analysis ToolPal addin via Tools - Addins

Regards

Dave

mcgitsupport

ASKER

Dave, I'm using Visual Studio 2005 and have Excel 2003 installed. Can you be a little more specific on how I can enable the addin? Thanks!

Dave

In Excel 2003, from the menu
Tools - Addins
and check "Analysis ToolPak" (both options)

Cheers

Dave

mcgitsupport

ASKER

On Excel I had already added the Analysis Toolpak, I'm trying to access the XIRR function in Visual Studio

Dave

the VBA option as well?

mcgitsupport

ASKER

Yes, I have them both.

Dave

Ok. In the VB Editor, go to Tools/References, and be sure there's a check mark in front of ATPVBAEN.XLS

I don't have a good feel for Visual Studio, so there may be an issue here beyond the VBA settings. . If this doesn't work then I do have a VBA user function that calculates XIRR

Cheers

Daver

mcgitsupport

ASKER

I'm using Visual Studio as my editor and I can't that reference. Any other options? How about your code?

SOLUTION

Patrick Matthews

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

Dave

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mcgitsupport

ASKER

Since I was not able to use the XIRR from excel, I used VB code.

Patrick Matthews

I object. At the very least, Dave should get the answer for having provided two avenues of attack (even if the referencing to the Analysis Toolpak did not work, the code for the IRR UDF Dave linked to could have been adapted).

It would be nice, but not necessary, if I got a share for my general warning regarding the unreliability of IRR under any circumstances.

Patrick