1

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.

Hans
  • 1,269
  • 3
  • 19
  • 38
  • You did NOT use my code - you changed the explicit array bounds `0 to 2` with ***three*** defined values to implicit array bounds `0 to 2` with only ***two*** defined values. You must give a value to a(0) and must have a sign change in the value list. – Pieter Geerkens Jan 27 '16 at 01:14
  • 1
    @PieterGeerkens: I neglected to add "option base 1" at the top previously. I have now added that. I do not like initializing an array at 0 but prefer to start it at 1. Now the code is essentially yours. Do you agree? – Hans Jan 27 '16 at 01:54
  • 1
    You don't have a sign change in your input value array, which is required for your "*invalid procedure ... argument*" to become *valid*. And, don't use OPTION BASE 1 because you will run into all sorts of problems doing so, I've been there, done that, and learned better. – Pieter Geerkens Jan 27 '16 at 01:56
  • I see, regarding the sign change. Regarding base 1, are the problems you refer to concern particularly with VBA or programming in general? I ask because I have no problem indexing sequences starting from 1 in Matlab. What kind of problem would I run into? – Hans Jan 27 '16 at 02:15

2 Answers2

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)
Pieter Geerkens
  • 11,775
  • 2
  • 32
  • 52
2

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
  • 2
    Although the first element in the array is USUALLY negative, it is sufficient that there be at least one sign change. There are (in general) as many solutions as there are sign changes in the value list provided, which can be problematic when there is a continuing investment after the first positive return. – Pieter Geerkens Jan 27 '16 at 01:16
  • Good point @PieterGeerkens - thank you for the clarification on the use of the [IRR function](https://support.office.com/en-us/article/IRR-function-64925EAA-9988-495B-B290-3AD0C163C1BC). I'm afraid I'm better at the the mechanics than the actual operation of the function. –  Jan 27 '16 at 01:19
  • Thank you both, jeeped and @PieterGeerkens. Adding application. resolves the problem. – Hans Jan 27 '16 at 01:45