玩转Excel的XIRR()函数——AI写代码初体验

Function XIRR_FLEX(values As Range, dates As Range, Optional guess As Double = 0.1) As Variant

    Dim vArr() As Double

    Dim dArr() As Variant

    Dim cell As Range

    Dim area As Range

    Dim i As Long

    Dim hasPositive As Boolean

    Dim hasNegative As Boolean

    ' 收集现金流

    i = 0

    For Each area In values.Areas

        For Each cell In area

            If IsNumeric(cell.Value) And cell.Value <> "" Then

                i = i + 1

                ReDim Preserve vArr(1 To i)

                vArr(i) = CDbl(cell.Value)

                If vArr(i) > 0 Then hasPositive = True

                If vArr(i) < 0 Then hasNegative = True

            End If

        Next cell

    Next area

    ' 收集日期

    i = 0

    For Each area In dates.Areas

        For Each cell In area

            If IsDate(cell.Value) And cell.Value <> "" Then

                i = i + 1

                ReDim Preserve dArr(1 To i)

                dArr(i) = CDbl(cell.Value)

            End If

        Next cell

    Next area

    ' 校验

    If UBound(vArr) <> UBound(dArr) Then

        XIRR_FLEX = CVErr(xlErrValue)

        Exit Function

    End If

    If UBound(vArr) < 2 Then

        XIRR_FLEX = CVErr(xlErrNum)

        Exit Function

    End If

    If Not (hasPositive And hasNegative) Then

        XIRR_FLEX = CVErr(xlErrNum)

        Exit Function

    End If

    ' 计算

    On Error GoTo ErrorHandler

    XIRR_FLEX = Application.WorksheetFunction.Xirr(vArr, dArr, guess)

    Exit Function

ErrorHandler:

    XIRR_FLEX = CVErr(xlErrNum)

End Function