0

Sorry if the question is stupid since I'm new to VBA.

The problem arises in the beginning since an excel IRR cannot calculate a changing column in a simulation(only calculates the initial values not the values in the following loops). So I went for the VBA IRR function, but the double array requirement of it seems to be another major problem.

When I try to read range into a variant array, and somehow convert each array element to double type, and then use them in VBA IRR function, the code still shows "type mismatch". I have tried in arrays too, but the same result of error.

Is there a way to make worksheet IRR work with changing cells, or to convert a variant array to a double which makes VBA IRR work?

Here is the code:

Dim va As Variant 

Dim va_d As Double

va = Worksheets(3).Range("AY16:AY136").Value 

va_d = CDbl(va)

Worksheets(3).Range("H7").Value = IRR(va_d)
braX
  • 11,506
  • 5
  • 20
  • 33
Mic432412e
  • 51
  • 3

1 Answers1

0

There might be a more elegant approach than this, but this example loops through the source data range and converts the values into array of doubles, before calculating the result:

Option Explicit

Public Sub CalcVBAIRR()

    Dim SourceRange As Range
    Dim SourceCell As Range
    Dim TargetRange As Range
    Dim va() As Double
    Dim Counter As Long
    
    Set SourceRange = Worksheets(3).Range("AY16:AY136")
    Set TargetRange = Worksheets(3).Range("H7")

    ReDim va(SourceRange.Cells.Count)

    Counter = 0
    For Each SourceCell In SourceRange
        va(Counter) = CDbl(SourceCell.Value)
        Counter = Counter + 1
    Next
    
    TargetRange.Value = IRR(va)

End Sub

and a function version ...

Public Function CalcIRR2(SourceRange As Range) As Double

    Dim SourceCell As Range
    Dim va() As Double
    Dim Counter As Long
    
    ReDim va(SourceRange.Cells.Count)

    Counter = 0
    For Each SourceCell In SourceRange
        va(Counter) = CDbl(SourceCell.Value)
        Counter = Counter + 1
    Next
    
    CalcIRR2 = IRR(va)

End Function

Example Output:

enter image description here

TechnoDabbler
  • 1,245
  • 1
  • 6
  • 12
  • Hi TechnoDabbler, thank you for your feedback. However, it still does not work with the error being "invalid procedure call or argument". I double-checked and the first element of range is negative, which is required by IRR function, so pretty sure the values are within the required ranges. Do you have an idea why IRR still does not work? Thank you! – Mic432412e Jul 06 '20 at 01:43
  • I retested the code with 120 values and it works, So we can deduce that the error isn't "invalid procedure call", but is more likely "invalid argument". As you've checked off the first value being negative then logic would say that the issue sits with some data in the range AY17:AY136. I've added a function version of the above code to use directly. Can I suggest that you run it on some simple/new test data. If that works, use it over your main dataset. If it doesn't work on the main dataset then halve the amount of data you feed in each time to pinpoint the data that's triggering the error. – TechnoDabbler Jul 06 '20 at 03:36
  • Thank you! I will try input some new data one at a time. – Mic432412e Jul 06 '20 at 15:06
  • Hi, I have tried and the IRR VBA function we create does not work when the IRR result is close to -1 or 0, i.e. when the following Cashflow is too small compared to Initial investments. – Mic432412e Jul 06 '20 at 23:10
  • Luckily, I happen to find the excel worksheet function IRR does work with my data set, but only with the initial 100 Cashflows, not covering the entire 120 data I have. And that is workable for my work so far. It's been quite a struggle to have tried to find why IRR sometimes work but sometimes doesn't. Anyway, thank you for your answers! It definitely confirms the idea that IRR(either iterations or other stuff) is not able to work with certain data that seems totally fine. – Mic432412e Jul 06 '20 at 23:10