ymiossec
asked on
Pb with calling Excel Function XIRR from VB
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.applic
XlAppl.workbooks.open xlappl.librarypath & "\Analysis\ATPVBAEN.XLA"
XlAppl.Workbooks("ATPVBAEN
1st solution :
(the following values are actually coming from a recordset)
wResult = XlAppl.Run("ATPVBAEN.XLA!X
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=-1
XlAppl.Cells(1,2).value= "12/31/1998"
XlAppl.Cells(2,2).value=27
XlAppl.Cells(2,2).value= "07/31/1999"
XlAppl.Cells(3,1).value=27
XlAppl.Cells(3,2).value= "12/31/2000"
XlAppl.Cells(4,1).value=27
XlAppl.Cells(4,2).value= "07/31/2001"
XlAppl.Cells(1,3).value=0.
XlAppl.Cells(1,4).formula=
Xlappl.calculate
wResult=XlAppl.Cells(1,4).
This gives wResult = "Error 2036" !!
I certainly missed something...
Any suggestion should be appreciated
Thanks
Yves
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.applic
XlAppl.workbooks.open xlappl.librarypath & "\Analysis\ATPVBAEN.XLA"
XlAppl.Workbooks("ATPVBAEN
1st solution :
(the following values are actually coming from a recordset)
wResult = XlAppl.Run("ATPVBAEN.XLA!X
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=-1
XlAppl.Cells(1,2).value= "12/31/1998"
XlAppl.Cells(2,2).value=27
XlAppl.Cells(2,2).value= "07/31/1999"
XlAppl.Cells(3,1).value=27
XlAppl.Cells(3,2).value= "12/31/2000"
XlAppl.Cells(4,1).value=27
XlAppl.Cells(4,2).value= "07/31/2001"
XlAppl.Cells(1,3).value=0.
XlAppl.Cells(1,4).formula=
Xlappl.calculate
wResult=XlAppl.Cells(1,4).
This gives wResult = "Error 2036" !!
I certainly missed something...
Any suggestion should be appreciated
Thanks
Yves
Check this out...
VB Version of the XIRR function....
http://www.entisoft.com/ESTools/MathFinancial_XIRRSample.HTML
VB Version of the XIRR function....
http://www.entisoft.com/ESTools/MathFinancial_XIRRSample.HTML
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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=27
XlAppl.Cells(2,2).value= "07/31/1999"
should be
XlAppl.Cells(1,2).value=27
XlAppl.Cells(2,2).value= "07/31/1999"
Dang123