Solved

Pb with calling Excel Function XIRR from VB

Posted on 2003-12-12
3
907 Views
Last Modified: 2012-05-04
Hi,

I'm unsuccessfully trying for a few days to call a XIRR EXCEL function from visual-basic.

In a VB process, I need to calculate the Internal rate of return for nonperiodic cash flow.
As such a financial function is not available in vb, I'd like to use the Excell XIRR function.

I found 2 ways for calling such a function from VB, but none worked :-(  :
Set XlAppl = createobject("Excel.application")
XlAppl.workbooks.open xlappl.librarypath & "\Analysis\ATPVBAEN.XLA"
XlAppl.Workbooks("ATPVBAEN.XLA").runautomacros (xlAutoOpen)

1st solution :
(the following values are actually coming from a recordset)
wResult = XlAppl.Run("ATPVBAEN.XLA!XIRR","-10000,2750,2750,2750","12/31/1998,07/31/1999,12/31/2000,07/31/2001",0.1)
This gives wResult = 'Error 2015' !!
I'm not sure of the format to use for dates and for separators but none of my trials was successfull.

2nd solution :
XlAppl.Workbooks.add
XlAppl.Cells(1,1).value=-10000
XlAppl.Cells(1,2).value= "12/31/1998"
XlAppl.Cells(2,2).value=2750
XlAppl.Cells(2,2).value= "07/31/1999"
XlAppl.Cells(3,1).value=2750
XlAppl.Cells(3,2).value= "12/31/2000"
XlAppl.Cells(4,1).value=2750
XlAppl.Cells(4,2).value= "07/31/2001"
XlAppl.Cells(1,3).value=0.1
XlAppl.Cells(1,4).formula= "=xirr(A1:A4,B1:B4,C1)"
Xlappl.calculate
wResult=XlAppl.Cells(1,4).value
This gives wResult = "Error 2036" !!

I certainly missed something...

Any suggestion should be appreciated
Thanks

Yves
0
Comment
Question by:ymiossec
  • 2
3 Comments
 
LVL 9

Expert Comment

by:Dang123
Comment Utility
Yves,
    In help for the XIRR function, it says: "If this function is not available, run the Setup program to install the Analysis ToolPak. After you install the Analysis ToolPak, you must enable it by using the Add-Ins command on the Tools menu." Is the ToolPak enabled on your machine?

    Also, there is a typo in your second sample.
   
XlAppl.Cells(2,2).value=2750
XlAppl.Cells(2,2).value= "07/31/1999"

should be

XlAppl.Cells(1,2).value=2750
XlAppl.Cells(2,2).value= "07/31/1999"


Dang123
0
 
LVL 2

Expert Comment

by:rhys_kirk
Comment Utility
Check this out...

VB Version of the XIRR function....

http://www.entisoft.com/ESTools/MathFinancial_XIRRSample.HTML
0
 
LVL 2

Accepted Solution

by:
rhys_kirk earned 500 total points
Comment Utility
Actually, an actual VB project that calculate XIRR is here....

http://www.programmersheaven.com/zone1/cat373/23046.htm
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now