首页 文章

EXCEL求解器，VBA在启动求解器之前调用不需要的函数

提问于
浏览 次
0

我正在编写一个代码来计算不同金融证券的公允 Value . 证券来自不同的发行人，因此按国家分组然后进行评估 .

为了做到这一点，我需要为每个组/国家拟合6个参数，遗憾的是这些参数不是固定的，但它们每天都会改变 . 我每次运行模型时都需要重新计算它们，所以我写了一个子程序来用Solver优化这个问题 .

到目前为止，它大部分时间都可以工作，但有时在解析求解器之前，vba代码会进入同一电子表格的其他函数，并更改我的问题的起始值（读作前一天参数） . Solver子程序中未提及这些功能，它们甚至不在同一模块或工作表中 .

知道为什么会这样吗？有关如何防止VBA踩到不需要的功能的任何想法？

这是我的解算器代码

Sub NSCoeff()
Dim current_wb As String
current_wb = ThisWorkbook.Name


Workbooks(current_wb).Sheets("Nelson_Siegel").Calculate
Workbooks(current_wb).Sheets("Nelson_Siegel").Activate
SolverReset
SolverOptions Precision:=0.01, Convergence:=0.1, AssumeNonNeg:=False
SolverOk SetCell:="$N$9", MaxMinVal:=2, ValueOf:=0, ByChange:="$N$4:$S$4", _
    Engine:=1, EngineDesc:="GRG Nonlinear"
SolverAdd CellRef:="$N$4", Relation:=3, FormulaText:="0.001"
SolverAdd CellRef:="$S$4", Relation:=3, FormulaText:="0.001"
SolverOk SetCell:="$N$9", MaxMinVal:=2, ValueOf:=0, ByChange:="$N$4:$S$4", _
    Engine:=1, EngineDesc:="GRG Nonlinear"
SolverOk SetCell:="$N$9", MaxMinVal:=2, ValueOf:=0, ByChange:="$N$4:$S$4", _
    Engine:=1, EngineDesc:="GRG Nonlinear"
SolverSolve userFinish:=True

End Sub

紧接在最后一行之后，代码进入此功能

Function Discount_Quartic(a As Double, b As Double, c As Double, d As Double, t As Double) As Double
Dim dr, r As Double
Application.Volatile

r = Worksheets("ImpBond").Cells(4, 26).Value
dr = Worksheets("ImpBond").Cells(4, 27).Value
Discount_Quartic = a * Exp(-r * t) + b * Exp(-r * t * dr) + c * Exp(-r * t * dr ^ 2) + d * Exp(-r * t * dr ^ 3) + (1 - a - b - c - d) * Exp(-r * dr ^ 4 * t)
End Function

谢谢您的帮助！

excel vba excel-vba

3 回答

  • 0

    如果Solver的参数是基于昨天数据的静态值，那么您应该使用Solver制作值的静态副本 . 我怀疑你的参数是计算的结果，其中包括折扣的日期等（以及费率，优惠券） . 因此，当您打开工作簿时，这些计算结果会更新（上面的函数被标记为易失性，如@ Matteo NNZ所述），因此Solver开始使用新值 .

    从您的代码中，Solver尝试通过更改单元格N4：S4来最小化单元格N9，但保持N4和S4> = 0.001 .

    回复于
  • 0

    这取决于：

    Application.Volatile

    此方法告诉应用程序该函数是 Volatile . 换句话说，每当某些内容更改为电子表格时，都必须重新计算该函数 .

    因此：

    • 您可以通过子 NSCoeff 修改电子表格;

    • Application 看到 Volatile 是什么，并重新计算它所有的一切 . 在你的情况下， Discount_Quartic . Voilàvoilà

    了解有关 Application.Volatile 方法here的更多信息 .

    至于如何防止VBA踩到不需要的功能？的任何想法，你有两个解决方案（可能更多，但现在我想到的是这些）：

    • 从函数中删除 Application.Volatile . 但是，这将不再提示重新计算该函数（除非您没有明确重新计算它） .

    • 做一个小技巧，我将通过一个简单的例子向您展示：

    Dim dontCall As Boolean '<-- default: False
Sub dontCallTheFunction()
    Range("A1") = 1 '<-- recalculation prompted, we will get into the function
    dontCall= True'<-- we don't want the function to be recalculated
End Sub
Function myFunction()
    Application.Volatile
    If dontCall= False Then
         myFunction = 3
    Else
        dontCall = False '<-- reset the value to False, so next time we'll calculate it normally
    End If
End Function
    回复于
  • 0

    我遇到了与你完全相同的问题，发现你的问题试图回答我的问题 . 我同意了ChipsLetten，然后我看了一下我的电子表格并发现我有这个功能的单元格 . 因此，请检查您的电子表格是否在某些单元格中没有使用 Discount_Quadratic

    （如单元格A1，公式为“= Discount_Quadratic（a，b，c，d，t）”）

    如果您在电子表格的单元格中具有此功能，Excel将运行此功能等于没有具有此功能的单元格 . 因此，当Excel更新所有电子表格的公式时，您的代码将随时被此更新中断并运行此函数内的代码，即使您没有真正调用它 .

    回复于

相关问题