How to use IRR() function in VBA

Question

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.

Answer 1

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.

    Dim a As Variant, b As Double

    a = Array(-6, 4, 5)

    b = Application.IRR(a, 0.1)
    Debug.Print b

    b = WorksheetFunction.IRR(a, 0.1)
    Debug.Print b

    b = Application.WorksheetFunction.IRR(a, 0.1)
    Debug.Print b

Results from the VBE's Immediate window ([ctrl]+G)

 0.305158649140883 
 0.305158649140883 
 0.305158649140883 

Answer 2

No, a is NOT an array; it is "a variant containing an array". VBA is NOT a C-type language, and doesn't have the initializers that those do. Try this code:

Dim a(0 To 2) As Double
a(0) = -3#
a(1) = 4#
a(2) = 5#

Dim v As Double: v = irr(a(), 0.1)