循环通过投资者列表，计算每个投资者的XIRR以实现流程自动化。

Question

所以我在这个问题上被困了几天了。我看了一些其他的代码，但我仍然很短。我在VBA也不是最好的。

我有一份投资者名单，附上他们的付款和日期。我试图运行一个命令按钮，它将遍历每个帐户，查找它们的相关付款和日期，运行XIRR函数，然后将XIRR值放在每个帐户的右下方。这很简单，可以手工完成，但是当您有一个15000 cells+的电子表格时，它就会变得单调乏味，我正在尝试自动化这个过程。这变得困难，因为每个投资者有不同的支付金额，所以找到正确的位置放置XIRR的价值也使我感到困惑。

下面是我的电子表格的一个例子

​

​

Dim i As Integer
Dim x As Double
Dim dateArray() As Date
Dim dateStrings() As String
Dim valArray() As Double
    
   ReDim dateArray(Dates.Count)
   ReDim valArray(Trans.Count)
   ReDim dateStrings(Dates.Count)



'Sheets("InvestorList").PivotTables.GetPivotData("Account", "x") = i
'Sheets("AccountPayments").Find ("i")
End Sub

Public Function MyXIRR(Dates As Range, Trans As Range, Balance As Double)


    For i = 1 To Dates.Count
        dateArray(i - 1) = Dates.Item(i).Value
        Next i
        
    For i = 1 To Trans.Count
        valArray(i - 1) = Trans.Item(i).Value
        Next i
    
    'Set the date on the "Balance" line to one day after the last transaction date
    dateArray(Dates.Count) = DateAdd("d", 1, Dates.Item(Dates.Count))
     valArray(Trans.Count) = -1 * Balance
      
    For i = 0 To Dates.Count
       dateStrings(i) = Format(dateArray(i), "mm/dd/yyyy")
       Next i
          
    MyXIRR = Application.WorksheetFunction.Xirr(valArray, dateStrings)
          

End Function

Answer 1

因此，我在一所大学咨询，他帮助我将代码简化为更简单、更干净的代码。我用数据运行了这段代码，它运行得很好。如果XIRR值不正确，可能需要进行一些抽查，但这有助于流程自动化。

 Private Sub CommandButton1_Click()
Dim myrow As Integer
Dim startrow As Integer
Dim valuerange As String
Dim daterange As String
Dim investor As String

myrow = 2
startrow = 2
investor = Cells(myrow, 1)
Do Until Cells(myrow, 1) = ""
    If Cells(myrow + 1, 1) <> investor Then
        'We are at the end of the list for the current investor.
        daterange = "R" & startrow & "C2:R" & myrow & "C2"
        valuerange = "R" & startrow & "C3:R" & myrow & "C3"
        Cells(myrow, 4) = "=XIRR(" & valuerange & ", " & daterange & ")"
        startrow = myrow + 1
        investor = Cells(myrow + 1, 1)
    End If
    myrow = myrow + 1
Loop
End Sub

Answer 2

我建议试着用宏记录仪记录你的脚步.如果您不知道如何这样做，以下是步骤！

在Excel中：

1. File
2. Options
3. Customize丝带(左面板)
4. 从以下命令中选择命令：(dropbox)选择“主选项卡”
5. select add>>
6. Click ok
7. Click developer选项卡，现在显示在最上带

H 117单击记录宏(左上角)H 218H 119Name宏，如果想要设置快捷方式和描述，请按您希望宏做的操作。当您为一个投资者完成该操作时，单击左上角的recording

1. Click宏，选择您刚刚创建的宏，然后单击Edite

，应该有一个框架例程来工作到您的循环G 231中。