I'm trying to call this function:

Function XIRR(values As IXRangeEnum, dates As IXRangeEnum, [guess As Double = 0.100000001490116])

within VB (not VBA). I need to convert two vb arrays to IXRangeEnum and somehow pass them into this function.

Using this code such as:

Function getXIRR() As Double

Dim obj As OCATP
Dim arrDates(3) As Date
Dim arrAmount(3) As Double
arrDates(0) = #5/1/2000#
arrDates(1) = #6/1/2000#
arrDates(2) = #7/1/2000#
arrAmount(0) = -100
arrAmount(1) = 100
arrAmount(2) = 200
Set obj = New OCATP
getXIRR = obj.XIRR(arrAmount, arrDates)

End Function

If you are using the built in function XIRR, it will either work with arrays
or it won't. If it won't, then you would have to write your array to a
range. In the case of XIRR, it does work with arrays, so you should need no
conversion - just pass in the arrays.


I am not sure what OCATP is intended to be, but this worked:

Sub Mytest()
Debug.Print getXIRR
End Sub

Function getXIRR() As Double

' Dim obj As OCATP
Dim arrDates(3) As Date
Dim arrAmount(3) As Double
arrDates(0) = #5/1/2000#
arrDates(1) = #6/1/2000#
arrDates(2) = #7/1/2000#
arrAmount(0) = -100
arrAmount(1) = 100
arrAmount(2) = 200
' Set obj = New OCATP
getXIRR = Application.Run("ATPVBAEN.XLA!XIRR", arrAmount, arrDates)

End Function

You would need to change the Application to a reference to the Excel
application and load the analysis toolpak VBA in the instance of the Excel
application (addins are not loaded automatically when you open Excel as an
Automation server).


The answer was: 1658.94447021484

--
Regards,
Tom Ogilvy

"damon.f" wrote in message
...
I'm trying to call this function:

Function XIRR(values As IXRangeEnum, dates As IXRangeEnum, [guess As
Double = 0.100000001490116])

within VB (not VBA). I need to convert two vb arrays to IXRangeEnum and
somehow pass them into this function.

Using this code such as:

Function getXIRR() As Double

Dim obj As OCATP
Dim arrDates(3) As Date
Dim arrAmount(3) As Double
arrDates(0) = #5/1/2000#
arrDates(1) = #6/1/2000#
arrDates(2) = #7/1/2000#
arrAmount(0) = -100
arrAmount(1) = 100
arrAmount(2) = 200
Set obj = New OCATP
getXIRR = obj.XIRR(arrAmount, arrDates)

End Function

Thanks for your example but I was hopeing to do this without OLE automation. I've simply included the mscowcf.dll in my VB project and use the function directly. Can you suggest how this might be achieved

Thanks