IRR double type array conversion from a variant type

Question

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)

In the code you posted, you're trying to get a range and store in a array va (variant) and change all the items in that array to double? Does the va_d is an attempt to get an array of double? — tdmsoares
If I'm not wrong you're getting the error 'type mismatch' because you want to make a whole array into double field. — tdmsoares

TechnoDabbler TechnoDabbler · Accepted Answer · 2020-07-06T00:46:00

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:

IRR double type array conversion from a variant type

1 Answers