I am trying to use the IRR function in VBA. IRR() works well in Excel. As I scripted it in VBA as follows,
a = Array(3, 4, 5)
b = IRR(a, 0.1)
it complained of "Compile error: Type mismatch: array or user-defined type expected". What dumbfounds me is a IS an array. What is wrong?
I rewrote the function according to Pieter Geerkens as follows:
option base 1
Function trial()
Dim a(2) As Double, b As Double
a(1) = 1.2
a(2) = 3.4
b = IRR(a(), 0.1)
End Function
I get the error message: Run-time error '5': Invalid procedure call or argument.
You need to apply either the Excel Application object, the WorksheetFunction object or both. Additionally, the first element in the array is expected to be negative.
Results from the VBE's Immediate window ([ctrl]+G)