Solved

Pb with calling Excel Function XIRR from VB

Posted on 2003-12-12
3
919 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 9

Expert Comment

by:Dang123
ID: 9928410
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
ID: 9928670
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
ID: 9928718
Actually, an actual VB project that calculate XIRR is here....

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

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
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…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

718 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