UDF with IRR

[stephen.h.dow]

Trying get a simple function to work - want to calculate the IRR of a
data set using VBA and not a formula. Keep getting an error: Here's
what I have:

Data:
-1000
300
300
300
300

Code:
Function testi(ArrayIn As Double)

ReDim ArrayIn(5) As Double

testi = IRR(ArrayIn(), 0.1)

End Function

Any ideas on how I can get this to work would be greatly appreciated.

Thanks.

 

[Tom Ogilvy]

I can get IRR to work if I create a reference to it. however, using
application.Run which works with most Analysis toolpak functions doesn't seem
to work with IRR (at least I couldn't get it to).

Here is a workaround:

Sub AAtest1()
Dim s As String
Dim v(0 To 4) As Double
v(0) = -1000
v(1) = 300
v(2) = 300
v(3) = 300
v(4) = 300
s = "{"

For i = LBound(v) To UBound(v)
s = s & v(i) & ","
Next
s = Left(s, Len(s) - 1) & "}"
MsgBox testi(s)
End Sub


Function testi(s As String)
testi = Evaluate("IRR(" & s & ", 0.1)")
End Function

This will be limited as to the number of payments you can include since
Evaluate chokes for character strings longer than about 255.

Otherwise, create the reference.

 

[stephen.h.dow]

Here's a solution that I just stumbled upon that seems to work:

Public Function testi(ArrayIn As Range) As Double

testi = Application.WorksheetFunction.IRR(ArrayIn(), 0.1)

End Function

 

[Luis Fernando Ortiz M.]

This work to

Public Function testi(ArrayIn As Range) As Double

testi= Application.IRR(ArrayIN, 0.1)

End Function

Regards

Luis Fernando Ortiz Maldonado