Passing an array into the IRR function in VBA. Keep getting the error "Compile error: Type mismatch: array or user-defined type expected."

Question

I'm calculating the IRR of an array in VBA. Here's my ugly code:

Dim iRRArray() As Variant
Erase iRRArray()
ReDim iRRArray(garageLife)

Dim i As Integer
i = 0

Do While i <= garageLife
    Dim difference As Double
    difference = (Worksheets("IRR").Cells(k + 1, i).Value - Worksheets("IRR").Cells(defender, i).Value) * -1
    
    iRRArray(i) = difference

    i = i + 1
Loop

Dim iRRValueTemp As Variant
iRRValueTemp = IRR(iRRArray(), 0.1)

(The mess that gets loaded into the "difference" variable just loads values into the array based on cells in the worksheet.)

This particular set of data works JUST FINE in the IRR function when I use the function in the spreadsheet. When I try to run the macro, though, it gives me the ol' "Compile error: Type mismatch: array or user-defined type expected" on the line containing the IRR function. This mystifies me, as what I'm loading into the IRR is clearly an array...

What I've tried:

1. Various combinations of Dim and ReDim iRRArray (same error)
2. iRRArray() vs. iRRArray, both in the IRR function and in the definition (same error)
3. Passing an entirely different array into the IRR function (same error)

And yes, the array does contain at least one sign change.

Thanks in advance!

Answer 1

Your call iRRValueTemp = IRR(iRRArray, 0.1) calls the Office-VBA IRR function. But that needs an array of Double instead an array of Variant.

Application.IRR or WorksheetFunction.IRR can use an array of Variant.

Sub test()

 Dim iRRArray() As Double
 'Dim iRRArray() As Variant
 ReDim iRRArray(9)

 iRRArray(0) = -3
 Dim i As Long
 For i = 1 To 9
  iRRArray(i) = 1 + i / 10
 Next

 Dim iRRValueTemp As Variant
 iRRValueTemp = IRR(iRRArray, 0.1) 'will only work using Dim iRRArray() As Double
 'iRRValueTemp = WorksheetFunction.IRR(iRRArray, 0.1)
 'iRRValueTemp = Application.IRR(iRRArray, 0.1)

End Sub